Copy and Paste Worksheet (+ Macro) in other workbooks

[xluser2007]

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:

[vba]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[/vba]
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.

[xluser2007]

Ok, I've been debugging in the meantime, and have made a few slight amendments to my Parameters spreadsheet. The Target worksheet tab is now copied into the Parameters workbook.

please find below my attempt to copy the Parameters worksheet into the target workbooks (note the target workbooks exist as strings from A3 onwards in the "Target Workbooks" tab:

[vba]Sub update1()

Dim Parameterswbk As Workbook

Dim Targetworkbook As String

Dim ParamterSht As Worksheet

Dim TargetwbkSht As Worksheet

Set Parameterswbk = ThisWorkbook

Set ParamterSht = ThisWorkbook.Sheets("Parameters")

Set TargetwbkSht = ThisWorkbook.Sheets("Target Workbooks")

TargetwbkSht.Activate

targetrows = 3

Do While TargetwbkSht.Cells(targetrows, 1).Value = ""

Targetworkbook = Cells(targetrows, 3) 'the gets the workbook path as a String
' e.g. C:\Documents\ABCD.xls

Workbooks.Open Filename:=Targetworkbook, UpdateLinks:=0

Parameterswbk.Activate

ParamterSht.Select

ParamterSht.Copy Before:=Workbooks(Targetworkbook).Sheets(1)

targetrows = targetrows + 1

Loop

End Sub[/vba]
It is encountering a 'Run time error '9'' Subscript out of range in the following line:

[vba]ParamterSht.Copy Before:=Workbooks(Targetworkbook).Sheets(1)[/vba]
Could anyone please help me correct this as it should paste over "Parameters" Worksheet in the "Targetworkbook".

Any Guru help would be much apprecaited.

[xluser2007]

Alos, I forgot to ask the Targetworkbooks are very large so once the Parameters worksheet is copied in as the first worksheet, the Target workbook should be saved and closed.

How do I add this in within the Do-While-Loop?