Cut and Paste Rows based on Value in Cell Loop

[mburke05]

Hello,

I've searched a few times for this specific solution, and I haven't come across it so I was hoping for some assistance.

I have a worksheet with data (call it "data sheet") for properties spanning the year. I created a code in column A that identifies the property, eg. dc0025, nc0131, etc. There are around 100 properties, each with 150 rows.

There is a worksheet that contains information on these properties named after the property code (dc0025, nc0131, etc.) I need to cut the data from the row the "data sheet" and paste it on it's corresponding property sheet.

For example,
column a column b
dc0025 2$
nc0131 4$

I need row 1 to be cut and paste to the sheet "dc0025", and the same with row 2.

I need this macro to loop through the entire range of data (say cutoff is row 13,000).

I am entirely lost in all approaches I've tried thus far. Any help is greatly appreciated.

Thanks

[Moo the Dog]

mburke,

See my attached sheet and run the macro 'CopyCodes' (code is below). Let me know if it works for what you need. Basically, it will look down column A of the "Data Sheet", and copy each row to an existing spreadsheet with the value in A, or if a spreadsheet for that value doesn't exist, it will create one and copy the data there.

It will leave the "Data Sheet" untouched, but all of the data should be copied to the sheet where it belongs.

Please Login or Register  to view this content.

- Moo

[mburke05]

Hi Moo,

This script works wonderfully! Thanks very much!

I had a question/modification request, and I've tried but I managed to bungle it up.

I need for the range to be copy and pasted over a specific range on the worksheet it's being pasted to. How would I modify this?

Additionally, the macro gets an error (Excel being overworked basically) when I try to run it for all of my data. It runs about 10,000 rows, and after its all run I get an error on Excel requiring more memory (which is impossible as it's the only program running on a very powerful computer). Any thoughts on how I could parcel this out? Maybe two macros, one for rows 1-10,000 and one for 10,000-20,000 ?

Thanks so much again!

[mburke05]

Also, it appears that it's pasting the first row onto every single sheet for some reason. How might I fix that? Is that from the Rows(1) part?

The first 5 rows of the stuff I'm pasting is totally useless, how can I embed a function into that which would delete those rows?

[benishiryo]

hi there, a few questions i hope you can clarify for us to help you better:

I need for the range to be copy and pasted over a specific range on the worksheet it's being pasted to

what's the specific range?

the macro gets an error (Excel being overworked basically) when I try to run it for all of my data. It runs about 10,000 rows

10K of rows is very little actually. but since you mentioned you have 100 different properties, i suppose there are 100 sheets? have not tried that before, so i cant tell. but do you have formulas/highlighting/conditional formatting that reference to the entire row or column? for eg.
=SUM(A:A)
that will take a lot of memory

Also, it appears that it's pasting the first row onto every single sheet for some reason. How might I fix that? Is that from the Rows(1) part?

that is actually for the header for every sheet. again, a sample file from you would help us understand what you need

ideally, your sample file should show us your desired result too.

ps: not expecting the 10,000 rows to be uploaded

[mburke05]

thanks benishiryo!

the attached has the "Data Sheet" tab, and the Sample Property tab.

if you run Moo's script (Module 1) it works wonderfully, as you will see, it will create ~100 sheets for each of the properties (i've shortened the data to only be 70ish properties due to upload size limits, but it works fine with 100 now).

there are some small flaws. it pastes the same row (row 2) on every property that it pastes the data to.

in addition, if you run his script after having run it already (so say i change the data sheet and need to update the data on each sheet, which i will need to every month), it will create thousands of columns on each of the sheets for some reason, increasing the size of the file (and making the scrollbar tiny).

my modification that would, i think, solve the problem, would be rather than copying the entire row that has the rcell value. instead copy the 15 adjacent columns, eg. range(Ax:Ox).

the paste range would be A3:Ox on each sheet, as long as it needs to run becasue each property will have a different row length (though all will be around 150, and all <200).

i hope that helps, so sorry if i'm unclear.

[JosephP]

perhaps

Please Login or Register  to view this content.

[JBeaucaire]

@Moo - the problem the OP is experiencing running your macro on a large data is that the SpecialCells method has a limit of 8600 (ish) separate "areas" within the range defined. That means that 8600 "groups of cells". If your data has more distinct areas than that, it just fails.

It's very possible to have a dataset of 100,000 rows but only 500 "areas" within the rows based on the SpecialCells application. On the other hand, it's easy to hit the 8600 areas if your "areas" are small and numerous throughout the data.

Here's Moo's macro back with a loop that only evaluates 8000 rows at a time....and skips the first 5 rows of your original data sheet:

Please Login or Register  to view this content.

[Moo the Dog]

Thank you, benishiryo & Jerry for the advice. And Joseph - bravo... what else is there to say?

- Moo