How to multi reference to cells in a macro


New Member
Jan 6, 2009
Hi, I hope someone can help me with this code I've been trying to work out... let me try to explain what it is I'm doing...

I currently have a macro which works through a list of Templates (Workbooks) to update them from a Data Source (a different Workbook).

The name of Template is picked up from a column in the macro workbook (as it works it's way down the list), but the data source location is partly hard coded into the macro and partly comes from what you type in a cell.
The macro looks for the template in the specified folder, takes the data from the relevant source workbook sheet according to the template name, pastes it into the relevant tab in the template, updates the pivot tables and saves the template as a different name according to the value in a cell... it does this for all the templates in the list for the same data source fine.

What I'd like to be able to do is get the macro to refer to, say column (A, X) for the template, and refer to column (B, X) for the name of the data source workbook. So I can add more templates and Data sources to the list if needs be. There will be occasions when various templates will need updating from the one data source so would also like it to check if the data source is already open etc.

I hope that makes sense?! The bit I need to add to this code is for the check if the data source is open, if it's not, open it, etc. And to open/ refer to the data source according to the template name.

This is the code I currently have, I've amended some of it to make it more generic;

Public Sub CommandButton1_Click()

Dim xlsRangeSource As Range 'Data in Region Pivots workbook
Dim xlsRangeDestination As Range 'Data in 'Data' worksheet of relevant pivot workbook

Dim x As Integer

Dim xlsSheetDestination As Worksheet
Dim xlsSheetSource As Worksheet
Dim xlsSheetList As Worksheet
Dim xlsBookSource As Workbook
Dim xlsPivot
Dim DateCell
Dim Folder
Dim SavePath

On Error Resume Next
Set xlsSheetList = ThisWorkbook.Worksheets("Sheet1") 'This Workbook
WeekNo = (xlsSheetList.Cells(7, 3)) 'Week
Set xlsBookSource = Workbooks.Open("N:\" + WeekNo + "\Data Source Name.xls") 'Opens pre-saved 'Data' workbook in this weeks folder and activates 'Data' Worksheet
DateCell = (xlsSheetList.Cells(7, 2)) 'Date
SavePath = "N:\" + WeekNo + "\" 'Tells it where to save the Pivot table after it's been updated

If xlsBookSource Is Nothing Then 'If the 'Data' workbook is not open

Set xlsBookSource = Workbooks.Open(Application.FileDialog(msoFileDialogOpen).SelectedItems(1)) 'Select file

End If

x = 7 'Start at Row 7
While xlsSheetList.Cells(x, 1) <> "" 'In this work book cells (x, 1), Do until you get a black cell
Set xlsSheetDestination = Workbooks.Open("N:\" + (xlsSheetList.Cells(x, 1) + ".xls")).Worksheets("Data") 'Open the pivot template
Set xlsSheetSource = xlsBookSource.Worksheets(CStr(xlsSheetList.Cells(x, 1))) 'From the Data Workbook, match the right tab to the Pivot report name.
Set xlsPivot = (xlsSheetList.Cells(x, 1)) 'Takes value from list and forms part of Pivot report name

xlsSheetSource.Activate 'Select the relevant Tab in the Data Workbook
xlsSheetSource.Cells.Select 'Select all Data
Selection.Copy 'Copy
xlsSheetDestination.Activate 'Open the relevant Pivot report onto the 'Data' Sheet
xlsSheetDestination.Cells.Select 'Select all
ActiveSheet.Paste 'Paste
Application.CutCopyMode = False
ActiveWorkbook.RefreshAll 'Refesh all Pivot tables
xlsSheetDestination.Activate 'Select Pivot Report
Worksheets("Control").Cells(11, 3) = DateCell 'Updates date on front sheet of pivot report
ActiveWorkbook.SaveAs fileName:=SavePath & xlsPivot & " Pivot - " & DateCell & ".xls", _
FileFormat:=xlNormal, _
Password:="", _
WriteResPassword:="", _
ReadOnlyRecommended:=True, _
x = x + 1 'Go to next record

xlsBookSource.Close (False)

End Sub

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Forum statistics

Latest member

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