I have a Macro that loops through a folder of workbooks and copies data (a variable number of rows per sheet based on an Array) into a single report. For some reason, the paste function overwrites lines rather than just going to whatever is the next blank line. It only keeps 15-17 lines before overwriting the rest. I inherited this report and its Macro, so I really have no idea of the logic. Can someone show me what I need to modify in the Paste to tell it to start the next paste on the next blank row?
Here is what I'm working with:
'Define variable for row to copy into
rnum = 2
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set mybook = Workbooks.Open(Filename:=myPath & myFile)
'Ensure Workbook has opened before moving on to next line of code
DoEvents
'Filter file for data rows wanted (Column I was used as current best source for the last row)
With mybook.Worksheets(3)
.AutoFilterMode = False
lRow = .Range("I" & .Rows.Count).End(xlUp).Row
.Range("$A$14:$AR$" & lRow).AutoFilter Field:=7, Criteria1:=Array("ES", "TS", "PS", "DC", "BL"), Operator:=xlFilterValues
End With
'Copy Project Data
mybook.Activate
mybook.Worksheets(3).Range("A15:Y" & lRow).SpecialCells(xlCellTypeVisible).Copy
basebook.Activate
basebook.Worksheets(1).Rows(rnum & ":" & rnum).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy Project Name
mybook.Activate
mybook.Worksheets(3).Select
mybook.Worksheets(3).Range("F7").Select
Selection.Copy
basebook.Activate
basebook.Worksheets(1).Range("A" & rnum & ":A" & rnum ).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
'Save and Close Workbook
mybook.Close SaveChanges:=False
'Ensure Workbook has closed before moving on to next line of code
DoEvents
'Update variable
rnum = rnum + 15
'Get next file name
myFile = Dir
Loop
Thanks for your Help!
Here is what I'm working with:
'Define variable for row to copy into
rnum = 2
'Loop through each Excel file in folder
Do While myFile <> ""
'Set variable equal to opened workbook
Set mybook = Workbooks.Open(Filename:=myPath & myFile)
'Ensure Workbook has opened before moving on to next line of code
DoEvents
'Filter file for data rows wanted (Column I was used as current best source for the last row)
With mybook.Worksheets(3)
.AutoFilterMode = False
lRow = .Range("I" & .Rows.Count).End(xlUp).Row
.Range("$A$14:$AR$" & lRow).AutoFilter Field:=7, Criteria1:=Array("ES", "TS", "PS", "DC", "BL"), Operator:=xlFilterValues
End With
'Copy Project Data
mybook.Activate
mybook.Worksheets(3).Range("A15:Y" & lRow).SpecialCells(xlCellTypeVisible).Copy
basebook.Activate
basebook.Worksheets(1).Rows(rnum & ":" & rnum).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Copy Project Name
mybook.Activate
mybook.Worksheets(3).Select
mybook.Worksheets(3).Range("F7").Select
Selection.Copy
basebook.Activate
basebook.Worksheets(1).Range("A" & rnum & ":A" & rnum ).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=True, Transpose:=False
'Save and Close Workbook
mybook.Close SaveChanges:=False
'Ensure Workbook has closed before moving on to next line of code
DoEvents
'Update variable
rnum = rnum + 15
'Get next file name
myFile = Dir
Loop
Thanks for your Help!