Copying data from one file to another using VBA

[s10gupta]

Hi all, I'm trying to use this VBA code to automate a weekly task which involves copying data from capinwk and capwk files into the inventory build file, then copying data from inventory build file into the capIBOB file. The last part of copying data from inventory build into the file called CapIBOB is not working correctly. When I run the code, the CapIBOB file is still blank. Can you spot any errors?

Sub AdjustmentsMacroV6()
'
Dim folderPath As String
Dim currentWeek As String
Dim inventoryBuildFile As String
Dim capwkFile As String
Dim capinwkFile As String
Dim capIBOBFile As String

' Set the folder path and current week number based on today's date
folderPath = "\\supply chain\group\capplan\2023\02 Weekly Reports\2023 Q3\W"
currentWeek = Format(Date, "ww")

' Construct the file names
inventoryBuildFile = folderPath & currentWeek & "\Inventory Build_wk" & currentWeek & ".xlsm"
capwkFile = folderPath & currentWeek & "\capwk_" & currentWeek & ".xlsm"
capinwkFile = folderPath & currentWeek & "\capinwk_wk" & currentWeek & ".xlsm"
capIBOBFile = folderPath & currentWeek & "\CapIBOB_wk" & currentWeek & ".xlsm"

' Open the inventory build file
Workbooks.Open inventoryBuildFile
Dim inventoryBuildWorkbook As Workbook
Set inventoryBuildWorkbook = ActiveWorkbook

' Clear and copy data in the inventory build file
inventoryBuildWorkbook.Names("LastWkIBFcstAdjs").RefersToRange.ClearContents
inventoryBuildWorkbook.Names("LastWkOBFcstAdjs").RefersToRange.ClearContents
inventoryBuildWorkbook.Names("ThisWkIBFcstAdjs").RefersToRange.Copy
inventoryBuildWorkbook.Sheets("LastWkIBFcstAdjs").Range("A2").PasteSpecial xlPasteValues

inventoryBuildWorkbook.Names("ThisWkOBFcstAdjs").RefersToRange.Copy
inventoryBuildWorkbook.Sheets("LastWkOBFcstAdjs").Range("A2").PasteSpecial xlPasteValues

' Open the capwk file
Workbooks.Open capwkFile
Dim capwkWorkbook As Workbook
Set capwkWorkbook = ActiveWorkbook

' Copy data from the capwk file to the inventory build file
capwkWorkbook.Names("ThisWkFcstAdjs").RefersToRange.Copy
inventoryBuildWorkbook.Sheets("ThisWkOBFcstAdjs").Range("K2").PasteSpecial xlPasteValues

' Open the capinwk file
Workbooks.Open capinwkFile
Dim capinwkWorkbook As Workbook
Set capinwkWorkbook = ActiveWorkbook

' Copy data from the capinwk file to the inventory build file
capinwkWorkbook.Names("ThisWkFcstAdjs").RefersToRange.Copy
inventoryBuildWorkbook.Sheets("ThisWkIBFcstAdjs").Range("K2").PasteSpecial xlPasteValues

' Open the CapIBOB file
Workbooks.Open capIBOBFile
Dim CapIBOBworkbook As Workbook
Set CapIBOBworkbook = ActiveWorkbook

' Clear and copy data in the CapIBOB file
CapIBOBworkbook.Names("LastWkIBFcstAdjs").RefersToRange.ClearContents
CapIBOBworkbook.Names("LastWkOBFcstAdjs").RefersToRange.ClearContents
CapIBOBworkbook.Names("ThisWkIBFcstAdjs").RefersToRange.Copy
CapIBOBworkbook.Sheets("LastWkIBFcstAdjs").Range("A3").PasteSpecial xlPasteValues
CapIBOBworkbook.Names("ThisWkOBFcstAdjs").RefersToRange.Copy
CapIBOBworkbook.Sheets("LastWkIBFcstAdjs").Range("A3").PasteSpecial xlPasteValues

' Copy data from the Inventory Build file to the CapIBOB file
inventoryBuildWorkbook.Names("ThisWkIBFcstAdjs").RefersToRange.Copy
CapIBOBworkbook.Sheets("ThisWkIBFcstAdjs").Range("A3").PasteSpecial xlPasteValues

inventoryBuildWorkbook.Names("ThisWkOBFcstAdjs").RefersToRange.Copy
CapIBOBworkbook.Sheets("ThisWkOBFcstAdjs").Range("A3").PasteSpecial xlPasteValues


' Save and close the workbooks
capinwkWorkbook.Close SaveChanges:=False
capwkWorkbook.Close SaveChanges:=False
inventoryBuildWorkbook.Save
inventoryBuildWorkbook.Close SaveChanges:=False
CapIBOBworkbook.Save
CapIBOBworkbook.Close SaveChanges:=False

' Release object references
Set capinwkWorkbook = Nothing
Set capwkWorkbook = Nothing
Set inventoryBuildWorkbook = Nothing
Set CapIBOBworkbook = Nothing

MsgBox "Weekly task automation completed!"


End Sub

 

[I_know_nuffin]

where is 'RefersToRange' defined?