ExcelBanter

ExcelBanter (https://www.excelbanter.com/)

- New Users to Excel (https://www.excelbanter.com/new-users-excel/)

- - Code (https://www.excelbanter.com/new-users-excel/17492-code.html)

The following code works well, however how can I change
this to run with a command button.
Also is there any way I could delete some columns from
the sheets as well.

Private Sub Workbook_Open()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations
summary", "RCA incl RIM", "First Qtr", "Second
Qtr", "Third Qtr", "Fourth Qtr")).Delete
Application.DisplayAlerts = True

End Sub

Thanks

Mark

Hi Mark

This example will delete column A and B in each sheet

Alt-F11
InsertModule from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you use Alt-F8 you get a list of your macro's
Select "test" and press Run

Or right click on a commandbar and click on Forms
Add a button on your sheet and assign the macro to it

Sub test()
Dim wb As Workbook
Dim sh As Worksheet
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations summary", "RCA incl RIM", "First Qtr", "Second Qtr", "Third Qtr", "Fourth
Qtr")).Delete
Application.DisplayAlerts = True

For Each sh In wb.Worksheets
sh.Columns("A:B").EntireColumn.Delete
Next
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Mark" wrote in message ...
The following code works well, however how can I change
this to run with a command button.
Also is there any way I could delete some columns from
the sheets as well.

Private Sub Workbook_Open()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations
summary", "RCA incl RIM", "First Qtr", "Second
Qtr", "Third Qtr", "Fourth Qtr")).Delete
Application.DisplayAlerts = True

End Sub

Thanks

Mark

Soz i have posted the first part of this query twice.
i now have the command button working.
so can anyone help with the second part ie deleting
colums. Is there anyway you can stop the command button
from transfering to the new sheet.

thanks for this

mark.
-----Original Message-----
The following code works well, however how can I change
this to run with a command button.
Also is there any way I could delete some columns from
the sheets as well.

Private Sub Workbook_Open()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations
summary", "RCA incl RIM", "First Qtr", "Second
Qtr", "Third Qtr", "Fourth Qtr")).Delete
Application.DisplayAlerts = True

End Sub

Thanks

Mark

.

this work great thanks for your help.

-----Original Message-----
Hi Mark

This example will delete column A and B in each sheet

Alt-F11
InsertModule from the menubar
paste the sub in there
Alt-Q to go back to Excel

If you use Alt-F8 you get a list of your macro's
Select "test" and press Run

Or right click on a commandbar and click on Forms
Add a button on your sheet and assign the macro to it

Sub test()
Dim wb As Workbook
Dim sh As Worksheet
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
wb.Sheets(Array("Suspense", "RCA exc
RIM", "Operations summary", "RCA incl RIM", "First
Qtr", "Second Qtr", "Third Qtr", "Fourth
Qtr")).Delete
Application.DisplayAlerts = True

For Each sh In wb.Worksheets
sh.Columns("A:B").EntireColumn.Delete
Next
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl

"Mark" wrote in
message ...
The following code works well, however how can I change
this to run with a command button.
Also is there any way I could delete some columns from
the sheets as well.

Private Sub Workbook_Open()
Dim wb As Workbook
'copy all sheets
Worksheets.Copy
Set wb = ActiveWorkbook
Application.DisplayAlerts = False
'delete the sheets you want
wb.Sheets(Array("Suspense", "RCA exc RIM", "Operations
summary", "RCA incl RIM", "First Qtr", "Second
Qtr", "Third Qtr", "Fourth Qtr")).Delete
Application.DisplayAlerts = True

End Sub

Thanks

Mark

.