Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Add a worksheet
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Add a worksheet
Resolved · Urgent Priority · Version 2007
Reply Wed 30 Mar 2011, 12:36Delegate Tracey said...
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).
For upcoming training course dates see: Pricing & availability
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
|