Reduce macro code size

[nicko]

Hi there,

I have recorded a macro to copy data from one workbook and paste into sheets into other workbooks then save and close. ..

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

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

[Aussiebear]

Is there only one sheet in each workbook that you are copying to?

[Bob Phillips]

Maybe this

[vba]

Sub test2()
'
' test2 Macro to update forecast files PEL/FSA/FSSI/FSW
'
Const path As String = _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\"
Dim vecWBs(1 To 4) As String
Dim wb As Workbook
Dim i As Long

vecWBs(1) = "PEL F11AOP MASTER FILE.xls"
vecWBs(2) = "FSA F11AOP MASTER FILE.xls"
vecWBs(3) = "FSSI F11AOP MASTER FILE.xls"
vecWBs(4) = "FSW F11AOP MASTER FILE.xls"

Selection.Copy

For i = 1 To 4

Set wb = Workbooks.Open(path & vecWBs(i))
With wb

.Worksheets(1).Range("B1").Paste
.Save
.Close
End With
Next i

Set wb = Nothing

Application.CutCopyMode = False
End Sub
[/vba]

[nicko]

Is there only one sheet in each workbook that you are copying to?

Yes, its just the one sheet I am coping and pasting from one file to another.

Many thanks,

Nick

[nicko]

Hi xld,

your version started well then stopped at

.Worksheets(1).Range("B1").Paste

any ideas???

Many thanks,

Nicko

[nicko]

the error message is

"Run-time error '438':

Object doesnt support this property or method"

[Bob Phillips]

Try this instead

[vba]


Sub test2()
'
' test2 Macro to update forecast files PEL/FSA/FSSI/FSW
'
Const path As String = _
"Y:\Sales\National Accounts - Traditional and Grocery\Grocery\FINANCIAL\Forecasts\F11\1.0 Master F10AOP Full Year Forecast\"
Dim vecWBs(1 To 4) As String
Dim wb As Workbook
Dim rng As Range
Dim i As Long

vecWBs(1) = "PEL F11AOP MASTER FILE.xls"
vecWBs(2) = "FSA F11AOP MASTER FILE.xls"
vecWBs(3) = "FSSI F11AOP MASTER FILE.xls"
vecWBs(4) = "FSW F11AOP MASTER FILE.xls"

Set rng = Selection

For i = 1 To 4

Set wb = Workbooks.Open(path & vecWBs(i))
With wb

rng.Copy .Worksheets(1).Range("B1")
.Save
.Close
End With
Next i

Set wb = Nothing

Application.CutCopyMode = False
End Sub
[/vba]