new to macro. seeking help on select range with defined minimum.

**aballoonflies** · 01-02-2013, 10:19 PM

HI All, I am new to macro and vba so please bear me with.

i am trying to setup macro to select and copy over data from one sheet to another. for example:

Column A is name, Column B would be $ amount. Data is sorted from largest $ to smallest $. I will need to have macro copy over all data where B $ is >$500.

I did a macro record as follow:

Range("A1:AC491").Select
Range("AC491").Activate

Another question is if I want to copy over all data from one sheet to the other. How do I have macro always select the last row of data?

Both questions are based on rows in spreadsheet changes every time I refresh data. So I have macro in personal macro and run it against every new sheet where the >$500 and last row would be on different row...

Hope i have made myself clear...

Thanks for your help!

**smuzoen** · 01-03-2013, 12:26 AM

You could use autofilter with criteria > 500 - see attached workbook

Please Login or Register  to view this content.

**aballoonflies** · 01-03-2013, 01:22 AM

Thanks!!
Hate to ask..but what about copying over all data above last row of a dataset?

**smuzoen** · 01-03-2013, 01:45 AM

You will need to upload a sample workbook with no sensitive data so I can see what you are trying to do - explain what needs to be copied and where it needs to go otherwise I am just making assumptions

**aballoonflies** · 01-03-2013, 05:08 AM

Sample.xlsx
Sample Maually Done what Marco should do.xlsx

HI...I have uploaded two files. Maybe it's got a bit more than what I have asked in original post. But I will give a shot to see if anyone can help me out

"Sample" lists out all the steps.
The other "Sample Manually Done" is what the workbook should look like after all macro are performed (You may already know I am trying to let Macro take my manual work)

I have been playing with smuzoen's code but I really dont have the VBA basics yet to extend further. I do plan to read more but in the meantime I hope to fix this up quickly...

Thanks for all of your support! This forum is truly helpful! Been browsing and searching as well

**aballoonflies** · 01-03-2013, 05:10 AM

Also for all the user input (Start Date, End Date, Type, Threshold), ideally I would like to put it in a separate tab...

**smuzoen** · 01-03-2013, 07:17 AM

See attached workbook - you may have to modify it to your exact specifications however it should get you well on the way - the criteria is entered on first page.

**aballoonflies** · 01-03-2013, 10:30 PM

I cant say how much thank you I owe you

I am learning from your code and making some changes. And encounter yet some more questions:

on Ln72 where create Pivot Table, if I already have the pivot then I can replace these with a code that tells macro to refresh pivot base on ws2 right? Reason being the data will be much bigger, recreate pivottable every time data refreshes will probably take longer.

and for Ln91, copy pivot table. Actually what I need to do is copy over the pivot which the subtotal of a person is larger than the threshold. So let's say we set threshold to be 2600, then only "John" will be copied over to Summary. Is there a way to have Macro look at subtotal of each Name?

Thank you so much smuzoen!! really appreciate it!

**smuzoen** · 01-05-2013, 05:38 AM

You will need to recreate the Pivot Table in the workbook - All the code will do is refresh the data rather than recreate it. I have changed the code so only the Subtotal is checked and if greater than the criteria then copied to summary. Hope this is what you need.

**aballoonflies** · 01-07-2013, 04:47 AM

Is there a way to copy over not just subtotal over to summary?

Say in pivot i have below. Set threshold to be 2000
John a 500
b 1000
c 1000
John Total 2500
Ken a 500
b 800
Ken Total 1300
Jen a 1500
b 1500
Jen total 3000

Then in summary, i can have:
John a 500
b 1000
c 1000
John Total 2500
Jen a 1500
b 1500
Jen Total 3000

**aballoonflies** · 01-10-2013, 03:16 AM

still trying to figure out...

**smuzoen** · 01-10-2013, 09:25 PM

See attached workbook - if you had a try always post your code as it is the only way to learn.

new to macro. seeking help on select range with defined minimum.

LinkBack

Thread Tools

Rate This Thread

Display

new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Re: new to macro. seeking help on select range with defined minimum.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions