Lookup del

chiragggg

Board Regular
Joined
Mar 18, 2010
Messages
197
Hi,

I have a a list of worksheet names in a worsheet. e/g column Z.

The list is the worksheet names that need to be deleted in the workbook,

I wanted a macro that looks at these worksheets and deletes any worksheet names that are on that list...

Any suggestions?
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Special-K99

Well-known Member
Joined
Nov 7, 2006
Messages
8,344
This works, assumes list is on Sheet1 in column A

You'll probably want to uncomment the "Application.DisplayAlerts" below so that you dont get prompted each time a worksheet is deleted

Code:
Sub k1()

'Application.DisplayAlerts = False

For Each c In Worksheets("Sheet1").Range("a1:a2").Cells
Sheets(c.Value).Delete
Next c

'Application.DisplayAlerts = True

End Sub
 

chiragggg

Board Regular
Joined
Mar 18, 2010
Messages
197
Does "c" variable need to be defined?

If I had the list in column Z could I use Z1:z4 as the range?
 

chiragggg

Board Regular
Joined
Mar 18, 2010
Messages
197
Get an error 91

Object variable or With block variable not set (Error 91)

Code below


Sub finaldelsheets()

Dim Ws As Worksheet
Dim c As Range


'select worksheet lookup

Sheets("Approved").Select
Dim MyName As String
MyName = Range("c2")

'deletes analysis sheets that are selected at start of process

Application.DisplayAlerts = False

For Each c In Ws("MyName").Range("z1:z4").Cells
Sheets(c.Value).Delete
Next c

Application.DisplayAlerts = True


End Sub
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,198
Messages
5,509,785
Members
408,752
Latest member
KrisF

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top