Need code to move data to new worksheets

[ScorpioMCN]

I've searched the forums and elsewhere but can't locate an answer to my specific issue.

Here's what I'm trying to do in Excel...I'm fairly new at VB code though

I have one tab full of journal line data on roughly 20 different g/l accounts (more to be added later) starting in row 8, column B (column header is in row 7). I need VB code that will take that data and create a new tab (incl. headers) for EACH unique account number (naming each new tab as that account number eg "121201-000", "123201-005", etc.) and then move that account number's data to that new tab (there is no set # of rows for each account, each account could contain 12 rows or 1212).

The "Data" tab is already sorted by account number.
Account Number Journal Date Amount Etc Etc
121201-000
121201-000
123201-005
123201-005
123201-005
123201-005
123201-005
123201-009
123201-009
123201-009

Once the "Data" tab is empty (all data moved) I'd like it deleted - that is no problem - need help on the rest however.

Would very much appreciate help on this.

 

[SkipVought]

Hi,

Say it ain't so, Scorpio!

You want to take a perfectly good (I'm assuming) table and chop it up into different sheets, and then destroy the original table???

You'll be back here posting, like so many other unsuspecting novice spreadsheet users, "how can I get a summary of my severly scattered data?"

Best and accepted practices leads to consolidate data for similar data, not segmented data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[ScorpioMCN]

Nope you assume too much Skip

It's not a table - the nVision Excel layout I've created uses a query that brings this data into my report...it's a account reconciliation report for our accountants - they want each account's data in one tab. Much more efficient to bring it all in in one query then run 20 separate queries (one for each account).

I just need some code help - not a scolding.

 

[SkipVought]

Much more efficient to run ONE query with a parameter for account in 20 sheets than to bring them all into one sheet and then divy them up.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Skip no doubt is correct especially if you know in advance what accounts will exist in your data. However, what I would do is either:

Create a pivot table with Account as a page field. Then Use "Show Pages" to create a separate sheet for each account.

Or use advanced filter to generate a list of unique account codes. Then loop through that list using it to generate a criteria field to extract the data to each sheet (again using advanced filter).


Gavin

 

[ScorpioMCN]

I appreciate your responses but with all due respect, you guys are off in left field - all I'm looking for is some VB code to move the data I've brought into my report for each account to a new worksheet.

This is not that complicated of a request - I just don't know how to code it. I don't want a pivot table - the accountants want each account's data in a separate tab - if I have to create a query for each tab that's one option but that would take much more server processing and we'd prefer to not do that.

Please can you help me with VB code to do this? Thanks.

 

[PHV]

So, what have you YOU tried so far and where in YOUR code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ScorpioMCN]

I have nothing yet - because as I said I'm new to VB - I have done macros somewhat before but nothing quite like this situation. I have tried to search for answers in books and online but nothing seems to fit what I'm needing.

I think I spelled out pretty well what I need above - if someone could get me started with some code to move my data to individual worksheets I'd really appreciate it.

 

[SkipVought]

Here's a strategy.

1. Loop thru a list of all the unique Account Numbers...

dim r as range
for each r in Range(YourAcctNbrRange)
  
next

Macro record using the AutoFilter on the Account Number. Modify to accept each r.value as a criteria.

Macro record inserting a new sheet and naming the sheet.

Macro record selecting the ROWS in the table, Copy, select the new sheet and paste special - VALUES.

The code for each of these macros, modified to suite, will go inside the loop.

Post your recorded code to get specific help.




Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gavona]

Following Skip's strategy. Use Recorder.
If you don't have a unique list then use the advanced filter to create one.
Use a named range "Unique" to specify the Output range (on a blank sheet). This will be a single cell containing your column heading ("Account number").
Once you have extracted the unique list then Range("Unique").CurrentRegion could be substituted for
YourAcctNbrRange in Skip's code.

Once you have a go and share what you have tried we will help you to make it more generic and efficient but these forums are about helping one another not doing it for you.

Gavin