Macro simplification

[Yarroll]

ê³êóHello,

Is there any way at all that the following macro could be simplified? use
less memory, etc. It works just fine for smaller files. But with larger ones
(ca. 30 MB) it just goes dead (the line where it happens is marked below).
Also comments interspersed. Any help is much appreciated, cos otherwise I'd
have to split truckloads of files into smaller ones.

Thanks. Yarroll

sub Whatever()

Dim howm As Long
Dim i As Long
howm = WorksheetFunction.CountA(Worksheets(5).Range("A:A"))

Sheets(5).Rows("1:1").Copy _
Sheets("Summary").Rows("1:1") 'this statement copies the header line from
one of data sheets

Sheets("Summary").Range("A2:W2").Replace What:="(", Replacement:="=(",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False
Sheets("Summary").Range("X2:Y2").Replace What:="(", Replacement:="=(",
LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False

' these 2 statements replace ( with =(, IOW make operative the formulas
contained there

Range("A2:J2").Copy _
Range("A3:J" & howm)
Range("A2:A" & howm).NumberFormat = "General"
Range("D2:E" & howm).NumberFormat = "General"
Range("H2:J" & howm).NumberFormat = "General"
Range("B2:C" & howm).NumberFormat = "0.00%"
Range("F2:G" & howm).NumberFormat = "0.00%"
Range("A2:J" & howm).Value = Range("A2:J" & howm).Value

ActiveWorkbook.Save
'I thought to release some memory here by saving the workbook to disk. Or
release RAM. No dice, this won't help :-((

Range("X2:Y2").Copy _
Range("X3:Y" & howm)
Range("X2:Y" & howm).Value = Range("X2:Y" & howm).Value

ActiveWorkbook.Save
'same as above

Application.Run "PERSONAL.XLS!ClearClipboard" 'that's an external macro to
release Memory
Sheets(5).Columns("K:W").Copy _
Sheets("Summary").Columns("K:W") 'this is where the macro goes dead. This
line just copies over fixed numbers from Sheet 5, no formulas involved -
just plain numbers which I need to copy over to make Summary sheet readable
Range("A1").Select

End Sub

 

[Bob Flanagan]

You might try setting calculation to manual and using a loop to do the copy

Application.calculation = xlManual

For I = 11 to 23
Sheets(5).Columns(I).Copy Sheets("Summary").Columns(I)
Next

Bob Flanagan
Macro Systems
http://www.add-ins.com
Productivity add-ins and downloadable books on VB macros for Excel