Solved: Copy Error creating 35mb file...:(

[Phelony]

Hi guys

I've got the following macro which in quick and simple terms moves data from an input area, sorts it and then moves it again so it can be picked up by formulae to populate what was previously a handwritten form.

Sheets("Data Sheet").Select
ActiveSheet.range("$A$1:$S$209").AutoFilter Field:=14, Criteria1:="=TRUE", _
Operator:=xlOr, Criteria2:="="
ActiveSheet.range("$A$1:$S$209").AutoFilter Field:=13, Criteria1:="=*?*", _
Operator:=xlAnd, Criteria2:="<>0"
Columns("M:M").Select
Selection.Copy
Sheets("Input Sheet - Parties").Select
Columns("P:P").PasteSpecial xlPasteValues
Sheets("Data Sheet").Select
Columns("R:R").Select
Selection.Copy
Sheets("Input Sheet - Parties").Select
Columns("O:O").PasteSpecial xlPasteValues
range("P2").Select
Sheets("Data Sheet").Select
ActiveSheet.ShowAllData

Sheets("Input Sheet - Parties").Select
Dim i As Long
For i = 1 To range("R2").Value
With range("A1")

.Resize(36, 14).Copy .Offset(i * 36 + 0, 0)
End With
Next i

The problem is, where I've got code such as:

Columns("R:R").Select
Selection.Copy

it really is copying the entire column. This results in the destination column being 1 million lines long. As this process occurs several times the net result is that the file swells to 35mb in size. :bug:

Could anyone suggest what needs to go into the code so that it will only select populated or used cells?

Main reason for not giving it a set copy area is that we have no idea how much data is going to be entered and therefore be required to be moved at any given time, so it has to remain dynamic. I've tried several methods of identifying the last used cell, but these have all met with dismal failure :(

Any help at all will be apprecaited.

Phel x

[p45cal]

Try
intersect(activesheet.usedrange,columns("R")).Selector
Union(Columns("R").SpecialCells(xlCellTypeConstants, 23), Columns("R").SpecialCells(xlCellTypeFormulas, 23)).Select(this last one misses out any blank cells in the column which may or may not be what you want.

[Phelony]

That seems to have fixed it!

Thanks P45Cal, you're a star as ever! :thumb

Phel x