VBA Paste Help

[jondavis1987]

Sub Open_Workbook()
'Open Workbook
Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Misc\Yearly HMA Charts.xlsx"
ThisWorkbook.Sheets("Sheet2").Range("J18:J25").Copy
Destination:=Workbooks("Yearly HMA Charts.xlsx").Sheets("Sieves").Range("G" & lastRow)
Workbooks("C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Misc\Yearly HMA Charts.xlsx").Close SaveChanges:=True
Dim fName As String
With ActiveWorkbook
     fName = Range("A!F19").Value
     ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
             "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, Quality:=xlQualityStandard, _
             IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
End With
End Sub

When I try to run this it says syntax error.

Destination:=Workbooks("Yearly HMA Charts.xlsx").Sheets("Sieves").Range("G" & lastRow)

Seems to be the problem

 

[jondavis1987]

Option Explicit
Sub Open_Workbook()
    Dim srcWB As Workbook
    Dim destWB As Workbook
    Dim fName As String
    Dim lastRow As Long
    
'   Capture current workbook as source workbook
    Set srcWB = ActiveWorkbook
'   Open destination workbook and capture it as destination workbook
    Workbooks.Open "C:\Users\jdavis\Dropbox\Quality Control\Excel\Yearly HMA Charts.xlsx"
    Set destWB = ActiveWorkbook
    
'   Find last row of data in destination workbook
    lastRow = destWB.Sheets("Sieves").Cells(Rows.Count, "G").End(xlUp).Row + 1
    
'   Copy data from source workbook to destination workbook, then save and close
    srcWB.Sheets("Sheet2").Range("J18:J25").Copy
    destWB.Sheets("Sieves").Range("G" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G29:G36").Copy
    destWB.Sheets("Sieves").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H39:H46").Copy
    destWB.Sheets("Sieves").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("F39:F46").Copy
    destWB.Sheets("Sieves").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("F29:F36").Copy
    destWB.Sheets("Sieves").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H29:H36").Copy
    destWB.Sheets("Sieves").Range("E" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("A10:A17").Copy
    destWB.Sheets("Sieves").Range("F" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G21:G28").Copy
    destWB.Sheets("Sieves").Range("H" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H21:H28").Copy
    destWB.Sheets("Sieves").Range("I" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G29").Copy
    destWB.Sheets("AC").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H39").Copy
    destWB.Sheets("AC").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("F39").Copy
    destWB.Sheets("AC").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("F29").Copy
    destWB.Sheets("AC").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H29").Copy
    destWB.Sheets("AC").Range("E" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("D18").Copy
    destWB.Sheets("AC").Range("F" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G47").Copy
    destWB.Sheets("AC").Range("G" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H47").Copy
    destWB.Sheets("AC").Range("H" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G29").Copy
    destWB.Sheets("Voids").Range("A" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H39").Copy
    destWB.Sheets("Voids").Range("B" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("F39").Copy
    destWB.Sheets("Voids").Range("C" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("F29").Copy
    destWB.Sheets("Voids").Range("D" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H29").Copy
    destWB.Sheets("Voids").Range("E" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("C48").Copy
    destWB.Sheets("Voids").Range("F" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("G48").Copy
    destWB.Sheets("Voids").Range("G" & lastRow).PasteSpecial xlPasteValues
    srcWB.Sheets("A").Range("H48").Copy
    destWB.Sheets("Voids").Range("H" & lastRow).PasteSpecial xlPasteValues
    destWB.Close SaveChanges:=True
'   Export
    With srcWB
        fName = Range("A!F19").Value
        ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
            "C:\Users\jdavis\Dropbox\Quality Control\Asphalt\Asphalt Reports\" & fName, Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
    End With
End Sub

This is what I've got now. The problems I'm running into are it's copying to the same row on worksheets "AC" & "Voids" as it does "Sieves" and for some reason it's no longer exporting the active workbook as a pdf at the end

 

[jondavis1987]

Never mind about the pdf problem. So the only problem I'm having is that it copies to the same row in all of the worksheets and I need it to copy to the bottom row of each worksheet

 

[Joe4]

Not sure I quite understand what you are doing, but sounds like you will need to do the lastRow calculation more than once, to get the value you need to use when pasting.