VBA - Pasting multiple tables below one another with line breaks

[cbreezy]

Hello everyone- I have been working on this for a while now so I am caving and posting here.

I have some VBA code that is opening a workbook, selecting pivot tables from a given sheet in that workbook, and pasting them as special values/special formats into the original workbook.

The code basically toggles back and forth between the two workbooks.

So far, the first 3 tables paste fine, but on the fourth one, I am getting a Run-Time error '1004': Application-defined or object-defined error.

The first 3 tables result in having data in cells A1:B15, D1:E15, and A17:B32.

The code is below, I have omitted the part where it opens the other workbook and stuff since that isn't relevant here. Any help on solving this issue would be great, as I have several more tables to paste as well.



Sub Macro2()
'
Application.DisplayAlerts = False
'THe code I have is run in workbook titled "Emails"
'the code opens another workbook titled "tables"

'1. the first pivot table is titled CA

Workbooks("Tables").Sheets("Email Tables").Activate
Workbooks("Tables").Sheets("Email Tables").PivotTables("CA").TableRange1.Select
Selection.Copy

Windows("Emails.xlsm").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'2. pasting the second pivot table CCNR next to pivot table CA, in cell D1

Workbooks("Tables").Sheets("Email Tables").Activate
Workbooks("Tables").Sheets("Email Tables").PivotTables("CCNR").TableRange1.Select
Selection.Copy

Windows("Emails.xlsm").Activate
Selection.Range("D1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False



'3. This is selecting the next pivot table of a different size, finding the next available row, and pasting it there. 'For some reason what was happening was it was pasting it in column D right below the second table (D16). So I 'offset it by (2,-3) to put a line break, then move it back to column A

Workbooks("Tables").Sheets("Email Tables").Activate
Workbooks("Tables").Sheets("Email Tables").PivotTables("WIP").TableRange1.Select
Selection.Copy

Windows("Emails.xlsm").Activate
Selection.Range("A" & Rows.Count).End(xlUp).Offset(2,-3).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


'4. This is where I get the error. I just want to paste it below table 3, and have a line break. The error takes 'place at the Range("A" & Rows.Count)

Workbooks("Tables").Sheets("Email Tables").Activate
Workbooks("Tables").Sheets("Email Tables").PivotTables("Touches").TableRange1.Select
Selection.Copy

Windows("Emails.xlsm").Activate
Selection.Range("A" & Rows.Count).End(xlUp).Offset(2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False


Any help on solving this issue would be great, as I have several more tables to paste

 

[MARK858]

What happens if you change

Windows("Emails.xlsm").Activate
 Selection.Range("A" & Rows.Count).End(xlUp).Offset(2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
 :=False, Transpose:=False
 Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
 SkipBlanks:=False, Transpose:=False

to

With Workbooks("Emails.xlsm").Sheets(1).Range("A" & Rows.Count).End(xlUp).Offset(2)
  .PasteSpecial Paste:=xlPasteValues
  .PasteSpecial Paste:=xlPasteFormats
End With

Assumes that the first sheet was the active sheet when you were activating Emails.xlsm