hi Peter,
This is more time commitment than just giving you the answer but are you ready to learn to fish...? 
#1: Sorry I'm not sure where I got that cell range from. Change this:
InsertLinks:
'### Insert links to other files
.Range(.Cells(5, ColToUpdate), .Cells(18, ColToUpdate)).FormulaR1C1 = "='[" & wbarr(i).Name & "]Export Data'!R[17]C2"
to
InsertLinks:
'### Insert links to other files
application.screenupdating = true 'delete line when finished testing
stop
Dim EqtnStr as string 'this could be put at the start of the macro
EqtnStr = "='[" & wbarr(i).Name & "]Export Data'!R[17]C2" '* change as needed
debug.print EqtnStr 'this shows the string in the Immediate Pane of the VBE & can be deleted when finished testing
.Range(.Cells(5, ColToUpdate), .Cells(18, ColToUpdate)).FormulaR1C1 = EqtnStr
stop
stop
application.screenupdating = false 'delete line when finished testing
'... rest of code
Manually create the links to the correct range for one file ie "=...Export Data'!$B41", now change the cell reference style to R1C1 via Tools - Options - ?(I can't remember the tab) within Excel, note down the equation which will look something like "...Export Data'!R[?]C2" & change this in the EqtnStr line of the above code.
Change the cell reference style back by unticking the "R1C1" option.
Next run the macro, it will run as normal until it gets to the first "Stop" line & then it will open the VBE & highlight this line yellow.
Press [ctrl + G] to open the Immediate pane within the VBE, press [F8] to step through the code "line by line" until the next Stop line. You can check that the string in the Immediate pane & that the formula in the spreadsheet is linking to the right range.
If it's not, try again... I'd do this by copying the "EqtnStr = " line into the Immediate pane, changing it, have the cursor on the line & press [enter] to "execute" the line of code which will change the contents of the Eqtnstr variable.
Copy the ".Range(.Cells(5, ColToUpdate),...= Eqtnstr" line into the Immediate pane, have the cursor on the line & press [enter]. Check the spreadsheet & see if it's correct - repeat as needed using the Immediate pane
Press [F5] to let the macro run by itself again, it will loop through the actions until it gest back to "Insert Links:" & the Stop line. If you want you can check again with this next file, or you can delete/comment out the stop lines & let the macro run.
#2: This is related to the missing "naming cell"...
in the Export Data ws, the column is headed Scenario A_,...
To be honest, it is just a matter of changing/linking the text to the "naming cell", there is no impact on how the macro will handle it.
and this can’t be because A only imports and the others only export.
If the file is "named as A", the export sheet becomes redundant to an extent but is still there because it is part of the standard template design & links to column b of the import data sheet. There are 2 possible approaches here:
- incorporate an If statement into the formulae on the Export Data sheet so that no data is returned if it is the "A" file.
- Add the below code immediately above the "ExitHandler:" label
'hide the export data sheet in the macro file
MasterFile.Worksheets("Import Data").Visible = False
NB: you may need to make some adjustment in the Thisworkbook module to take appropriate action to unhide the Export data sheet when the "A" file is saved/closed - have a think, see if you can solve it &/or ask.
So, it would be the selected Scenario, B…E.
No worries, delete/hide the column & change the title as needed. The macro works by choosing the appropriate column by searching the title row for the right scenario prefix (within the "IdHdrColumn" function).
If you have any more questions or once you've got this sorted, can you please upload the latest version of the file (make sure it includes a naming cell!)?
... how's the fishing, have you caught anything yet?
hth
Rob
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks