VBA: Deleting all sheets but one, not knowing sheet names

B

Beowulf

New Member
Joined
Oct 5, 2005
Messages
26
I have to do some data cleaning of different files where every sheet has its unique name.
The sheet with the macrobutton should be saved, the rest of the sheets deleted. Why doesn't the following work:

<code>
Dim ws As Worksheet
dim MADDATA as Worksheet
Set MADDATA = ActiveSheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws <> MADDATA Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
</code>

Help would be much appreciated!
 

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
Sort by date Sort by votes
I believe you need to declare what you are trying to compare rather than just comparing the objects? Try:
Rich (BB code): 
Dim ws As Worksheet
dim MADDATA as Worksheet
Set MADDATA = ActiveSheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.name <> MADDATA.name Then
ws.Delete
End If
Next
Application.DisplayAlerts = True
Hope that works.
 
Last edited by a moderator:
Upvote 0
Not sure why this is showing up in two code boxes when I only want one and I even edited to remove the extra code box?
 
Upvote 0
Dearest schielrn!

Wow! It took the whole of 10 seconds for you to assess and address my problem. My sincere thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

Guinaba
  • Solved
Looping across all tabs and deleting empty rows
Replies
6
Views
285
Alex Blakenburg
A
R
  • Solved
SaveAs method not actually saving, wrong sheets being deleted (1004 Error)
Replies
4
Views
628
Alex Blakenburg
A
S
  • Question
Saving as dialog box. Application.DisplayAlerts not working
Replies
7
Views
448
Serafin54
S
H
  • Question
Macro to save sheet as a CVS
Replies
5
Views
366
Joe4
Joe4
J
  • Solved
VBA script - split sheets into unique folder named of cell entry
Replies
7
Views
474
james00
J

Forum statistics

Threads
1,221,590
Messages
6,160,668
Members
451,662
Latest member
reelspike

Share this page

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
Back
Top