nicko
01-04-2011, 03:59 PM
Hi there,
I have recorded a macro to copy data from one workbook and paste into sheets into other workbooks then save and close. ..
Sub test2()
'
' test2 Macro to update forecast files PEL/FSA/FSSI/FSW
'
'
Selection.Copy
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\PEL F11AOP MASTER FILE.xls"
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\FSA F11AOP MASTER FILE.xls"
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\FSSI F11AOP MASTER FILE.xls"
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\FSW F11AOP MASTER FILE.xls"
Windows("National_Accounts_Forecast_F10311210.xls").Activate
Windows("FSW F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Selection.Copy
Windows("FSA F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Selection.Copy
Windows("FSSI F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Selection.Copy
Windows("PEL F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
It works ok, but it is really long for something which is pretty simple. is there anyway I can condense the macro...
any ideas welcome.
many thanks,
Nicko
I have recorded a macro to copy data from one workbook and paste into sheets into other workbooks then save and close. ..
Sub test2()
'
' test2 Macro to update forecast files PEL/FSA/FSSI/FSW
'
'
Selection.Copy
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\PEL F11AOP MASTER FILE.xls"
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\FSA F11AOP MASTER FILE.xls"
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\FSSI F11AOP MASTER FILE.xls"
Workbooks.Open Filename:= _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\FSW F11AOP MASTER FILE.xls"
Windows("National_Accounts_Forecast_F10311210.xls").Activate
Windows("FSW F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Selection.Copy
Windows("FSA F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Selection.Copy
Windows("FSSI F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
Selection.Copy
Windows("PEL F11AOP MASTER FILE.xls").Activate
Range("B1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWindow.Close
End Sub
It works ok, but it is really long for something which is pretty simple. is there anyway I can condense the macro...
any ideas welcome.
many thanks,
Nicko