xluser2007
01-21-2008, 02:07 AM
Hi All,
I have a workbook called Parameter_Sheet_December07.xls.
It consists of only one Worksheet named "Parameters".
It has a few key parameters already placed in certain cells and single Commandbutton that has the following macro to update all Pivot tables in the workbook:
Sub RefreshAllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
Application.Calculate
End Sub
This is stored in Module 1 of Parameter_Sheet_December07.xls.
I also have a workbook called Targetworkbooks.xls. It consists of only one worksheet Titled "Target Workbooks". In Cells A1:A10 of "Target Workbooks" are strings containing key workbooks paths as strings e.g. A1 = C:\Documents\WXYZ.xls, A2 = C:\Documents\ABCD.xls ...
I want to essentially copy the "Parameters" worksheet from Parameter_Sheet_December07.xls and paste it as the FIRST worksheet in all the Target Workbooks located in Targetworkbooks.xls (Cells A1:A10). How do I do this?
Also once the worksheet has been relevantly pasted I would like the above PivotTable macro to be copied through to Module 1 in the pasted workbooks and the Command button to link to the macro in the workbook that it exists in. IS this possible, I was having trouble manually doing this as the commandbutton once pasted kept referring to the macro in Parameter_Sheet_December07.xls Module 1, not in the actual workbook that it existed in i.e. the macro didn;t carry over to C:\Documents\WXYZ.xls when i manually copied the "Parameters" worksheet over.
Any help on this would be really appreciated.
I have a workbook called Parameter_Sheet_December07.xls.
It consists of only one Worksheet named "Parameters".
It has a few key parameters already placed in certain cells and single Commandbutton that has the following macro to update all Pivot tables in the workbook:
Sub RefreshAllWorkbookPivots()
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
Application.Calculate
End Sub
This is stored in Module 1 of Parameter_Sheet_December07.xls.
I also have a workbook called Targetworkbooks.xls. It consists of only one worksheet Titled "Target Workbooks". In Cells A1:A10 of "Target Workbooks" are strings containing key workbooks paths as strings e.g. A1 = C:\Documents\WXYZ.xls, A2 = C:\Documents\ABCD.xls ...
I want to essentially copy the "Parameters" worksheet from Parameter_Sheet_December07.xls and paste it as the FIRST worksheet in all the Target Workbooks located in Targetworkbooks.xls (Cells A1:A10). How do I do this?
Also once the worksheet has been relevantly pasted I would like the above PivotTable macro to be copied through to Module 1 in the pasted workbooks and the Command button to link to the macro in the workbook that it exists in. IS this possible, I was having trouble manually doing this as the commandbutton once pasted kept referring to the macro in Parameter_Sheet_December07.xls Module 1, not in the actual workbook that it existed in i.e. the macro didn;t carry over to C:\Documents\WXYZ.xls when i manually copied the "Parameters" worksheet over.
Any help on this would be really appreciated.