VBA - Pivot table - Excel - copy data on specific column and paste to another template

[thedm]

Hi Aloha,

Having problem copy all the active data from pivot (excel file 1) transferring to another template file (excel file 2) excluding the row 1 to 11 header.

I have four (4) columns in master report to copy in template on the same sheet name (i.e. sushi, bakery, bento, genmerch)
First, I want to copy A12:C27 or all the way down the pivot. and paste in the template A2
Second, copy the M12:O27 or all the way down the pivot. and paste in the template M2

While in master (excel file 1,) it will open the template file X:\Backup\Report_Template.xltm (which is the excel file 2) to paste the data as values only for specific columns on the same columns.

Please help the faster way to paste it. My current vba coding is on below and pictures to visualize what I'm trying to accomplish.



excel file 1(master report)

excel file 2 (template)

Sub Create_Report()
On Error GoTo Err_Error_Handler

Dim wbMaster As Workbook, wbfirstTemplate As Workbook

Set wbMaster = ThisWorkbook
Set wbfirstTemplate = Workbooks.Open("X:\Backup\Report_Template.xltm")

lastRow = Range("A12" & Rows.Count).End(xlUp).Row 'copy A12 to last row

Application.ScreenUpdating = False
Application.DisplayAlerts = False


    wbMaster.Activate 'copy from master
    wbMaster.Sheets("Sushi").Range("A12:C" & lastRow).Select
    wbMaster.Sheets("Sushi").Cells.Copy
        Workbooks.Add Template:= "X:\Backup\Report_Template.xltm" 'open template
        wbfirstTemplate.Activate 'paste to template
        'first batch column to paste
        wbfirstTemplate.Sheets("Sushi").Range("A2").Select
        wbfirstTemplate.Sheets("Sushi").Range("A2").PasteSpecial xlPaste

    wbMaster.Activate 'copy from master
    wbMaster.Sheets("Sushi").Range("M12:O" & lastRow).Select
    wbMaster.Sheets("Sushi").Cells.Copy
        wbfirstTemplate.Activate 'paste to template
        'second batch column to paste
        wbfirstTemplate.Sheets("Sushi").Range("M2").Select
        wbfirstTemplate.Sheets("Sushi").Range("M2").PasteSpecial xlPaste


  Application.DisplayAlerts = True
  Application.ScreenUpdating = True


Exit_Error_Handler:
    Exit Sub
    
Err_Error_Handler:
    Application.Cursor = xlNormal
    MsgBox "Create_Report: " & Err.Number & " - " & Err.Description
    Resume Exit_Error_Handler
End Sub

 

[thedm]

Any inputs please.
Any other option if this not possible to.
Thank you for your help.