Below is my sub routine. The line below is where the code stops every single time with 1004 error message. When I hit “Debug” the sheet seems to populate with the copied data and I continue on with the code without any issues until it comes back to the line again and the same error presents itself. It is like the copy of the values isn’t finished.
‘Sets the active workbook
Set wb = ActiveWorkbook
<code>'Finds the last populated row in the of the unique consultants in PDF Data spreadsheet
LastRow = wb.Sheets("Queries").Cells(Rows.Count, 7).End(xlUp).Row
wb.Sheets("PDF Layout").Range("A3").Value = wb.Sheets("Queries").Range("G" & x).Value
If wb.Sheets("PDF Layout").Range("A3").Value <> "" Then
If wb.Sheets("PDF Layout").Range("A3").Value <> "0" Then
wb.Sheets("PDF Data").Activate
ActiveSheet.ListObjects("PDF_tbl").Range.AutoFilter Field:=2, Criteria1:=wb.Sheets("PDF Layout").Range("A3").Value
PDFDataLastRow = wb.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row - 1
wb.Sheets("PDF Layout").Range("A6:N6").Clear
wb.Sheets("PDF Layout").Range("A7:N9999").Delete
wb.Sheets("PDF Data").Range("PDF_tbl[[#All],[Customer ID]:[Total AR]]").Copy Destination:=wb.Sheets("PDF Layout").Range("A5")
'wb.Sheets("PDF Data").Range("C5:P9999").SpecialCells(xlCellTypeVisible).Copy
'wb.Sheets("PDF Layout").Range("A5").PasteSpecial xlPasteAll
**wb.Sheets("PDF Layout").Range("A1").Select**
PDFLayoutLastRow = wb.Sheets("PDF Layout").Cells(Rows.Count, 1).End(xlUp).Row
wb.Sheets("PDF Layout").Range("A5:N" & PDFLayoutLastRow).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(8, 9, 10, 11, 12, 13, 14), Replace:=True, Pagebreaks:=False, SummaryBelowData:=True
wb.Sheets("PDF Layout").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Z:BenFAgingPDF" & Replace(Replace(wb.Sheets("PDF Layout").Range("A3").Value, ",", ""), ".", "") & "_" & Format(Now, "yyyymmdd") & ".pdf"
Set wbCopy = Workbooks.Add
wb.Sheets("PDF Layout").Copy Before:=wbCopy.Sheets(1)
wbCopy.SaveAs "Z:BenFAgingExcel" & Replace(Replace(wb.Sheets("PDF Layout").Range("A3").Value, ",", ""), ".", "") & "_" & Format(Now, "yyyymmdd") & ".xlsx"
MsgBox "Excel and PDF files have been created.", vbOKOnly
<code>'Finds the last populated row in the of the unique consultants in PDF Data spreadsheet
LastRow = wb.Sheets("Queries").Cells(Rows.Count, 7).End(xlUp).Row
x = 7
Do Until x > LastRow
wb.Sheets("PDF Layout").Range("A3").Value = wb.Sheets("Queries").Range("G" & x).Value
If wb.Sheets("PDF Layout").Range("A3").Value <> "" Then
If wb.Sheets("PDF Layout").Range("A3").Value <> "0" Then
wb.Sheets("PDF Data").Activate
ActiveSheet.ListObjects("PDF_tbl").Range.AutoFilter Field:=2, Criteria1:=wb.Sheets("PDF Layout").Range("A3").Value
PDFDataLastRow = wb.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row - 1
wb.Sheets("PDF Layout").Range("A6:N6").Clear
wb.Sheets("PDF Layout").Range("A7:N9999").Delete
wb.Sheets("PDF Data").Range("PDF_tbl[[#All],[Customer ID]:[Total AR]]").Copy Destination:=wb.Sheets("PDF Layout").Range("A5")
'wb.Sheets("PDF Data").Range("C5:P9999").SpecialCells(xlCellTypeVisible).Copy
'wb.Sheets("PDF Layout").Range("A5").PasteSpecial xlPasteAll
'Adds subtotals
**wb.Sheets("PDF Layout").Range("A1").Select**
PDFLayoutLastRow = wb.Sheets("PDF Layout").Cells(Rows.Count, 1).End(xlUp).Row
wb.Sheets("PDF Layout").Range("A5:N" & PDFLayoutLastRow).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(8, 9, 10, 11, 12, 13, 14), Replace:=True, Pagebreaks:=False, SummaryBelowData:=True
wb.Sheets("PDF Layout").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Z:BenFAgingPDF" & Replace(Replace(wb.Sheets("PDF Layout").Range("A3").Value, ",", ""), ".", "") & "_" & Format(Now, "yyyymmdd") & ".pdf"
Set wbCopy = Workbooks.Add
wb.Sheets("PDF Layout").Copy Before:=wbCopy.Sheets(1)
wbCopy.SaveAs "Z:BenFAgingExcel" & Replace(Replace(wb.Sheets("PDF Layout").Range("A3").Value, ",", ""), ".", "") & "_" & Format(Now, "yyyymmdd") & ".xlsx"
wbCopy.Close
End If
End If
x = x + 1
Loop
MsgBox "Excel and PDF files have been created.", vbOKOnly
</code>
'Finds the last populated row in the of the unique consultants in PDF Data spreadsheet
LastRow = wb.Sheets("Queries").Cells(Rows.Count, 7).End(xlUp).Row
x = 7
Do Until x > LastRow
wb.Sheets("PDF Layout").Range("A3").Value = wb.Sheets("Queries").Range("G" & x).Value
If wb.Sheets("PDF Layout").Range("A3").Value <> "" Then
If wb.Sheets("PDF Layout").Range("A3").Value <> "0" Then
wb.Sheets("PDF Data").Activate
ActiveSheet.ListObjects("PDF_tbl").Range.AutoFilter Field:=2, Criteria1:=wb.Sheets("PDF Layout").Range("A3").Value
PDFDataLastRow = wb.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row - 1
wb.Sheets("PDF Layout").Range("A6:N6").Clear
wb.Sheets("PDF Layout").Range("A7:N9999").Delete
wb.Sheets("PDF Data").Range("PDF_tbl[[#All],[Customer ID]:[Total AR]]").Copy Destination:=wb.Sheets("PDF Layout").Range("A5")
'wb.Sheets("PDF Data").Range("C5:P9999").SpecialCells(xlCellTypeVisible).Copy
'wb.Sheets("PDF Layout").Range("A5").PasteSpecial xlPasteAll
'Adds subtotals
**wb.Sheets("PDF Layout").Range("A1").Select**
PDFLayoutLastRow = wb.Sheets("PDF Layout").Cells(Rows.Count, 1).End(xlUp).Row
wb.Sheets("PDF Layout").Range("A5:N" & PDFLayoutLastRow).Select
Selection.Subtotal GroupBy:=2, Function:=xlSum, TotalList:=Array(8, 9, 10, 11, 12, 13, 14), Replace:=True, Pagebreaks:=False, SummaryBelowData:=True
wb.Sheets("PDF Layout").Activate
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="Z:BenFAgingPDF" & Replace(Replace(wb.Sheets("PDF Layout").Range("A3").Value, ",", ""), ".", "") & "_" & Format(Now, "yyyymmdd") & ".pdf"
Set wbCopy = Workbooks.Add
wb.Sheets("PDF Layout").Copy Before:=wbCopy.Sheets(1)
wbCopy.SaveAs "Z:BenFAgingExcel" & Replace(Replace(wb.Sheets("PDF Layout").Range("A3").Value, ",", ""), ".", "") & "_" & Format(Now, "yyyymmdd") & ".xlsx"
wbCopy.Close
End If
End If
x = x + 1
Loop
MsgBox "Excel and PDF files have been created.", vbOKOnly
Expectation is that the bolded line will work. I tried placing other lines there that interact with the same worksheet and it still errors out. It is almost as if the paste isn’t finished.