macros for linking multiple workbooks to one workbook

D

dac803

I have a workbook for each team with 31 worksheets in each workbook for each
day of the month. These workbooks are linked to a monthly workbook with a
row for each team. I am looking for a macro to run that will link daily
totals for each day into the monthly workbook. I hope this is clear.
 
J

Joel

Linking into multiple workbooks slows opening a workbook. It is better to
just put the daily totals into the workbook. I need more information to help
write a macro

1) The names of the 31 worksheets
2) The source and destination locations of the data.
3) the worksheet name(s) in the monhthly total workbook
 
D

dac803

Joel,

I hope this is the information you asked for.

1.The names of the worksheets are 01-Dec thru to 31-Dec.
2.The source and destinations are a group drive with different access writes.
3. team names like West A for workbook names.

As with most work environments a workbook has totals of the whole area not
team names and different management levels require different formats.
 
J

Joel

Can you record a macro while you perform one link and post the code.

1) Start Recording from worksheet menu Tools - Macro -Record enw Macro
2) Perform you links
3) Stop Recording - tools - Macro Stop Recording
4) go to VBA wiondow and get Macro. Alt-F11 to get to VBA window. Find
macro in Module 1.
 
D

dac803

Thanks Joel, I had already tried that, I have put the code below that the
macro recorded. I have to put this into 31 worksheets for the month changing
the date for each date and for 8 lines(teams). running a macro that would do
it for the whole workbook would be greater use of time.


ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C3"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C5"
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C7"
Range("H8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C8"
Range("I8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C9"
Range("J8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C10"
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C11"
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C12"
Range("M8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C13"
Range("N8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C14"
Range("O8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C15"
Range("P8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C16"
Range("Q8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C17"
Range("R8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C18"
Range("S8").Select
ActiveCell.FormulaR1C1 = "="
Range("S8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C19"
Range("T8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C20"
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C21"
Range("U9").Select
End Sub
dacbam
 
J

Joel

I created an array where you can put each team name workbook. I still think
you are going to ask for some additional moidification like selecting the
date(s). This code should give you some ideas on how to create a macro to
link the workbook(s). This code would go in the monthly workbook.

You could put this in a workbook which will automatically open the monthly
workbook. Even create the monthly workbook if it doesn't exist. I thought is
would be best to start simple and then add to the code.



Sub test()

Teams = Array("A - Dec 08 -Summary -West A.xls", "A - Dec 08 -Summary -East
A.xls")

RowCount = 28
For Each Team In Teams
BKName = "'[" & Team & "]"
ShtName = BKName & Format(Date, "DD-MMM") & "'"
MyFormula = "=" & ShtName & "!"
Range("C" & RowCount).Formula = MyFormula & "C28"
Range("D" & RowCount).Formula = MyFormula & "D28"
Range("E" & RowCount).Formula = MyFormula & "E28"
Range("G" & RowCount).Formula = MyFormula & "G28"
Range("H" & RowCount).Formula = MyFormula & "H28"
Range("I" & RowCount).Formula = MyFormula & "I28"
Range("J" & RowCount).Formula = MyFormula & "J28"
Range("K" & RowCount).Formula = MyFormula & "K28"
Range("L" & RowCount).Formula = MyFormula & "L28"
Range("M" & RowCount).Formula = MyFormula & "M28"
Range("N" & RowCount).Formula = MyFormula & "N28"
Range("O" & RowCount).Formula = MyFormula & "O28"
Range("P" & RowCount).Formula = MyFormula & "P28"
Range("Q" & RowCount).Formula = MyFormula & "Q28"
Range("R" & RowCount).Formula = MyFormula & "R28"
Range("S" & RowCount).Formula = MyFormula & "S28"
Range("T" & RowCount).Formula = MyFormula & "T28"
Range("U" & RowCount).Formula = MyFormula & "U28"

RowCount = RowCount + 1
Next Team
End Sub


dac803 said:
Thanks Joel, I had already tried that, I have put the code below that the
macro recorded. I have to put this into 31 worksheets for the month changing
the date for each date and for 8 lines(teams). running a macro that would do
it for the whole workbook would be greater use of time.


ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C3"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C5"
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C7"
Range("H8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C8"
Range("I8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C9"
Range("J8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C10"
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C11"
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C12"
Range("M8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C13"
Range("N8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C14"
Range("O8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C15"
Range("P8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C16"
Range("Q8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C17"
Range("R8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C18"
Range("S8").Select
ActiveCell.FormulaR1C1 = "="
Range("S8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C19"
Range("T8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C20"
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C21"
Range("U9").Select
End Sub
dacbam


Joel said:
Can you record a macro while you perform one link and post the code.

1) Start Recording from worksheet menu Tools - Macro -Record enw Macro
2) Perform you links
3) Stop Recording - tools - Macro Stop Recording
4) go to VBA wiondow and get Macro. Alt-F11 to get to VBA window. Find
macro in Module 1.
 
D

dac803

Thanks Joel, I tried this.
I copied this into the spreadsheet and ran it, the results formula is placed
in Row 28 of the monthly and not in the row required in the case of west A it
needs to start at cell C8 and end in U8 then the next row for West b is C9 to
U9 and so on for the teams.
Cell C
8 West A formula location which links back to the team workbook
9 West B
10 West C
11 West D
12 West E
I am not sure that I am being all that clear. I have created several
workbooks and the data is collected in a monthly workbook.

dacbam


Joel said:
I created an array where you can put each team name workbook. I still think
you are going to ask for some additional moidification like selecting the
date(s). This code should give you some ideas on how to create a macro to
link the workbook(s). This code would go in the monthly workbook.

You could put this in a workbook which will automatically open the monthly
workbook. Even create the monthly workbook if it doesn't exist. I thought is
would be best to start simple and then add to the code.



Sub test()

Teams = Array("A - Dec 08 -Summary -West A.xls", "A - Dec 08 -Summary -East
A.xls")

RowCount = 28
For Each Team In Teams
BKName = "'[" & Team & "]"
ShtName = BKName & Format(Date, "DD-MMM") & "'"
MyFormula = "=" & ShtName & "!"
Range("C" & RowCount).Formula = MyFormula & "C28"
Range("D" & RowCount).Formula = MyFormula & "D28"
Range("E" & RowCount).Formula = MyFormula & "E28"
Range("G" & RowCount).Formula = MyFormula & "G28"
Range("H" & RowCount).Formula = MyFormula & "H28"
Range("I" & RowCount).Formula = MyFormula & "I28"
Range("J" & RowCount).Formula = MyFormula & "J28"
Range("K" & RowCount).Formula = MyFormula & "K28"
Range("L" & RowCount).Formula = MyFormula & "L28"
Range("M" & RowCount).Formula = MyFormula & "M28"
Range("N" & RowCount).Formula = MyFormula & "N28"
Range("O" & RowCount).Formula = MyFormula & "O28"
Range("P" & RowCount).Formula = MyFormula & "P28"
Range("Q" & RowCount).Formula = MyFormula & "Q28"
Range("R" & RowCount).Formula = MyFormula & "R28"
Range("S" & RowCount).Formula = MyFormula & "S28"
Range("T" & RowCount).Formula = MyFormula & "T28"
Range("U" & RowCount).Formula = MyFormula & "U28"

RowCount = RowCount + 1
Next Team
End Sub


dac803 said:
Thanks Joel, I had already tried that, I have put the code below that the
macro recorded. I have to put this into 31 worksheets for the month changing
the date for each date and for 8 lines(teams). running a macro that would do
it for the whole workbook would be greater use of time.


ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C3"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C5"
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C7"
Range("H8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C8"
Range("I8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C9"
Range("J8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C10"
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C11"
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C12"
Range("M8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C13"
Range("N8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C14"
Range("O8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C15"
Range("P8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C16"
Range("Q8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C17"
Range("R8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C18"
Range("S8").Select
ActiveCell.FormulaR1C1 = "="
Range("S8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C19"
Range("T8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C20"
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C21"
Range("U9").Select
End Sub
dacbam


Joel said:
Can you record a macro while you perform one link and post the code.

1) Start Recording from worksheet menu Tools - Macro -Record enw Macro
2) Perform you links
3) Stop Recording - tools - Macro Stop Recording
4) go to VBA wiondow and get Macro. Alt-F11 to get to VBA window. Find
macro in Module 1.

:

Joel,

I hope this is the information you asked for.

1.The names of the worksheets are 01-Dec thru to 31-Dec.
2.The source and destinations are a group drive with different access writes.
3. team names like West A for workbook names.

As with most work environments a workbook has totals of the whole area not
team names and different management levels require different formats.

--
dacbam


:

Linking into multiple workbooks slows opening a workbook. It is better to
just put the daily totals into the workbook. I need more information to help
write a macro

1) The names of the 31 worksheets
2) The source and destination locations of the data.
3) the worksheet name(s) in the monhthly total workbook

:

I have a workbook for each team with 31 worksheets in each workbook for each
day of the month. These workbooks are linked to a monthly workbook with a
row for each team. I am looking for a macro to run that will link daily
totals for each day into the monthly workbook. I hope this is clear.
 
J

Joel

I just had to change the initial value of Rowcount to 8 instead of 28. I
also made it a little easier to change the date and make an array of Team
Names.

Sub test()

MyDate = "Dec 08"
Teams = Array("West A", "West B", "West C", "West D", "West E")

RowCount = 8
For Each Team In Teams
FName = "A - " & MyDate & " -Summary -" & Team & ".xls"
BKName = "'[" & FName & "]"
ShtName = BKName & Format(Date, "DD-MMM") & "'"
MyFormula = "=" & ShtName & "!"
Range("C" & RowCount).Formula = MyFormula & "C28"
Range("D" & RowCount).Formula = MyFormula & "D28"
Range("E" & RowCount).Formula = MyFormula & "E28"
Range("G" & RowCount).Formula = MyFormula & "G28"
Range("H" & RowCount).Formula = MyFormula & "H28"
Range("I" & RowCount).Formula = MyFormula & "I28"
Range("J" & RowCount).Formula = MyFormula & "J28"
Range("K" & RowCount).Formula = MyFormula & "K28"
Range("L" & RowCount).Formula = MyFormula & "L28"
Range("M" & RowCount).Formula = MyFormula & "M28"
Range("N" & RowCount).Formula = MyFormula & "N28"
Range("O" & RowCount).Formula = MyFormula & "O28"
Range("P" & RowCount).Formula = MyFormula & "P28"
Range("Q" & RowCount).Formula = MyFormula & "Q28"
Range("R" & RowCount).Formula = MyFormula & "R28"
Range("S" & RowCount).Formula = MyFormula & "S28"
Range("T" & RowCount).Formula = MyFormula & "T28"
Range("U" & RowCount).Formula = MyFormula & "U28"

RowCount = RowCount + 1
Next Team
End Sub


dac803 said:
Thanks Joel, I tried this.
I copied this into the spreadsheet and ran it, the results formula is placed
in Row 28 of the monthly and not in the row required in the case of west A it
needs to start at cell C8 and end in U8 then the next row for West b is C9 to
U9 and so on for the teams.
Cell C
8 West A formula location which links back to the team workbook
9 West B
10 West C
11 West D
12 West E
I am not sure that I am being all that clear. I have created several
workbooks and the data is collected in a monthly workbook.

dacbam


Joel said:
I created an array where you can put each team name workbook. I still think
you are going to ask for some additional moidification like selecting the
date(s). This code should give you some ideas on how to create a macro to
link the workbook(s). This code would go in the monthly workbook.

You could put this in a workbook which will automatically open the monthly
workbook. Even create the monthly workbook if it doesn't exist. I thought is
would be best to start simple and then add to the code.



Sub test()

Teams = Array("A - Dec 08 -Summary -West A.xls", "A - Dec 08 -Summary -East
A.xls")

RowCount = 28
For Each Team In Teams
BKName = "'[" & Team & "]"
ShtName = BKName & Format(Date, "DD-MMM") & "'"
MyFormula = "=" & ShtName & "!"
Range("C" & RowCount).Formula = MyFormula & "C28"
Range("D" & RowCount).Formula = MyFormula & "D28"
Range("E" & RowCount).Formula = MyFormula & "E28"
Range("G" & RowCount).Formula = MyFormula & "G28"
Range("H" & RowCount).Formula = MyFormula & "H28"
Range("I" & RowCount).Formula = MyFormula & "I28"
Range("J" & RowCount).Formula = MyFormula & "J28"
Range("K" & RowCount).Formula = MyFormula & "K28"
Range("L" & RowCount).Formula = MyFormula & "L28"
Range("M" & RowCount).Formula = MyFormula & "M28"
Range("N" & RowCount).Formula = MyFormula & "N28"
Range("O" & RowCount).Formula = MyFormula & "O28"
Range("P" & RowCount).Formula = MyFormula & "P28"
Range("Q" & RowCount).Formula = MyFormula & "Q28"
Range("R" & RowCount).Formula = MyFormula & "R28"
Range("S" & RowCount).Formula = MyFormula & "S28"
Range("T" & RowCount).Formula = MyFormula & "T28"
Range("U" & RowCount).Formula = MyFormula & "U28"

RowCount = RowCount + 1
Next Team
End Sub


dac803 said:
Thanks Joel, I had already tried that, I have put the code below that the
macro recorded. I have to put this into 31 worksheets for the month changing
the date for each date and for 8 lines(teams). running a macro that would do
it for the whole workbook would be greater use of time.


ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C3"
Range("D8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C4"
Range("E8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C5"
Range("G8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C7"
Range("H8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C8"
Range("I8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C9"
Range("J8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C10"
Range("K8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C11"
Range("L8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C12"
Range("M8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C13"
Range("N8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C14"
Range("O8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C15"
Range("P8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C16"
Range("Q8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C17"
Range("R8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C18"
Range("S8").Select
ActiveCell.FormulaR1C1 = "="
Range("S8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C19"
Range("T8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C20"
Range("U8").Select
ActiveCell.FormulaR1C1 = _
"='[A - Dec 08 -Summary -West A.xls]01-Dec'!R28C21"
Range("U9").Select
End Sub
dacbam


:

Can you record a macro while you perform one link and post the code.

1) Start Recording from worksheet menu Tools - Macro -Record enw Macro
2) Perform you links
3) Stop Recording - tools - Macro Stop Recording
4) go to VBA wiondow and get Macro. Alt-F11 to get to VBA window. Find
macro in Module 1.

:

Joel,

I hope this is the information you asked for.

1.The names of the worksheets are 01-Dec thru to 31-Dec.
2.The source and destinations are a group drive with different access writes.
3. team names like West A for workbook names.

As with most work environments a workbook has totals of the whole area not
team names and different management levels require different formats.

--
dacbam


:

Linking into multiple workbooks slows opening a workbook. It is better to
just put the daily totals into the workbook. I need more information to help
write a macro

1) The names of the 31 worksheets
2) The source and destination locations of the data.
3) the worksheet name(s) in the monhthly total workbook

:

I have a workbook for each team with 31 worksheets in each workbook for each
day of the month. These workbooks are linked to a monthly workbook with a
row for each team. I am looking for a macro to run that will link daily
totals for each day into the monthly workbook. I hope this is clear.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top