Copying Specfic data from a main worksheet to several breakdown sheets.

**itsnick** · 10-21-2009, 12:01 PM

Hello all

I'm looking for a solution for my worksheet
I've got a main tab in which contains deposit data, I need to take the raw data format it and break it down into 7 different tabs. Each week a report is exported and then one for the actual month containing all the data. I’m currently manually recording some formatting macros because I haven’t learned to write my own yet. The number of columns in this report is always constant but the rows vary depending on the number of deposits. I have 7 tabs where the data from the main sheet will be broken down in. In my spread sheet I have the following headings.
(A1)Date, (A2)Batch, (A3)Source (A4)Control (A5)Total Batch (A6)Total Variance

The information I would use to copy the rows to other sheets is going to be based on (A2).
Anything that starts with U65 would go into the U-65 tab.
1,7 or 8 would go to the lockbox tab.
2 or 5 = IN-HOUSE
3 = WIRES
4 = DATA OCEAN
Eh, WH, or WE = ACH
Finally the variance tab will contain any rows which have a number other than 0 Positive or Negative.

My biggest problem is that I never know how many rows the data will have so when I recorded my macro I Started dragging my formats to about row 200. It could one day surpass this number so If I had a choice id set it to row 1,000. I really need help putting this together, its currently done manually and having a macro

Please see the attached info for an example. The numbers will not match in the tabs on the example they all have dummy numbers. Also here are the macros I recorded so far. (They suck)

Please Login or Register  to view this content.

Few more things I remembered, on the raw data sheet you will notice the last entry (Batch) matches the one above it. In reality it is not another entry but the total for the raw data it just exports this way.

Thank you for any help you can provide.

**royUK** · 10-21-2009, 12:50 PM

If you date column was complete , ie each field contained a date, a helper column you could do this with a PivotTable.

Your posted code will be extremely inefficient with all that selecting of sheets.

**modytrane** · 10-21-2009, 01:14 PM

Roy's suggestion is likely to be more efficient.
However, since your raw data is a download, you may not have much control over the date column.
I also know that a VBA code [written by one of the Gurus here], could be quite efficient and elegant.
I am not one of those Gurus [not yet anyway], so here's my humble attempt.

see the attached example. It uses array formulas to list items from the main sheet. I've writtem formulas for four (4) sheets. You should be able to write the others. The only thing missing is the last row with Totals. That can be added with a small macro.

Hope this helps.
modytrane

**itsnick** · 10-21-2009, 03:14 PM

Roy,

I figured the code would be ineffecient, but because I cant write the code the best I can do is record it. Im really a big time excel newbie, Ive looked at the pivot table possiblity but theres to much room for user error.

Mody,

The array formulas might work for me, I just need to protect the cells so they cant edit the forumlas. Im going through it now and trying to figure it out.

**JBeaucaire** · 10-21-2009, 04:10 PM

If your dummy data always comes in the way shown, then the "autofilter" method can be used to copy the data enmasse to the separate worksheets. Your sample data had column F data needing to be moved to column E and a formula put into column F to create the true variance, right?

Attached is your dummy raw data sheet with the macro in it. Just press the button and it will create the needed sheets and add the totals as shown in your original sample.

**itsnick** · 10-22-2009, 08:28 AM

Jbeaucaire,

This works perfectly, thank you. Yes the information in column F
was moved over to create the true variance. Thank you again.

**itsmark** · 07-16-2010, 10:14 AM

Originally Posted by JBeaucaire

If your dummy data always comes in the way shown, then the "autofilter" method can be used to copy the data enmasse to the separate worksheets. Your sample data had column F data needing to be moved to column E and a formula put into column F to create the true variance, right?

Attached is your dummy raw data sheet with the macro in it. Just press the button and it will create the needed sheets and add the totals as shown in your original sample.

Jbeaucaire,
I have a similar problem with slight changes which i am not able to workout as a novice. Please guide. I have attached a file for more clarification. I have a master sheet and 7 additional sheets. Master sheet looks like this:

Serial No. Order No. Description No. Assigned To Status

. We have 5 columns with heading as above. The rows will be copied to respective child sheets based on the criteria of column 4 (Assigned To) and column5 (Status). In column -Assigned To any of the 4 names can be entered. And in column -Status we can enter any of the 3 different status.

I have attached a file for more clarification.

Requirements:
1. We have 8 worksheets including Master.
2. We do not have to program to create a new worksheet.
4. As we enter the details in the master sheet, the data should be updated based on the criteria mentioned in above details.
5. The columns are removed from EMP1,EMP2, EMP3 & EMP4 however the rest of the 3 sheets have all the columns.

Your cooperation will be very much appreciated if you there is anything you could do.
thanks & regards,
Mark