Copying Values from one Workbook to a newly created workbook

Question

Can someone please review my code below and tell me what's missing? I thought this would work, but keeps breaking on the copy function. Help greatly appreciated.

Code copies an excel workbook template and renames it in a new directory. Then needs to paste data in from the current workbook to the new one. Copying from this workbook sheet "Site Page", and pasting into NewFileName sheet "Summary".

Public NewIntegration As String
    
Sub Copy_One_File()
    
'Variables
Dim TemplateWorkbook As String
Dim NewWorkbook As String
Dim GivenLocation As String
Dim OldFileName As String
Dim NewFileName As String

'Define Strings
TemplateWorkbook = "\\r.sharepoint.com@SSL\DavWWWRoot\teams\NOE-RadioEng\Radio\New Build\_2019 NSB Tracker\Integration Reports\Template\TEMPLATE.xlsx"
NewWorkbook = "\\r.sharepoint.com@SSL\DavWWWRoot\teams\NOE-RadioEng\Radio\New Build\_2019 NSB Tracker\Integration Reports\TEMPLATENew.xlsx"
GivenLocation = "\\r.sharepoint.com@SSL\DavWWWRoot\teams\NOE-RadioEng\Radio\New Build\_2019 NSB Tracker\Integration Reports\"
ReportCreator = "\\r.sharepoint.com@SSL\DavWWWRoot\teams\NOE-RadioEng\Radio\New Build\_2019 NSB Tracker\Site_Integration_Report_Creator.xlsm"
OldFileName = "TEMPLATENew.xlsx"
NewFileName = Range("D9").Text 'grab new file name report creator
NewIntegration = GivenLocation & NewFileName 'New location and file name

'Functions
FileCopy TemplateWorkbook, NewWorkbook 'copy file to new location
Name GivenLocation & OldFileName As GivenLocation & NewFileName 'Rename based on H2 cell name

'MsgBox NewIntegration
'Workbooks.Open (NewIntegration)
'MsgBox NewIntegration
'Workbook.Close (NewIntegration)
'MsgBox Worksheets("Summary").Range("A1")

'copy values to new sheet
Worksheets("Site Page").Range("J2").Copy_ Workbooks(NewIntegration).Worksheets("Summary").Range("G1")
End Sub

"keeps breaking on the copy function" is not very descriptive - what happens when you run your code ?" — Tim Williams
Also as you're working with multiple workbooks, you should qualify every sheet/range reference with a workbook object. — Tim Williams
How do i qualify with a workbook object? I'll try with opening it too. — Andrew

Tim Williams Tim Williams · Accepted Answer · 2020-06-23T00:49:40

You can try something like this:

Dim wbNew As Workbook
'...
'...

Set wbNew = Workbooks.Open(NewIntegration, ReadOnly:=False)
'what is the source of the copy operation???
Worksheets("Site Page").Range("J2").Copy wbNew.Worksheets("Summary").Range("G1")
wbNew.Close True 'save and close

Copying Values from one Workbook to a newly created workbook

1 Answers