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

 

[Joe4]

You are using a variable named "lastRow", but you actually have not calculated it anywhere (so it is equal to zero, and there is no cell reference "G0").

You should use OPTION EXPLICIT in all your VBA code, which forces you to declare all variables before using them.
Even with that, you still would need to give it a value before using it.

 

[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
LastRow = Workbooks("Yearly HMA Charts.xlsx").Sheets("Sieves")cells(Rows.Count, "G").End(xlUp).Row '+ 1 for next available row
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

I tried it as this too. I can't seem to get that part right

 

[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
LastRow = Workbooks("Yearly HMA Charts.xlsx").Sheets("Sieves")cells(Rows.Count, "G").End(xlUp).Row '+ 1 for next available row
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

That's what I had tried

 

[Joe4]

When working with multiple workbooks, I prefer to set them equal to workbook objects, which makes it easy to bounce back and forth between them. See the code below. Note I have not analyzed the last section ("Export"):

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\Asphalt\Misc\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 Destination:=destWB.Sheets("Sieves").Range("G" & lastRow)
    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

 

[jondavis1987]

thank you. That worked perfectly. I'm very new to vba

 

[Joe4]

There are a few tips that I think are very helpful for new VBA users:

1. Become familiar with the Macro Recorder. If you turn it on, and record the steps you want to do manually, it will give you the building blocks for a lot of the VBA code you need!

2. Stepping through your code is very helpful in debugging your code (using the F8 key to go through your code line-by-line). If you have two monitors, have the VBA Editor up on one screen and your workbook open on the other (or if you only have one screen, re-size the VB Editor down to about 1/4 the size of the screen so you can see both on the same screen). Then, as you go through your code one line at a time using F8, you can see what is happening on your sheet. Also, if you hover over any variables in your VBA code (like "lastRow"), it will show you the value of it at that time.

A lot of times, when you see what is happening on your worksheet, it becomes evident what isn't working and what the problem is (i.e. maybe you aren't on the sheet or file that you think you are!).

 

[jondavis1987]

So I thought it was working. It's pasting in the right spot but when I click on what is pasted there it says it's posted from sheet "A" cell A1634 of the source workbook

 

[Joe4]

it says it's posted from sheet "A" cell A1634 of the source workbook

Are you copying formulas?

Note that if you copy/move formulas, there cell references will often change.
Do you want to copy just the hard-coded values of what is returned by the formulas?

 

[jondavis1987]

yeah. We got it

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
    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