Tracey has attended:
Excel VBA Intro Intermediate course
Add a worksheet
I was provided on the course with code to search for a sheet and delete it if it exists, and also to add a new sheet.
I am trying to amalgamate code to say
for each sheet, if the sheet is named x, clear contents otherwise create sheet named 'x', but it tries to create the sheet as soon as it hits another sheet.
How do I write the code to create a sheet named 'x' if it doesn't exist? (I can't delete existing sheets named 'x' as other sheets reference them).
RE: add a worksheet
Hi Tracey,
Thank you for your question.
I think the following code may help you as a good starting point as you would have the code to create a new worksheet anyway from the course.
The following routine checks the collection of sheets to say delete all sheets that is not equal to 'Total Sales' and 'List Page'.
You can try and adapt this:
Dim shtNames() As String ' variable
Application.DisplayAlerts = False
ReDim shtNames(1 To ActiveWorkbook.Sheets.Count) 'resizing array
For i = 1 To Sheets.Count
shtNames(i) = Sheets(i).Name
Next i
'array that contains all the sheet names within it
For i = 1 To Sheets.Count
If Sheets(shtNames(i)).Name <> "Total Sales" And Sheets(shtNames(i)).Name <> "List Page" Then
Sheets(shtNames(i)).Delete
Else
End If
Next i
Application.DisplayAlerts = True
MsgBox "All previous sheets have been deleted"
I hope this helps.
Regards
Simon
