Code Run Time Error

steveh1983

New Member
Joined
Feb 18, 2016
Messages
4
Hi Guys,

Trying to run the below code but is falling over at wsh.Select F. The code seems to work in a different workbook I have (smaller file) but comes up with run time error now.

Code
Dim wsh As Worksheet
Dim f As Boolean
f = True
For Each wsh In Worksheets
Select Case wsh.Name
' List the exceptions here
Case "Contract Extract", "Tree", "Total Region", "Start", "BOB", "ESA", "ESB", "LNA", "LNB", "LNC", "LND", "LNG", "LNH", _
"LNN", "LNO", "LNP", "LNQ", "LNR", "LNS", "LNT", "LNV", "LNW", "LNX", "LNY", "LNZ", "RAA", _
"SHA", "SHB", "SKA", "SKB", "SKC", "SKD", "SKO", "End"
' Skip these
Case Else
wsh.Select f
f = False
End Select
Next wsh

Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
What's the error message?
 

steveh1983

New Member
Joined
Feb 18, 2016
Messages
4
it comes up with below

run time error 1004 method select of object _worksheet failed
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
What are you trying to do with the code?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,282
Office Version
  1. 365
Platform
  1. Windows
Why not just delete the worksheets as you loop?
Code:
Case Else
    Application.DisplayAlerts = False
    wsh.Delete
    Application.DisplayAlerts = True
End Select
 
Last edited:

steveh1983

New Member
Joined
Feb 18, 2016
Messages
4
Why not just delete the worksheets as you loop?
Code:
Case Else
    Application.DisplayAlerts = False
    wsh.Delete
    Application.DisplayAlerts = True
End Select

Thanks - will try this when back in the office
 

Watch MrExcel Video

Forum statistics

Threads
1,129,752
Messages
5,638,162
Members
417,011
Latest member
Amaden95

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top