Does anyone have SIMPLE code for this that can be run in a normal Sub. My worksheet will always be named "Cleaned". I have seen some posts regarding Functions and other things that seem more complicated than necessary.
Thanks.
Delete Worksheet If It Exists
-
-
-
-
-
Re: Delete Worksheet If It Exists
Okay, well im not quite shore why you changed the notation to represent the sheet name because the code is looping through all the worksheets in the workbook not all all the worksheets in the workbook named cleaned. But anyway what ever suits you best the code is still correct and your the one using it.
Try checking to make sure there is not a space after Cleaned on the worksheet tab. Or try creating a new sheet naming it test and deleting it with the code to see if that works.
You could also try temprorarily removing the application.displayalerts part of the code to see what message you get.
-
Re: Delete Worksheet If It Exists
Failing that, post the workbook as an example and I will have a look at it.
-
-
Re: Delete Worksheet If It Exists
Do you mean changing ws to Clnd?
Attached is the file & all code. -
Re: Delete Worksheet If It Exists
Okay I downloaded your workbook and the code is working.
But it may not be working as you need it to.
Could you tell me what you are trying to do with the code.
Your code creates a worksheet named "cleaned". At the moment you have the code set to delete the sheet cleaned before it is created. When do you want to delete the sheet?
-
Re: Delete Worksheet If It Exists
try this:
it will create your sheet run your code and then delete the sheet named cleaned at the end.
Also you need to turn screenupdating back on at the end which I have done for you.
And also, generally you dont need to select cells to work with them. You may want to look into that.
Code
Display Moreub CleanContacts() Dim cLen As Long Application.ScreenUpdating = False Sheets.Add.Name = "Cleaned" Sheets("Contacts").Columns("A:A").Copy Range("A1").PasteSpecial Sheets("Contacts").Columns("C:C").Copy Range("C1").PasteSpecial cLen = Range("C1").End(xlDown).Row Range("B1").FormulaR1C1 = "=CONCATENATE(Contacts!RC[-1],"" "",Contacts!RC)" Range("B1", Cells(cLen, 2)).Formula = Range("B1").Formula Range("C1").FormulaR1C1 = "=CONCATENATE(Contacts!RC[1],"", "",Contacts!RC[2],"", "",Contacts!RC[3],"" "",Contacts!RC[4])" Range("C1", Cells(cLen, 3)).Formula = Range("C1").Formula Sheets("Contacts").Columns("I:M").Copy Range("D1").PasteSpecial Columns("A:A").Delete Shift:=xlToLeft Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues Range("L1").FormulaR1C1 = "=Contacts!RC[-9]" Range("L1", Cells(cLen, 12)).Formula = Range("L1").Formula Range("M1").FormulaR1C1 = "=RC[-12]" Range("M1", Cells(cLen, 13)).Formula = Range("M1").Formula Columns("M:M").Copy Columns("N:S").PasteSpecial Cells.Copy Cells.PasteSpecial Paste:=xlValues Application.CutCopyMode = False Columns("L:S").Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False Columns("A:K").Delete Shift:=xlToLeft Range("B1").FormulaR1C1 = "Name" Range("C1").FormulaR1C1 = "Address" Range("D1").FormulaR1C1 = "Fax Number" Range("E1").FormulaR1C1 = "Direct Line" Range("F1").FormulaR1C1 = "Company Phone Number" Range("G1").FormulaR1C1 = "Mobile Phone Number" Range("H1").FormulaR1C1 = "E-mail Address" Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.EntireColumn.AutoFit Columns("C:C").Cut Columns("B:B").Insert Shift:=xlToRight Columns("F:F").Cut Columns("C:C").Insert Shift:=xlToRight Columns("E:E").Cut Columns("D:D").Insert Shift:=xlToRight Range("A1").Select Application.DisplayAlerts = False Sheets("Cleaned").Delete Application.DisplayAlerts = True Application.ScreenUpdating = True End Sub -
Re: Delete Worksheet If It Exists
I can't have it delete the sheet at the end otherwise it defeats the purpose of the code that is running. "Cleaned" are the results that I need to look at.
-
Re: Delete Worksheet If It Exists
Try this:
Code
Display MoreSub CleanContacts() Dim cLen As Long Application.ScreenUpdating = False Application.DisplayAlerts = False Sheets("Cleaned").Delete Application.DisplayAlerts = True Sheets.Add.Name = "Cleaned" Sheets("Contacts").Columns("A:A").Copy Range("A1").PasteSpecial Sheets("Contacts").Columns("C:C").Copy Range("C1").PasteSpecial cLen = Range("C1").End(xlDown).Row Range("B1").FormulaR1C1 = "=CONCATENATE(Contacts!RC[-1],"" "",Contacts!RC)" Range("B1", Cells(cLen, 2)).Formula = Range("B1").Formula Range("C1").FormulaR1C1 = "=CONCATENATE(Contacts!RC[1],"", "",Contacts!RC[2],"", "",Contacts!RC[3],"" "",Contacts!RC[4])" Range("C1", Cells(cLen, 3)).Formula = Range("C1").Formula Sheets("Contacts").Columns("I:M").Copy Range("D1").PasteSpecial Columns("A:A").Delete Shift:=xlToLeft Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues Range("L1").FormulaR1C1 = "=Contacts!RC[-9]" Range("L1", Cells(cLen, 12)).Formula = Range("L1").Formula Range("M1").FormulaR1C1 = "=RC[-12]" Range("M1", Cells(cLen, 13)).Formula = Range("M1").Formula Columns("M:M").Copy Columns("N:S").PasteSpecial Cells.Copy Cells.PasteSpecial Paste:=xlValues Application.CutCopyMode = False Columns("L:S").Replace What:="0", Replacement:="", LookAt:=xlWhole, SearchOrder:=xlByRows, MatchCase:=False Columns("A:K").Delete Shift:=xlToLeft Range("B1").FormulaR1C1 = "Name" Range("C1").FormulaR1C1 = "Address" Range("D1").FormulaR1C1 = "Fax Number" Range("E1").FormulaR1C1 = "Direct Line" Range("F1").FormulaR1C1 = "Company Phone Number" Range("G1").FormulaR1C1 = "Mobile Phone Number" Range("H1").FormulaR1C1 = "E-mail Address" Cells.Select Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom Selection.EntireColumn.AutoFit Columns("C:C").Cut Columns("B:B").Insert Shift:=xlToRight Columns("F:F").Cut Columns("C:C").Insert Shift:=xlToRight Columns("E:E").Cut Columns("D:D").Insert Shift:=xlToRight Range("A1").Select Application.ScreenUpdating = True End Sub -
-
Re: Delete Worksheet If It Exists
Here is how I would do what you are trying to do:
Code
Display MoreSub CleanContacts() Dim ws As Worksheet Dim c As Range Dim SrtRng As Range For Each ws In ThisWorkbook.Worksheets If ws.Name = "Cleaned" Then Application.DisplayAlerts = False ws.Delete Application.DisplayAlerts = True End If Next ws Sheets.Add.Name = "Cleaned" With Worksheets("Cleaned") .Range("A1").Value = "Company" .Range("B1").Value = "Address" .Range("C1").Value = "Company Phone Number" .Range("D1").Value = "Fax Number" .Range("E1").Value = "Name" .Range("F1").Value = "Direct Line" .Range("G1").Value = "Mobile Phone Number" .Range("H1").Value = "E-mail Address" End With For Each c In Worksheets("Contacts").Range("A2", Worksheets("Contacts").Range("A65536").End(xlUp)) With Worksheets("Cleaned").Range("A65536").End(xlUp) .Offset(1, 0).Value = c.Offset(0, 2).Value .Offset(1, 1).Value = c.Offset(0, 3).Value & ", " & c.Offset(0, 4).Value & ", " & c.Offset(0, 5).Value & ", " & c.Offset(0, 6).Value .Offset(1, 2).Value = c.Offset(0, 10).Value .Offset(1, 3).Value = c.Offset(0, 8).Value .Offset(1, 4).Value = c.Value & " " & c.Offset(0, 1).Value .Offset(1, 5).Value = c.Offset(0, 9).Value .Offset(1, 6).Value = c.Offset(0, 11).Value .Offset(1, 7).Value = c.Offset(0, 12).Value End With Next With Worksheets("Cleaned").Range("A1", Worksheets("Cleaned").Range("A65536").End(xlUp).Offset(0, 7)) .Sort Key1:=Range("A2"), Order1:=xlAscending, Key2:=Range("B2"), Order2:=xlAscending, Header:=xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom .EntireColumn.AutoFit End With End Sub -
-
Re: Delete Worksheet If It Exists
Yes I agree handling the error if the sheet doesnt exsist is better that cycling through the sheets to see if it exsists even if there is only two sheets as you might want to add more.
How ever you will still need to handle the alert so:
-
Re: Delete Worksheet If It Exists
That looks like it just might work. I'll try tomorrow when I get in to work and let you know. Thanks a lot!
-
Re: Delete Worksheet If It Exists
Nope. No Luck. I thought it might help to make a seperate sub and run it from the other code but that didn't help either.
-
-
Re: Delete Worksheet If It Exists
What line of code fails with what error message?
-
Re: Delete Worksheet If It Exists
It's not that the code bugs exactlly, it just doesn't delete the sheet. The code after that (which inserts a new sheet called "Cleaned") bugs.
-
Re: Delete Worksheet If It Exists
I have attached a working example of or your code. Its tested and works...
Let me know how you get on.
-
Re: Delete Worksheet If It Exists
Why delete the sheet? Why not just clear or delete the contents if you're just going to re-add. Would save a step.
ska
-
Re: Delete Worksheet If It Exists
That's weird, I tested the attachment & I bugged on me.
As far as clearing the sheet, I might have to do that, but at the same time, there should be a way to do this. -
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!