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

 

[chicagodiceman]

Can't you use links via a formula to calculate your master workbook?

=workbook1!a1 + workbook2!a1 + , etc.

 

[jc.021286]

Hey there,

Look at the macro I constructed here
http://www.mrexcel.com/forum/showthread.php?t=472304&page=2

it is a bit more complex then you require, though would find the Directory and associate the proper cells to pull the information you require.

it will do the task without opening the workbooks. Though may be at a much higher level then what you need.

Regards,
Jc

 

[JonReyno]

Hi JC,

Thanks for the reply. I have looked at the code which you put on the prveious thread but I can't really make head nor tail of it to see how I can tweak it to what I need.

I'm liking the sound of having the master spreadsheet update without the need to open the other workbooks but I need to just figure out what your code means. Are you able to explain in simple terms what your code would achieve (the one in the other thread) so I can understand how it works?

Thanks
Jon

 

[jc.021286]

Most certainly

The code is separated into several components
First we construct the path or where the file is located, here we chose teh C:\ drive and then MM would be subsequent subfolders if any, currently it is set to blank (ie: file is located there)

Path = "C:\"   ' where your data is located
    MM = "" ' sub folder if any...

then we set up a loop for processing the data, so in their instance we were pulling several thousands of cells

For a = 1 To 3000

lastly we need to tell excel how to find the information. First set of lines is simply to check if the file exists or not, if not you would put in a msg box or exit the sub.
we construct the file to go to the directory, find the file and pull the cell specified (ie CC1)
Combine this into our argument and execute the macro to load that value into our GetValue.
Then put GetValue where needed in your own

         arg1 = "'" & Path & "[" & SS & "]" & CC ' construct path and file name
    If Dir(arg1) = "" Then
        GetValue = "File Not Found"
            End If
        
   FF = Dir(arg1)
    SS = "report'!"   'sheet the data is stored on
    CC1 = Cells(a, 13).Address(, , xlR1C1)    'the cells we shall be working with (column M)
    
'Combine into argument
    arg1 = "'" & Path & "[" & FF & "]" & SS & CC1
    
'
    GetValue1 = ExecuteExcel4Macro(arg1)
    Next a
'
End Sub

Does this make a bit more sense?
The loop is to gather a lot of data

let me know and we can start to try to apply it to your unique needs,
regards,
jc

 

[JonReyno]

Hey JC,

That does make a bit more sense, so thanks for the breakdown of what you put.

I'm assuming this code would need to be set into the Master spreadsheet to call the data from the other spreadsheets?

I'll give you the specific details so you know what I'm working with:

The Spreadsheets are:

BSC 2010 - Master
BSC 2010 - PW
BSC 2010 - DT
BSC 2010 - CL
BSC 2010 - DS
​

Each of the spreadsheets are laid out exactly the same. They all consist of tabs January through to December, again all of these tabs are in the same layout and format just with different months.

The cells that I want to pull the infomation from are:

G/H (Merged Cells) 6
G/H 7
G/H 10
G/H 11
G/H 12
G/H 14
G/H 15
G/H 16
G/H 17
G/H 18
G/H 20
G/H 21
G/H 23
G/H 24
G/H 25
G/H 26
G/H 27
​

If I need to alter the merged cells, I should be able to not too much hassle.

If we can get a code to run from the Master spreadsheet to pull from the other 4 spreadsheets either automatically or via a Macro Button (which I know how to do! Only becuase it's easy) then that would be great.

Thanks for your help on this, you could be a life saver

 

[jc.021286]

Okay, simple enough to have it from the master spreadsheet.

You will want to create a CC# for each variable
so CC1 = G/H 6 'which will be Cells(6,7) (6,7) is row/column respectively
__CC2 = Cells(7,7)
... and so on and so forth in the same manner that i have.

You specified that they are on monthly tabs, we will need to know what tabs go where, or do all tabs need to go to your BSC 2010 - Master?

we will need the file name, what I have labeled as FF
so that in the end when we construct the argument we are saying go look at
'C:/Data/[file1]my_data_sheet1'!G6
that is the most finicky portion, then all we do is create a loop to pull all instances of your data.

The basics are
1) "DIM" to gives proper dimensions to our variables
2) construct the argument (arg1,arg4,arg27) so that it will look like the above
3) sub arg# into your executemacro(arg#) line to load that # into a GetValue variable
4) set the destination cell in the master to your GetValue

Wash rinse repeat for all variables that you have.

Give it a try to map it out as you will want it and post the code, that will give me the best idea of what you have and where it goes. Then I can make the small changes to get you operational today if possible.

Regards,
jc

Hey JC,

That does make a bit more sense, so thanks for the breakdown of what you put.

