Unable to combine submacros to work as one macro

[lscarstens]

To Whomeever Might be Willing to Help Me -

I am pretty new at this (my first macro project) so I would appreciate any positive help.

1) I have several sub macros that were working by themselves but when I combined them in the CostCenterMacro the
Sub CostCenterstoSheets() would no longer run and it was pointing me towards
Set ws = Sheet1 - what do I need to change this to in order to make the sub macro work again?
2) Once this works - what do I need to add so the 2nd 2 sub macros work for all spreadsheets not just 1

Thank you!
LC

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Paul]

To quickly answer your first question, you are using

Please Login or Register  to view this content.

Excel doesn't know what you mean by Sheet1. In this case it is being considered just another (undeclared) variable with no value. Since ws is Dim'd as Worksheet, it's expecting an object. You have to refer to Sheet1 as an object, in that case. Try:

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

[lscarstens]

Hi Paul,

Thank you! I tried your suggestion and it worked perfectly!
As you can tell I am definately a MacroNewbie.

Now to Question#2 - Can you help me with this as well?
When I run the macro for the data in Sheet1 I get 5 additional sheets named (just as an example) 5040, 5041, 5042, 5043, and 5044. The sort&subtotal and print sub macros work on the last spreadsheet 5044. What do I need to do to have the sort&subtotal and print sub macros recycle and update 5040,5041, 5042, and 5043 before I add another command to delete the original Sheet1?

I appreciate your help!! "There are no stupid questions - they are just the easiest to answer" - thank you for answering what I am sure is Easy for you