Alternative to copy and paste values based on criteria

Hi,

So i have a sheet with various bits of data on it and need to populate new tabs according to a specific field. Although this is not my file, in the attached as an example i would look to populate new tabs f-or each unique state. So a tab for LA, MI, NJ etc. Now at the momement i create a pivot and use a for loop to create a new sheet per state. I would then use range of new sheet = us-500's range and then delete all rows where the data doesn't match the current sheet name. The issue is that this is a lengthy task if there are 1000s of rows to delete. Does anyone have an alternative?

Hi,

I'm looking to completely automate a process using cost codes rather than state. The issue is that there may be new cost codes added to the data so filtering will only work for the initially used cost codes.

Originally Posted by jaslake

Hi krishns

Create a Dynamic Named Range of Unique Cost Codes...call it CostCodes. This can be done with Code. As new Cost Codes are added they'll be added to the Dynamic Named Range.

See this Link...

http://www.ozgrid.com/Excel/DynamicRanges.htm

Hi,

Thanks for your reply.

My preference where possible is not to copy and paste as with large data sets excel seems to struggle. I can potentially be using over 30000 lines and in the past with a previous macro i created excel would struggle/crash when attempting to copy and paste

Hi,

I feel i'm almost there with a solution, please see macro "Issues". Rather than making all values in the selection = to range it only matches the table headers

Hi,
I looked at your macro "Issues", but could not understand what information you where trying to convey.

I think you may not fully understand the implication of using Range.SpecialCells(xlCellTypeVisible). When used, the Range may (probably will) contain multiple areas that you need to iterate through. Here is example based on your spreadsheet. I probably have not set it up completely the way you would like it, but it should give you an idea of the steps needed.

Please Login or Register  to view this content.

Originally Posted by TnTinMN

Hi,
I looked at your macro "Issues", but could not understand what information you where trying to convey.

I think you may not fully understand the implication of using Range.SpecialCells(xlCellTypeVisible). When used, the Range may (probably will) contain multiple areas that you need to iterate through. Here is example based on your spreadsheet. I probably have not set it up completely the way you would like it, but it should give you an idea of the steps needed.

Please Login or Register  to view this content.

Hi TnTinMN,

Thans, this works really well. Please can you modify the code to also incorporate table headers?

Originally Posted by krishns

Hi TnTinMN,

Thans, this works really well. Please can you modify the code to also incorporate table headers?

I could, but what is preventing you from doing it yourself?

I feel that the code has sufficient comments on the exclusion of the header that you should be able to figure out what needs to be modified.

If your do not understand what a particular command does, do you look it up in Excel's VBA Help. You can place the cursor on the commands you do not understand and press the "F1" key to bring up the built-in help.

Give it try yourself.