how to reference an open file dynamically in set

Petertt

New Member
Joined
Sep 13, 2012
Messages
5
I am stuck with the two yellow lines of code in yellow. I have two workbooks open, one master file and one source data file. Then i copy data from the source data file to master file and then save as a different file name by country, then repeat the same process for 20+ files. Below is what i got so far.

1. Open source data file in the same folder by referencing the cell value in a worksheet "Control" D2 of the master file
2. Once the source workbook is open, copy data in 'Sheet1" to master workbook sheet "Volume'.

i haven't got to the file saving step yet. Let me know if my message is clear. Thanks for your help in Advance!
Sub OpenworkbookAndUpdateVolume()

Application.ScreenUpdating = False
Dim wb As String
Dim wkb As Workbook
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long

wb = Worksheets("Control").Range("D2").Value
wb2 = Worksheets("Control").Range("J2").Value

Dim Path As String
Path = "c:\bookingkpi\"
ChDir Path
Workbooks.Open Filename:=wb
'Workbooks.Open Filename:=wb2

Set wkb = Workbooks("wb")
Set wsCopy = wkb.Worksheets("Sheet1")
Set wsDest = Workbooks("New Report.xlsm").Worksheets("Volume")
Set wsDest2 = Workbooks("New Report.xlsm").Worksheets("Active")
Set wsDest3 = Workbooks("New Report.xlsm").Worksheets("Count")

'==================================================================================
'Below procedures copy the volume data

'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

'3. Clear contents of existing data range
wsDest.Range("A32:FB" & lDestLastRow).ClearContents

'4. Copy & Paste Data to 'volume' sheet
wsCopySource.Range("A30:FB" & lCopyLastRow).Copy _
wsDest.Range("A32")

'5. Update list
wsDest.Range("A32:A" & lCopyLastRow).Copy _
wsDest2.Range("A32")



Application.ScreenUpdating = True
End Sub
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Petertt

New Member
Joined
Sep 13, 2012
Messages
5
I worked it out. The correct set statement should be 'Set wkb = Workbooks.Open(Filename:=wb)'. thanks!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,119
Messages
5,768,221
Members
425,460
Latest member
Astros1243

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