How to multi reference to cells in a macro

[excellor]

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

Application.FileDialog(msoFileDialogOpen).Show
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").Activate
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, _
CreateBackup:=False
ActiveWorkbook.Close
x = x + 1 'Go to next record
Wend


xlsBookSource.Close (False)

End Sub

 

[excellor]

Anyone?

 

[excellor]