0

First time poster, I am a novice a VBA and have gotten by with just piecing stuff together and I am hitting a wall with this.

I am attempting to compile daily sales data into a single worksheet. I have a workbook that has sheets from Day 1 - Day 31 as well as some summary sheets at the end.

What I want to do is copy specific cells from each 'Day' sheet and paste in to another workbook on a single sheet and paste the new data at the bottom of the sheet.

I am very close and the only issue I am running into is my macro will not move the the next sheet in the original workbook before it runs the For Next again. Everything else is doing what I want it to.

Here is what I have thus far, some of it maybe incomplete as I have been messing with different methods. Any help here is greatly appreciated.

'''

Workbooks.Add
[A1:M1] = Split("*InvoiceNo *Customer *InvoiceDate *DueDate Terms Location Memo Item(Product/Service) ItemDescription ItemQuantity ItemRate *ItemAmount ServiceDate")
ActiveWorkbook.SaveAs Filename:= _
    "c:\Qbooksw\Bulkqboimport In Progress", FileFormat:=xlCSV, _
    CreateBackup:=False
Workbooks("Sales 2021-12 with QBOmacro NEW.xls").Activate
Dim Days As Worksheet
Dim Day As Variant


Day = Array("Day 1", "Day 2", "Day 3", "Day 4", "Day 4", "Day 5", "Day 6", "Day 7", "Day 8", "Day 9", "Day 10", "Day 11", "Day 12", _
"Day 13", "Day 14", "Day 15", "Day 16", "Day 17", "Day 18", "Day 19", "Day 20", "Day 21", "Day 22", "Day 23", "Day 24", "Day 25", "Day 26", _
"Day 27", "Day 28", "Day 29", "Day 30", "Day 31")


For Each Day In Days
    .Unprotect Password:="*******"
    .Rows("143:188").EntireRow.Hidden = False
    .Range("B144:N188").Select
    .Copy
    
Workbooks("Bulkqboimport In Progress").Activate


Dim EndRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
Columns("A:M").Sort Key1:=Range("A1"), Header:=xlYes
Workbooks("Sales 2021-12 with QBOmacro NEW.xls").Activate
Rows("143:188").EntireRow.Hidden = True
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, Password:="Finch21"
Next Day
    
Workbooks("Bulkqboimport In Progress").Activate
Range("A1, C1, D1, M1").EntireColumn.Select
Application.CutCopyMode = False
Selection.NumberFormat = "m/d/yy"
Columns("L:L").Select
Selection.NumberFormat = "0.00"

ActiveWorkbook.SaveAs Filename:= _
    "c:\Qbooksw\Bulkqboimport " & Format(Now(), "mm-dd-yy hh mm AMPM"), FileFormat:=xlCSV, _
    CreateBackup:=False

'''

CASConsulting
  • 1
  • 1
  • `For Each Day In Days` - here `Day` is just a string and so has no properties or methods. You need to convert that string into a worksheet `Dim ws As Worksheet: Set ws = Thisworkbook.Worksheets(Day)` and then work explicitly with `ws`. – Tim Williams Mar 02 '22 at 00:02
  • Also highly encourage applying all recommendations from: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba – Tim Williams Mar 02 '22 at 00:04

0 Answers0