Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one 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. -- dacbam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one workbook
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one workbook
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one workbook
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one workbook
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one workbook
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one workbook
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" wrote: 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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macros for linking multiple workbooks to one workbook
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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking multiple workbooks | Links and Linking in Excel | |||
Linking multiple workbooks | Excel Discussion (Misc queries) | |||
Linking multiple workbooks | Links and Linking in Excel | |||
Linking Multiple Workbooks | Excel Programming | |||
linking of macros with other workbooks. need to stop it! | Excel Discussion (Misc queries) |