PDA

View Full Version : Solved: Preserve sheets before Macros Runs



Nurofen
10-14-2007, 08:54 AM
Hi,

How can i ask the macro to preserve sheets before it starts running.


Thankyou for your time


Nurofen

lucas
10-14-2007, 09:18 AM
Will you know the name of these sheets:
Maybe something like:
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Delete
Next

Nurofen
10-14-2007, 09:42 AM
Hi Lucas,

I tried the below but it asks if I want to delete the sheets. I want the macro to start after the sheets. I have the sheet names there are five in total 1)miss1 2)miss2 3)miss3 4)Master copy 5)Home Page. 6th and after I want the Macro to run on.

Thankyou for your time

Nurofen

lucas
10-14-2007, 09:49 AM
Are you actually going to delete sheets? If not then that should not be a problem. If you are then you can use
Sub a()
Dim ws As Worksheet
For Each ws In Worksheets
Application.DisplayAlerts = False
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Delete
Application.DisplayAlerts = True
Next
End Sub
If you set Application.DisplayAlerts to False...always set it back to true at the end of the routine.

Nurofen
10-14-2007, 09:57 AM
Thanks Lucas,

No not deleting any sheets

I missed around with:



For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Delete
Next


And used it like this



Sub MyMacro12()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Miss1" And ws.Name <> "Miss2" And ws.Name <> "Miss3" _
And ws.Name <> "MASTERCOPY" And ws.Name <> "Home Page" Then 'ws.Count
'For Each ws In ActiveWorkbook.Worksheets
With ws
.Activate
ActiveWindow.Zoom = 75
.Columns("A").Columns.AutoFit
.Columns("B").ColumnWidth = 10.29
.Columns("G").ColumnWidth = 11.86

.Columns("H").ColumnWidth = 12
.Columns("I").ColumnWidth = 11.71
.Columns("J").ColumnWidth = 11.29
.Columns("S").ColumnWidth = 12
.Columns("T").ColumnWidth = 12.86
.Columns("U").ColumnWidth = 2.29
End With
'For Each ws In ActiveWorkbook.Worksheets
'Next ws

End If
Application.ScreenUpdating = True
Next
End Sub

Thank you very much again for you help Lucas now everything is working the way it should.


Nurofen

Norie
10-14-2007, 10:42 AM
Nurofen

A Select Case might be a better approach here.
Sub MyMacro12()
Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
Select Case ws.Name
Case "Miss1", "Miss2", "Miss3", "MASTERCOPY", "Home Page"
' do nothing
Case Else
With ws
.Activate
ActiveWindow.Zoom = 75
.Columns("A").Columns.AutoFit
.Columns("B").ColumnWidth = 10.29
.Columns("G").ColumnWidth = 11.86

.Columns("H").ColumnWidth = 12
.Columns("I").ColumnWidth = 11.71
.Columns("J").ColumnWidth = 11.29
.Columns("S").ColumnWidth = 12
.Columns("T").ColumnWidth = 12.86
.Columns("U").ColumnWidth = 2.29
End With

End Select
Next ws

Application.ScreenUpdating = True
End Sub

Nurofen
10-14-2007, 11:16 AM
Thank you Norie,
:thumb

Thanks for your time


Nurofen