A macro (VBA) to group a number of worksheets

[Guest]

I want to write a macro that will select the pages in an open workbook and then save them to a new workbook.
The following example works provided the sheets have the name 1,2 or 3. My problem is that the sheets do not always have the same name. How can I modify this macro? or is there a better way.

Sub Macro1()
'
Sheets(Array("1", "2", "3")).Select
Sheets("3").Activate
Sheets(Array("1", "2", "3")).Copy Before:=Sheets(1)
End Sub

Thanks
John

 

[Guest]

Many thanks Russell. This solved my problem.
One further question, when I save this new workbook it saves references to the original workbook that I don't want saved. Since I have a lot of macro buttons on the work sheet it causes error messages if they are clicked. (When the workbook is reopened). Can I disable the ref to unsaved documents?

Thanks
John

 

[Russell Hauf]

On 2002-03-13 16:58, Anonymous wrote:
Many thanks Russell. This solved my problem.
One further question, when I save this new workbook it saves references to the original workbook that I don't want saved. Since I have a lot of macro buttons on the work sheet it causes error messages if they are clicked. (When the workbook is reopened). Can I disable the ref to unsaved documents?

Thanks
John

I guess I'd have to ask what kind of references you're talking about? Cell/formula references, or references in your code?

 

[Guest]

References are to macros.

 

[Russell Hauf]

Can you give an example? List the code if you can (all references)...

 

[Guest]

Thanks Russell

I think I may be attacking this the hard way. It might be easier to assign a new macro to the buttons, although I’m not sure how to go about this.

I’ll explain what I am doing. I have a workbook containing 33 worksheets and a very large amount of macro code. This workbook is about 1.4 mb.

By making various entries and choices the user has a minimum of 3 and a maximum of 4 pages that they need to save. In order to reduce file size I am copying these pages to a new workbook (thanks to your code) and then saving this new workbook. The original workbook is not saved. The problem is that when the user opens the saved book if they click on any of the buttons they get a message saying (workbook1) macro not found. What I was trying to do was remove this reference, however in retrospect I think the better way would be to write a macro that would assign a new macro to the buttons. (a pop up message telling the user they were disabled).

My problem here is that sometimes there will be 4 sheets and sometimes only 3. The sheet names will vary as will the number of buttons on each sheet. Another problem I am finding is that since I sometimes did a cut and paste of buttons often more than one is called button one (these were created from forms) so I could have say 3 button 1’s, 1 button 7 etc (incidentally can you rename these buttons).

Is there a simple way to produce a macro that will select each sheet in turn, determine the number of buttons on that sheet then assign a new macro to them? I know any macro I write will be long and complicated compared with the simple code you provided for me to copy the sheets.

Thanks for your time and help

Regards John