macro - Select All Sheets

Cosmos75 · Apr 8th, 2002, 01:02 PM

I am trying to create a macro to select all sheets in active workbook and copy them into a new book (Named "Copy.xls") that doesn't contain the macros and buttons in the original used to launch the macro. Any quick way to do this?

At the least, I would like to know how to write code to select all sheets. THANKS!

[ This Message was edited by: Cosmos75 on 2002-04-08 12:03 ]

Al Chara · Apr 8th, 2002, 01:12 PM

Worksheets.copy will copy all the worksheets to a new workbook. As for not copying code, I do not know.

Ricky Morris · Apr 8th, 2002, 01:14 PM

Sub Select_All_Sheets()
Dim sht As Worksheet
Dim SelectMe() As String
Dim s As Integer
For Each sht In Worksheets
s = s + 1
ReDim Preserve SelectMe(1 To s) As String
SelectMe(s) = sht.Name
Sheets(SelectMe).Select
Next
End Sub

Cosmos75 · Apr 8th, 2002, 01:32 PM

Got this code to select buttons and delete them.

ActiveSheet.Shapes.Range(Array("Button 1", "Button 2", "Button 3")).Select

Is there a way to get it to select all the buttons and delete them?

Al Chara · Apr 8th, 2002, 01:39 PM

How about the following:

ActiveSheet.Shapes.SelectAll
Selection.Delete

Ricky Morris · Apr 8th, 2002, 01:48 PM

The following code will accomplish the same as the code I posted above.

worksheets.Select

Apr 8th, 2002, 01:12 PM	#2
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	Worksheets.copy will copy all the worksheets to a new workbook. As for not copying code, I do not know. __________________ Kind regards, Al Chara

Apr 8th, 2002, 01:14 PM	#3
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	Sub Select_All_Sheets() Dim sht As Worksheet Dim SelectMe() As String Dim s As Integer For Each sht In Worksheets s = s + 1 ReDim Preserve SelectMe(1 To s) As String SelectMe(s) = sht.Name Sheets(SelectMe).Select Next End Sub __________________ It's never too late to learn something new. Ricky

Apr 8th, 2002, 01:39 PM	#5
Al Chara MrExcel MVP Join Date: Feb 2002 Location: Monterrey, Mexico Posts: 1,433	How about the following: ActiveSheet.Shapes.SelectAll Selection.Delete __________________ Kind regards, Al Chara

Apr 8th, 2002, 01:48 PM	#6
Ricky Morris Board Regular Join Date: Mar 2002 Posts: 363	The following code will accomplish the same as the code I posted above. worksheets.Select __________________ It's never too late to learn something new. Ricky

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 8th, 2002, 01:02 PM	#1
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	I am trying to create a macro to select all sheets in active workbook and copy them into a new book (Named "Copy.xls") that doesn't contain the macros and buttons in the original used to launch the macro. Any quick way to do this? At the least, I would like to know how to write code to select all sheets. THANKS! [ This Message was edited by: Cosmos75 on 2002-04-08 12:03 ]

Apr 8th, 2002, 01:32 PM	#4
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	Got this code to select buttons and delete them. ActiveSheet.Shapes.Range(Array("Button 1", "Button 2", "Button 3")).Select Is there a way to get it to select all the buttons and delete them?