Y
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
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