I'm assuming this code would need to be set into the Master spreadsheet to call the data from the other spreadsheets?

I'll give you the specific details so you know what I'm working with:

The Spreadsheets are:

BSC 2010 - Master
BSC 2010 - PW
BSC 2010 - DT
BSC 2010 - CL
BSC 2010 - DS
​

Each of the spreadsheets are laid out exactly the same. They all consist of tabs January through to December, again all of these tabs are in the same layout and format just with different months.

The cells that I want to pull the infomation from are:

G/H (Merged Cells) 6
G/H 7
G/H 10
G/H 11
G/H 12
G/H 14
G/H 15
G/H 16
G/H 17
G/H 18
G/H 20
G/H 21
G/H 23
G/H 24
G/H 25
G/H 26
G/H 27
​

If I need to alter the merged cells, I should be able to not too much hassle.

If we can get a code to run from the Master spreadsheet to pull from the other 4 spreadsheets either automatically or via a Macro Button (which I know how to do! Only becuase it's easy) then that would be great.

Thanks for your help on this, you could be a life saver

 

[JonReyno]

OK, this is really testing my brain power today (having a mental block day I'm afraid).

I can sort out the CC#'s as they make sense to me and I can also provide you with the file name/path showing where the spreadsheets will sit, so that shouldn't be a problem either, I'm just getting lost where it comes to the other stuff (DIM, arguments etc). I'm only very new to all this and maybe punchnig above my weight but I will give you the CC#'s and file reference and we can go from there, if thats ok?

 

[jc.021286]

okay,
For myself, I actually ignore the Dim, ya shame on me.
you will want
Dim path, mm, ss, ff, cc1, cc2, ... , cc20 as String
Dim arg1, arg2, ... as Variant

then build the loop, and inside the loop all i need is the
cc1 = cells(6,7).address(, , xlR1C1)
cc2 = sheets("January").cells(etc. etc)
cc3 = where is the data coming from

path = you know
the arguments are easy to build you will want to keep them in teh format I specified earlier, if not, I'll adjust those as needed for you.

getvalue1 = executeexecelmacro4(arg1)
getvalue2 = ...
repeat for all instances

'master sheet data
cells( , ) = Getvalue1
show me where you need this information to go, or give me a general conceptualization, are we putting it in whatever blank row there is? specific spot so you can do calculations? etc etc.

so I just need you to show me the start and finish and i'll do the inbetween stuff.

Regards,
jc


Sub Retrieve()
Dim a As Integer
'
Path = "C:\Temp\" ' where your data is located
MM = "Data File" & "\" ' sub folder if any...
For a = 1 To 3000
arg1 = "" & Path & MM & "Master*" & "" ' construct path and file name
If Dir(arg1) = "" Then
GetValue = "File Not Found"
Exit Sub
End If
FF = Dir(arg1)
SS = "Summary'!" 'sheet the data is stored on
CC1 = Cells(a, 13).Address(, , xlR1C1) 'the cells we shall be working with (column M)
CC2 = Cells(a, 37).Address(, , xlR1C1) 'AK
CC3 = Cells(a, 29).Address(, , xlR1C1) 'AC
CC4 = Cells(a, 56).Address(, , xlR1C1) 'BD
CC5 = Cells(a, 92).Address(, , xlR1C1) 'CN
CC6 = Cells(a, 52).Address(, , xlR1C1) 'AZ
CC7 = Cells(a, 8).Address(, , xlR1C1) 'H
CC8 = Cells(a, 35).Address(, , xlR1C1) 'AI
CC9 = Cells(a, 27).Address(, , xlR1C1) 'AA
CC10 = Cells(a, 54).Address(, , xlR1C1) 'BB
CC11 = Cells(a, 90).Address(, , xlR1C1) 'CL
CC12 = Cells(a, 12).Address(, , xlR1C1) 'L
CC13 = Cells(a, 34).Address(, , xlR1C1) 'AH
CC14 = Cells(a, 26).Address(, , xlR1C1) 'Z
CC15 = Cells(a, 53).Address(, , xlR1C1) 'Ba
CC16 = Cells(a, 89).Address(, , xlR1C1) 'Ck
'Combine into argument
arg1 = "'" & Path & MM & "[" & FF & "]" & SS & CC1
arg2 = "'" & Path & MM & "[" & FF & "]" & SS & CC2
arg3 = "'" & Path & MM & "[" & FF & "]" & SS & CC3
arg4 = "'" & Path & MM & "[" & FF & "]" & SS & CC4
arg5 = "'" & Path & MM & "[" & FF & "]" & SS & CC5
arg6 = "'" & Path & MM & "[" & FF & "]" & SS & CC6
arg7 = "'" & Path & MM & "[" & FF & "]" & SS & CC7
arg8 = "'" & Path & MM & "[" & FF & "]" & SS & CC8
arg9 = "'" & Path & MM & "[" & FF & "]" & SS & CC9
arg10 = "'" & Path & MM & "[" & FF & "]" & SS & CC10
arg11 = "'" & Path & MM & "[" & FF & "]" & SS & CC11
arg12 = "'" & Path & MM & "[" & FF & "]" & SS & CC12
arg13 = "'" & Path & MM & "[" & FF & "]" & SS & CC13
arg14 = "'" & Path & MM & "[" & FF & "]" & SS & CC14
arg15 = "'" & Path & MM & "[" & FF & "]" & SS & CC15
arg16 = "'" & Path & MM & "[" & FF & "]" & SS & CC16
'
GetValue1 = ExecuteExcel4Macro(arg1)
GetValue2 = ExecuteExcel4Macro(arg2)
GetValue3 = ExecuteExcel4Macro(arg3)
GetValue4 = ExecuteExcel4Macro(arg4)
GetValue5 = ExecuteExcel4Macro(arg5)
GetValue6 = ExecuteExcel4Macro(arg6)
GetValue7 = ExecuteExcel4Macro(arg7)
GetValue8 = ExecuteExcel4Macro(arg8)
GetValue9 = ExecuteExcel4Macro(arg9)
GetValue10 = ExecuteExcel4Macro(arg10)
GetValue11 = ExecuteExcel4Macro(arg11)
GetValue12 = ExecuteExcel4Macro(arg12)
GetValue13 = ExecuteExcel4Macro(arg13)
GetValue14 = ExecuteExcel4Macro(arg14)
GetValue15 = ExecuteExcel4Macro(arg15)
GetValue16 = ExecuteExcel4Macro(arg16)
'
' at this point all the Get Values now hold the information that is important to us.
'we need to now place it in the sheet where required.
'

Cells(7*a - 5, 1) = GetValue6
Cells(7*a - 4, 3) = GetValue1
Cells(7*a - 3, 3) = GetValue2
Cells(7*a - 2, 3) = GetValue3
Cells(7*a - 1, 3) = GetValue4
Cells(7*a, 3) = GetValue5
Cells(7*a - 4, 4) = GetValue7
Cells(7*a - 3, 4) = GetValue8
Cells(7*a - 2, 4) = GetValue9
Cells(7*a - 1, 4) = GetValue10
Cells(7*a, 4) = GetValue11
Cells(7*a - 4, 4) = GetValue12
Cells(7*a - 3, 4) = GetValue13
Cells(7*a - 2, 4) = GetValue14
Cells(7*a - 1, 4) = GetValue15
Cells(7*a, 4) = GetValue16
Cells(7*a - 4, 2) = "Actual"
Cells(7*a - 3, 2) = "Design"
Cells(7*a - 2, 2) = "Code"
Cells(7*a - 1, 2) = "QAC"
Cells(7*a, 2) = "Unit Test"
Next a
'
'</pre>

 

[JonReyno]

Hey JC,

OK, the path where the files will sit is:

S:\Power Networks\Asset Management\SND\OPC\WAYLEAVES\Balance Score Card

The Master File (file name: BSC 2010 - Master) will sit in this folder. The remaining 4 other spreadsheets (file names: BSC 2010 - Chris Lawrence, BSC 2010 - Dave Spinner, BSC 2010 - Derek Timms & BSC 2010 - Peter Wyatt) will be in seperate folders named:

Chris Lawrence
Dave Spinner
Peter Wyatt
Derek Timms

The CC references are as follows:

CC1 = Cell 6,7
CC2 = Cell 7,7
CC3 = Cell 10,7
CC4 = Cell 11,7
CC5 = Cell 12,7
CC6 = Cell 14,7
CC7 = Cell 15,7
CC8 = Cell 16,7
CC9 = Cell 17,7
CC10 = Cell 18,7
CC11 = Cell 20,7
CC12 = Cell 21,7
CC13 = Cell 23,7
CC14 = Cell 24,7
CC15 = Cell 25,7
CC16 = Cell 26,7
CC17 = Cell 27,7

As previously mentioned, there are 12 tabs for the months of the year on each spreadsheet. I would like January from the 4 spreadsheets to be totalled in January of the Master and so on for each month. The CC#s above show where the information is coming from and going to (CC1 to be taken from all 4 spreadsheets and then totalled in CC1 on the Master spreadsheet).

I hope this is what you want. Is there any more information you need me to supply?

Thanks for all your help on this, I know your probably extremely busy and don't want to be having to hold my hand through all this but hopefully I will gain better knowledge from your assistance.

Cheers
Jon