Multi-Wookbook Macro

[JonReyno]

Hi Guys,

Is it possible to have a Macro on a 'Master' Workbook which, when activated, adds the value of the same cell from 4 other Workbooks?

All of the 5 workbooks are exactly the same layout, the 1st one is the Master one which is where I would like the collate inforamtion from the other 4 to go to.

For example: Workbook 1, Cell A1 = 2... Workbook 2, Cell A1 = 1... Workbook 3, Cell A1 = 0 and Workbook 4, Cell A1 = 0. Matser Workbook then takes the information from Cell A1 of each of the workbooks and puts it in to Cell A1 of the Master Workbook to get a total, in this example 3...

I'm clutching at straws and don't know if it's possible, any help would be apprecaited.

Thanks
Jon

 

[jc.021286]

Okay,
and it should be:
'S:\...\[BSC 2010 - Chris Lawrence.xls]Jan ''10'!R6C7

need to specify that we are looking for the .xls
so change to look like this.

if b = 1 then FF = "BSC 2010 - Chris Lawrence" & ".xls"
if b = 2 then FF = "BSC 2010 - Dave Spinner" & ".xls"
if b = 3 then FF = "BSC 2010 - Peter Wyatt" & ".xls"
if b = 4 then FF = "BSC 2010 - Derek Timms" & ".xls"
</pre>

see if that works, unfortunately, the appostrophe that you currently employ may make things difficult if the books are not factoring them in properly. As we would need the .name to understand that it needs to be a reference, I'll need to do a bit of quick research on that matter unless you do not mind changing to 2010.

jc

OK, the details in those 2 cells are as follows:
<table style="width: 445px; border-collapse: collapse; height: 39px;" x:str="" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(236, 233, 216); width: 48pt; height: 12.75pt; background-color: transparent;" x:str="'S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R6C7" height="17" width="64">S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R6C7</td></tr></tbody></table>&
S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R7C7
<table style="border-collapse: collapse;" x:str="" border="0" cellpadding="0" cellspacing="0" height="32" width="496"><colgroup><col style="width: 48pt;" width="64"></colgroup><tbody><tr style="height: 12.75pt;" height="17"><td style="border: medium none rgb(236, 233, 216); width: 48pt; height: 12.75pt; background-color: transparent;" x:str="'S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan '10'!R7C7" height="17" width="64">
</td></tr></tbody></table>

 

[JonReyno]

It still comes up with the error on the same line 'GetValue2'.

I put the cells(100,1) = arg1 Cells(101,1) = arg2 in again and it is now showing the .xls on there.

Any other thoughts?

 

[jc.021286]

okay,
in the master and in the first file, change the worksheet names to not include the apostrophe.
so
S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card\Chris Lawrence\[BSC 2010 - Chris Lawrence]Jan 2010'!R7C7

it is that first apostrophe that is essentially telling VBA to stop short and because we are using the .name to pick it up, we are at this point not picking up the referencing style required. Still looking into that at the moment.

If you can make that change to the 2 workbooks as I look for the solution, in order to see if that is what is currently holding us up.

regards,
jc

 

[JonReyno]

That seems to have worked. I didn't change Feb '10 and then that is when it came up with the error. Do you want me to change the names of all the tabs in all 5 spreadsheets to say 2010 rather than '10?

 

[jc.021286]

thinking of our current issue;

SS = sheets(a).name
SS = SS & "'!"

making a change to

SS = sheets(a).name
SS = Left(SS,4) & "'" & Right(SS,3) & "'!"

Could make the change that we require...
Oh you just posted, 1 sec as I read

 

[jc.021286]

Try changing it back to what you had and make those changes I just specified, it may allow you to skip having to change everything.

let me know
jc

 

[JonReyno]

The amendment to the code worked to a degree. It goes through the first steps no problem, when it repeats (which I assume is for the next spreadsheet) it comes up with the follows:

'Run-time error '13': Type Mismatch'

when it gets to:

'Sheets(a).Cells(14, 7) = Sheets(a).Cells(14, 7) + GetValue6'

 

[jc.021286]

to a degree? what seems to be the issue.

the type miss match could be due to the figure appearing as text.
at the top, Dim GetValue1,... as Long

this may solve the problem, also, what is the value that got loaded into the GetValue6!? try hovering the mouse or use the cells() = GetValue to check it out. For some reason it is unable to add them together.

jc

 

[JonReyno]

Could it be due to the cell it is refering to is a % and not just a number?

This looks to be the case to me, if that is the problem then I can just change it back to a number.

There is also a cell which is a cost, would this be likely to come up with the same issue?

 

[jc.021286]

it could be,
I do not know what data you are summarizing. I would hope that the Dim _ as long would fix that, long being a decimal number.

it is just weird that it works for other sections and stops mid code at one section. Thus, I was thinking if it was trying to do = Blue + 27% there would be an issue. Though if adding percentages, should not be a problem. You could do individual DIM _ref_ as percentage, DIM as integer
or set it Sheets(a).Cells(,).Formula = "=" & Sheets(a).Cells(,) & "+" & GetValue6