Expert: Tom Ogilvy - 11/9/2009

Question
I have a spreadsheet with many rows of data.  I have created a column A that a user puts a mark in (I have it as the letter X now) on rows that they would like to copy.  I am trying to create a macro that, when executed, will take all of the marked rows (with the X values in column A), copy them, then paste them in a new sheet that contains the same heading rows (rows 1-5).  Any help on how to execute this, or know of an easier method of completing this same action?

Answer
Dan,

If I wanted to copy rows that have a text value in column A I would do


Sub ABC()
Dim r as Range, r1 as Range
' get the next available row in sheet2 using column A
set r1 = worksheets("Sheet2").Cells(rows.count,"A").end(xlup).row + 1
' pick up the rows in sheet1 that have text in column A
set r = worksheets("Sheet1").columns(1).specialcells(xlconstants,xlTextValue)
' do the copy
r.copy r1
End sub

this works if the layout in each sheet is identical.  If it isn't (and that isn't totally clear from your description), then you would have to loop through the identified data and copy to the appropriate column - doing a row at a time and within each row, by column.

The exception is if you use the advanced filter.  the advanced filter can do this as a manual command (no macro required), but the headers would need to be the same in terms of the content of the header name (no requirement to be in the same location).  However, the advanced filter only looks at a one row header (you mentioned something about the headers being 5 rows).  The five row header would be no problem if the last row of the header can be used as a unique identifier - then the first 4 rows are ignored for purposes of the advance filter.  This is the easiest in my opinion if your data fits the "model"

--
Regards,
Tom Ogilvy