macro out ofmemory

[CookieMonster76]

Hi

I have the following which repeats itself over and over, until the point whereby excel cannot run it.

The only difference in each section is the reference to the cells highlighted in red.

Is there a way to consolidate it into a few lines?

Thanks

Paul

With Sheets("Gross Pay")
.Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Costing Report").Range("K1")
.Range("E1:E" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Costing Report").Range("N1")
End With
Range("M4:M" & Range("K" & Rows.Count).End(xlUp).Row).Value2 = Range("N3").Value2
Range("K4:N" & Range("K" & Rows.Count).End(xlUp).Row).Copy Destination:=Range("A1048576").End(xlUp).Offset(1, 0)
Range("K:N").Delete

With Sheets("Gross Pay")
.Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Costing Report").Range("K1")
.Range("F1:F" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Costing Report").Range("N1")
End With
Range("M4:M" & Range("K" & Rows.Count).End(xlUp).Row).Value2 = Range("N3").Value2
Range("K4:N" & Range("K" & Rows.Count).End(xlUp).Row).Copy Destination:=Range("A1048576").End(xlUp).Offset(1, 0)
Range("K:N").Delete

 

[Norie]

Paul

How are you running the code?

Is it meant to repeat over and over?

 

[CookieMonster76]

Hi

Yes, it is converting a table with (say) 50 columns into a column.

From the table cols A to C are fixed, and repeat in the column. I then want to to col E from the table, then coll F etc.

In essence:


Jones 100 400
Smith 200 500
Brown 300 600

Needs to become


Jones 100
Smith 200
Brown 300
Jones 400
Smith 500
Brown 600

 

[hiker95]

macro out ofmemory
Hi

I have the following which repeats itself over and over, until the point whereby excel cannot run it.

CookieMonster76,

In a copy of your workbook, try adding the below bold lines of code, after each copy command.

With Sheets("Gross Pay")

  .Range("A1:B" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Costing Report").Range("K1")

  Application.CutCopyMode = False

  .Range("E1:E" & .Range("A" & .Rows.Count).End(xlUp).Row).Copy Destination:=Sheets("Costing Report").Range("N1")

  Application.CutCopyMode = False

End With