extract a range of data from one sheet to another and rearrange

**aygw** · 01-23-2010, 06:03 PM

Hi folks, I need some helps on a task that I have been seeking solution for a long time:
I am seeking helps for automating a task to extract a range of data from one sheet to another . please see example below:
1. user highlights and selects a range of data from an existing spreadsheet, e.g.
......(Col A) (Col B) (Col C) (Col D) (Col E)
row1 Store1 First1 second1 third1 forth1
row2 Store2 First2 second2 third2 forth2
row3 Store3 First3 second3 third3 forth3
row4 Store4 First4 second4 third4 forth4
row5 Store5 First5 second5 third5 forth5

user selects Col A,C and D on row2, row4 and row 5 only
2. an automation to put this selected range of data to another excel file and rearrange the columns, e.g, original (col C and D ) will be in (col A and B) and original (col A) will be (col C). if the file is not already exist, creates it, based on the date .e.g. jan15newdata.xls, if the new data file is already exist then append the data to the existing ones

I hope my description is clear. Any helps would be appreciated.

**Richard Buttrey** · 01-23-2010, 07:09 PM

Hi,

Is there something unique to rows 2,4 & 5. If so why not just use Data Filter Advanced Filter to extract the columns to another file, in whichever order you want.

HTH

**aygw** · 01-23-2010, 10:53 PM

Originally Posted by Richard Buttrey

Hi,

Is there something unique to rows 2,4 & 5. If so why not just use Data Filter Advanced Filter to extract the columns to another file, in whichever order you want.

HTH

Hi,
thanks for the suggestion. there is no uniqueness.
Can you shed some lights on how to get to expected result as described by using Advanced Filter automatically?
cheer!

**Richard Buttrey** · 01-24-2010, 07:04 AM

Hi,

What determines the decision to pick rows 2,4 & 5. Tell me that and I can probably tell you how to set a Data Filter to do it.

Regards

**aygw** · 01-24-2010, 12:28 PM

Originally Posted by Richard Buttrey

Hi,

What determines the decision to pick rows 2,4 & 5. Tell me that and I can probably tell you how to set a Data Filter to do it.

Regards

For example when column B has a blank cell

thanks again

**Richard Buttrey** · 01-25-2010, 06:28 AM

Hi,

Thanks for that. That's why I asked you what was unique about those rows. So in that case if you are wanting to extract data which has blank cells in column B, then that is precisely what the Data Filter functionality is there for.

Try the simple data filter first. i.e. filter the data 'in place' by selecting the criteria blank in the column B drop down. This will leave you with the view of rows 2,4 & 5 only,(although of course the other rows are still there but hidden). If you want to extract those rows to another worksheet, or indeed another workbook then you need to create an output range which contains those column labels that you want to extract, and a criteria range, which in this case would be the column B lable. Now you can use the Advanced Data Filter.

If you're still struggling to get this to work, upload an example of your workbook with a note as to which data you are trying to extract.

Regards

**DonkeyOte** · 01-25-2010, 06:46 AM

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!