More Excel Answers
Question Library
Ask a question about Excel
Volunteer
Experts of the Month
Expert Login
Awards
About Us
Tell friends
Link to Us
Disclaimer
|
| |
|
|
| |
| | | |
About Tom Ogilvy
Expertise
Worked with the program for many years - provided assistance on MS Excel Newsgroups since 1997. Have received the Microsoft MVP award annually since 1999.
I don't answer questions on using Excel in a browser
Since I have no way to test this. Prefer not to answer charting questions. I consider myself to be particularly knowledgeable about using VBA internal to Excel but have no problems with formulas and pivot tables either.
Experience
Have Used Excel for 15 - 20 years. Answered in excess of 70,000 Excel related questions in MS Excel newsgroups. Unless obvious, please specify whether you want a worksheet function or macro/VBA solution.
Education/Credentials
BS General Engineering (concentration in Industrial Engineering)
MS Operations Research Systems Analysis
| | |
| |
You are here: Experts > Computing/Technology > Microsoft Software > Excel > Using marked cells to select and copy rows
Excel - Using marked cells to select and copy rows
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
Ask a Question
|
|
