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.
--
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

"dac803" wrote:

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.
--
dacbam

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


"Joel" wrote:

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

"dac803" wrote:

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.
--
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.

"dac803" wrote:

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


"Joel" wrote:

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

"dac803" wrote:

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.
--
dacbam

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" wrote:

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.

"dac803" wrote:

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


"Joel" wrote:

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

"dac803" wrote:

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.
--
dacbam

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" wrote:

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" wrote:

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.

"dac803" wrote:

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


"Joel" wrote:

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

"dac803" wrote:

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.
--
dacbam