Various Errors - program to create separate sheets for each account from data dump

[wellen72]

Hi All,

I run a query that has 10 accounts and I'm trying to write a program that will take the original data dump and create a new worksheet, rename it for a given account # and delete all accounts except a specified one and repeat until I have a worksheet for each account and still have the results from the original query. The approach I'm taking seems to work when I started writing it for just one account but I run into problems when I add the block of code for the other account. It seems to run super quick for one but when the coding for the other accounts is added the procedure runs and runs and I have to break the script. Even when I comment all the other accounts and try to run the first account I run into different errors. Can anyone see a problem or have a recommendation for a different approach?

Please Login or Register  to view this content.

[mikeTRON]

Where is it breaking?

This link should help you walk through the code and such.
http://www.cpearson.com/excel/DebuggingVBA.aspx

[wellen72]

it is breaking at ActiveSheet.Name = "10-111595" at the beginning.

[mikeTRON]

Does that sheet name ALREADY exist?

[wellen72]

I'm pretty sure that there wasn't already a sheet with that name. When I first got the error I looked to see if there was a sheet with that name and there wasn't and I ran it again and got the same error. I just NOW ran the proc and I didn't get the same error this time it broke at the line 4 lines below the ActiveCell.Offset(-1, 0).Select line. Weird

[bakerman2]

Instead of copying the sheet over and over and deleting unwanted rows it's better to create unique list of accounts, autofilter main sheet using each element of that unique list, copy visible cells to seperate sheets and your done.