PDA

View Full Version : Reduce macro code size



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

Aussiebear
01-04-2011, 04:34 PM
Is there only one sheet in each workbook that you are copying to?

Bob Phillips
01-04-2011, 05:06 PM
Maybe this



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

nicko
01-04-2011, 05:57 PM
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
01-04-2011, 06:31 PM
Hi xld,

your version started well then stopped at

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

any ideas???

Many thanks,

Nicko

nicko
01-04-2011, 06:39 PM
the error message is

"Run-time error '438':

Object doesnt support this property or method"

Bob Phillips
01-05-2011, 02:09 AM
Try this instead




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