VBA Paste Help

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
Code:
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.

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

Seems to be the problem
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
Code:
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

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
Code:
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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"):
Code:
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
 
Last edited:

jondavis1987

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
thank you. That worked perfectly. I'm very new to vba
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,703
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Dec 31, 2015
Messages
416
Office Version
  1. 2019
Platform
  1. Windows
yeah. We got it

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

Watch MrExcel Video

Forum statistics

Threads
1,133,539
Messages
5,659,392
Members
418,500
Latest member
Guru Prasad S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top