VBA Code for copying/pasting from Master-to-target files

[Pedroluna]

I have a master excel file (VP Partial P&L's -Feb'09.xls ) with about 95 different sheetss one per department. Each of these sheets have to be inserted in its own file (i.e. Marketing VP.xls, IT VP.xls etc…). So I need a macro that can take a look into this file (VP Partial P&L's -Feb'09.xls) and take each sheet per its name and insert it in the appropriate VP file. Also, if I can gvie the new sheet a different name (i.e. Feb 2009), it’d be great.

I am copying and pasting each sheet which is time consuming. I was thinking about a macro/program that can do the copying and pasting, but doing the above is a better fix.
Thank you
Pedro

[Pedroluna]

I started a macro that will do what i require. This macros open the master file one of the destination file copies the appropriate page from the master file and paste it in the destination file. THe problem is that i needed to copy/paste in several other files. I tried to copy the coding for copy and paste. it sort of work, but i getting an error message. Fiirst it says that the "workbook contains one or more links that cannot be updated." after i press continues it says it has to stop.
Here is the code:
Sub VP_File_Macro_II()
'
' VP_File_Macro_II Macro
' An improvement on first one
'

'
ChDir "W:\2009 Budgets by Department\Monthly & YTD Reports\03 Mar '09"
Workbooks.Open Filename:= _
"W:\2009 Budgets by Department\Monthly & YTD Reports\03 Mar '09\VP Partial P&L's -Mar '09.xls" _
, UpdateLinks:=0
ChDir "W:\2009 Budgets by Department\Sent to VPs\Non Revenue"
Workbooks.Open Filename:= _
"W:\2009 Budgets by Department\Sent to VPs\Non Revenue\Accounting Finance 2009.xlsx"
Sheets("Feb 09").Select
Sheets("Mar 09").Visible = True
Windows("VP Partial P&L's -Mar '09.xls").Activate
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Acctg-Finance").Select
Application.Goto Reference:="R1C1"
Range("A1:H57").Select
Selection.Copy
Windows("Accounting Finance 2009.xlsx").Activate
Application.Goto Reference:="R1C1"
ActiveSheet.Paste
Columns("E:E").Select
Selection.ColumnWidth = 30
Columns("A:A").Select
Selection.ColumnWidth = 2
Application.Goto Reference:="R58C8"
Application.CutCopyMode = False
Selection.Style = "STYLE1"
With Selection
.HorizontalAlignment = xlFill
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.NumberFormat = "#,###,##0;(#,###,##0)"
ActiveCell.FormulaR1C1 = "Done"
Range("H59").Select
End Sub

'new one
Sub VP_File_Macro_IIa()
'
' VP_File_Macro_II Macro
' An improvement on first one
'
'
'
ChDir "W:\2009 Budgets by Department\Monthly & YTD Reports\03 Mar '09"
Workbooks.Open Filename:= _
"W:\2009 Budgets by Department\Monthly & YTD Reports\03 Mar '09\VP Partial P&L's -Mar '09.xls" _
, UpdateLinks:=0
'Upto here it opened the first file = Bill's file
ChDir "W:\2009 Budgets by Department\Sent to VPs\Non Revenue"
Workbooks.Open Filename:= _
"W:\2009 Budgets by Department\Sent to VPs\Non Revenue\Accounting Finance 2009.xlsx"
Sheets("Feb 09").Select
Sheets("Mar 09").Visible = True
Windows("VP Partial P&L's -Mar '09.xls").Activate
'Upto here both files are open and the current month sheet is unhide
'
'
Sheets("Acctg-Finance").Select
Application.Goto Reference:="R1C1"
Range("A1:H57").Select
Selection.Copy
'Now the info on the Bill's files is copied.
Windows("Accounting Finance 2009.xlsx").Activate
Sheets("Mar 09").Select
Application.Goto Reference:="R1C1"
ActiveSheet.Paste
'Now the information is pasted on the VP file
Columns("E:E").Select
Selection.ColumnWidth = 30
Columns("A:A").Select
Selection.ColumnWidth = 2
'Now the colunms on the VP files are resized
Application.Goto Reference:="R58C8"
Application.CutCopyMode = False
Selection.Style = "STYLE1"
With Selection
.HorizontalAlignment = xlFill
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
'Selection.NumberFormat = "#,###,##0;(#,###,##0)"
ActiveCell.FormulaR1C1 = "Done Copying"
Range("H59").Select
'
'new file to open below
'
ChDir "W:\2009 Budgets by Department\Sent to VPs\Non Revenue"
Workbooks.Open Filename:= _
"W:\2009 Budgets by Department\Sent to VPs\Non Revenue\HR 2009 - Steve K.xlsx"
Sheets("Feb 09").Select
Sheets("Mar 09").Visible = True
Windows("VP Partial P&L's -Mar '09.xls").Activate
'Now the new file is open
>>> Sheets("Feb 09").Select<<< HERE IT STOPS RUNNING
Sheets("Mar 09").Visible = True
Windows("VP Partial P&L's -Mar '09.xls").Activate
'Upto here three files are open and the current month sheet is unhide
'
Sheets("HR").Select
Application.Goto Reference:="R1C1"
Range("A1:H57").Select
Selection.Copy
'Now the info on the Bill's files is copied.
Windows("Accounting Finance 2009.xlsx").Activate
Sheets("Mar 09").Select
Application.Goto Reference:="R1C1"
ActiveSheet.Paste
'Now the information is pasted on the VP file
Columns("E:E").Select
Selection.ColumnWidth = 30
Columns("A:A").Select
Selection.ColumnWidth = 2
'End With
'Selection.NumberFormat = "#,###,##0;(#,###,##0)"
ActiveCell.FormulaR1C1 = "Done Copying"
Range("H59").Select

End Sub