PasteSpecial Overwrites Previously Rows

MRHein

New Member
Joined
Jul 12, 2019
Messages
3
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!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,052
Office Version
2013
Platform
Windows
Replace what you posted with the code below.

Code:
'Define variable for row to copy into
'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.Worksheets(3).Range("A15:Y" & lrow).SpecialCells(xlCellTypeVisible).Copy
    basebook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    'Copy Project Name
    mybook.Worksheets(3).Range("F7").Copy
    basebook.Worksheets(1).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial Paste:=xlPasteValues
    'Save and Close Workbook
    mybook.Close SaveChanges:=False
    'Ensure Workbook has closed before moving on to next line of code
    DoEvents
    'Update variable
    'Get next file name
    myFile = Dir
Loop
 

Watch MrExcel Video

Forum statistics

Threads
1,102,452
Messages
5,486,991
Members
407,575
Latest member
calc

This Week's Hot Topics

Top