Results 1 to 2 of 2

Thread: PasteSpecial Overwrites Previously Rows
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2019
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PasteSpecial Overwrites Previously Rows

    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!

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    11,041
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    2 Thread(s)

    Default Re: PasteSpecial Overwrites Previously Rows

    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
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •