Copy Range of Cells from One Worksheet to Another Worksheet

Hello Everyone,

KEY INFORMATION that will be relevant to the VBA code:

- I have 4 worksheets in the workbook “PivotTableReport.xlsm” and these are the names of the worksheets:

1. rptTune (Data in Cells should be PASTED to this worksheet)
2. rptNoTune (Data in Cells should be COPIED from this worksheet)
3. AFTER MACRO in rptTune (What “rptTune” worksheet should look like after the Macro is executed)
4. Data Feeding Pivot Tables (The data feeding worksheets “rptTune” and “rptNoTune”)

An EXPLANATION of what I am looking to do and I attached a workbook along with post by the name of “PivotTableReport.xlsm”:

I want to copy cells that contain data from the “rptNoTune” worksheet and paste them on to a specific location on the “rptTune” worksheet . I want to copy all the cells that come AFTER the SUBTOTAL ROW “RT Total” under the PIVOT FIELD “Make” in the “rptNoTune” worksheet and PASTE them AFTER the SUBTOTAL ROW “RT Total” under the PIVOT FIELD “Make” in the “rptTune” worksheet. Since the Pivot Tables can expand and contract with data rows, it is KEY that the cells being copied always come after the SUBTOTAL ROW “RT Total” or the last SUBTOTAL ROW under the PIVOT FIELD “Make” in the “rptNoTune” worksheet.

The worksheet “AFTER MACRO in rptTune” provides a VISUAL of what the “rptTune” worksheet should look like after the Macro has been executed.

Note: When copying cells from the “rptNoTune” worksheet, I really don’t care if the cells being copied extend all the way to the last row of the worksheet as oppose to the last row that contains data. Whatever is easier to code is fine by me.

Thanks in Advance!

See attached workbook.

Ah...good point. It's better to use R1C1 notation and refer to cells. Then you can use column and row numbers in your references, and refer to them using the cells method of range:

Sub UpdateReport()
    Dim lngFirstRowToAdd As Long, lngLastRowToAdd As Long, lngLastReportRow As Long, lngLastColumnToAdd As Long
    lngFirstRowToAdd = Sheets("rptNoTune").Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).Row
    lngLastRowToAdd = Sheets("rptNoTune").Range("A65536").End(xlUp).Row
    lngLastColumnToAdd = Sheets("rptNoTune").Range("A10").End(xlToRight).Column
    lngLastReportRow = Sheets("rptTune").Range("A65536").End(xlUp).Offset(1, 0).Row
    Sheets("rptNoTune").Range(Cells(lngFirstRowToAdd, 1), Cells(lngLastRowToAdd, lngLastColumnToAdd)).Copy
    Sheets("rptTune").Range("A" & lngLastReportRow).PasteSpecial
    Application.CutCopyMode = False
End Sub

I added the variable lngLastColumnToAdd which will hold the column number, and this line of code was modified from:

Sheets("rptNoTune").Range("A" & lngFirstRowToAdd & ":K" & lngLastRowToAdd).Copy

To:

Sheets("rptNoTune").Range(Cells(lngFirstRowToAdd, 1), Cells(lngLastRowToAdd, lngLastColumnToAdd)).Copy

Weird. I ran into the same thing, but noticed if I save, close and reopen the book, the code runs, and I haven't a clue why it doesn't generate the error every time. I may want to look at a range object instead of a reference. Let me see about tweaking the code.

Hmmmm....that's cool! I'll have to remember the With|End with construction next time I run into this.