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!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0

Forum statistics

Threads
1,223,920
Messages
6,175,373
Members
452,638
Latest member
Oluwabukunmi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top