PasteSpecial is overwriting my headers RRS feed

  • Question

  • I have this code:

    'Where ws is a worksheet object that I am copying from
    'and TargetSheet is a worksheet object that I am copying to
    ws.Range("A2:I" & ws.Range("A" & Rows.Count).End(xlUp).Row).Copy
    TargetSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues

    I have headings in both sheets. This code says to copy all of the data in columns A through I of the ws sheet and then pastespecial them into the first available row in column A of the target sheet. Since my target sheet has headings, the first available row in column A is row 2. I've used the immediate window to show that my code is pointing to A2. BUT...when I run the code, my copied data gets pasted to A1, overwriting my headings. I've even tried hard coding the target address as:

    TargetSheet.Range("A2").PasteSpecial Paste:=xlPasteValues

    and the data still gets pasted to A1.

    What's a better way to do this, or what's going wrong? 


    Friday, March 4, 2016 2:55 PM

All replies

  • Hi

    This works perfectly, You're not showing your macro so can't help you any further.

    Sub test()
    Dim ws As Worksheet, targetSheet As Worksheet
    Set ws = Worksheets("Sheet1")
    Set targetSheet = Worksheets("Sheet2")
    ws.Range("A2:I" & ws.Range("A" & Rows.Count).End(xlUp).Row).Copy
    targetSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End Sub


    Friday, March 4, 2016 10:02 PM