VBA- Adding Sheet Looping and Sheet exists

[PhosFeedLogisticsMan]

I am relatively new to VBA. Getting Run time error 1004: That name already taken. Try a different one.

Here is what I have, I just recently added the Loop, but I don't think I need it, and believe its something simple I am missing or don't understand.
Quick Background- Looks at a separate worksheet, all data in column A and will be adding to this column as needed as new (SCAC codes) are added. I will need to delete all of these worksheets (in order for something else in my wb to work), then add this list back again as new sheets when I am done(using that sheet called SCAC_Codes). When I run what I have... it will go down the list until it hits an existing named sheet and tell me it exists, and then stop/debug. I want the code to not add it because it exist, but keep going down the column and adding the rest until it gets to the end(blank cell). Then stop. Hope this is not to much info, and that someone can find a simple solution, as I know it is something easily fixed.


Sub RenameSheet()Application.ScreenUpdating = False
Sheets("SCAC_Codes").Activate
Dim myCell As Range, MyRange As Range

Set MyRange = Sheets("SCAC_Codes").Range("A1")
Set MyRange = Range(MyRange, MyRange.End(xlDown))
For Each myCell In MyRange
Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
Sheets(Sheets.Count).Name = myCell.Value ' renames the new worksheet
Do Until IsEmpty(ActiveCell)
ActiveCell.Offset(1, 0).Select
Loop
Next myCell

If ActiveSheet.Range("A1").Value = "" Then Exit Sub

Application.ScreenUpdating = True
End Sub

 

[RickXL]

Hi and welcome to the MrExcel Message Board.

Try this:

Sub RenameSheet()
    Dim myCell      As Range
    Dim MyRange     As Range
    Dim sht         As Object
    Dim shtExist    As Boolean
    
    Application.ScreenUpdating = False
    Sheets("SCAC_Codes").Activate
    Set MyRange = Sheets("SCAC_Codes").Range("A1")
    Set MyRange = Range(MyRange, MyRange.End(xlDown))
    
    For Each myCell In MyRange
        shtExist = False
        For Each sht In Sheets
            If sht.Name = myCell.Value Then shtExist = True: Exit For
        Next
        If Not shtExist Then
            Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
            Sheets(Sheets.Count).Name = myCell.Value ' renames the new worksheet
        End If
    Next myCell
End Sub

Every time it reads a new name from the worksheet, it loops round the sheets to see if that name already exists. If it does not then it creates the new sheet. If it does then it skips that part.


Regards,

 

[jim may]

I'de add the codeline

Application.ScreenUpdating = True

just above the last codeline End Sub

 

[RickXL]

Hi Jim,

Yes, I know you are supposed to but I am afraid that I always remove it.

If you explicitly need to turn it back on because you are going to perform some more processing in that Sub then it needs to be there, otherwise not, as far as I can tell.

The clincher was that when I timed it (not this particular code but something with a lot of data) it ran slightly faster without it than with.


Regards,

 

[jim may]

Thanks Rick..
I too vaguely remember the TRUE statement wasn't required, but I am now, thanks to you..
You ALWAYS give solid advice.
Thanks,
Jim

 

[RickXL]

You ALWAYS give solid advice.

Thanks for the vote of confidence. I just hope I will not disappoint you.


Regards,

 

[PhosFeedLogisticsMan]

This worked great RickXL! Thank you for your help. Also learned a few things along the way.

Also Jim May- what exactly does : Application.ScreenUpdating = True do? I was told by an office peer who does a lot more VBA to add it to end and also to add the = False to the beginning of sub after the sub identifier.

No idea what these do but I add them anyway.

 

[jim may]

It all has to do with disabling the various Screens & Windows from showing, or reflecting what would show IF YOU DID MANUALLY what your Macro is doing.

 

[RickXL]

Just to amplify what Jim said ...

Whenever you send data from VBA to the worksheet, or vice versa, then there is a slight delay. I think of it as loading a van and sending the data from one factory to another. Loading the van and driving from one place to the other is not useful time.


"ScreenUpdating = True" is the default situation. When you run a macro that updates the worksheet, it will hesitate slightly each time the data is copied from VBA to the worksheet. So if you, say, calculate 10,000 cells then the macro will pause 10,000 times while it brings the worksheet up to date.

On the other hand, if you use "ScreenUpdating = False" then the macro runs right to the end before it updates the worksheet. It moves the same amount of data but it gets it all into one van so it only needs to make one journey.

When a macro finishes, it automatically ensures that the worksheet is updated. I suspect (I have no proof) that if you switch ScreenUpdating back on at the end it repeats some checking and that takes a small amount of time. Consequently, I no longer switch it back on at the end because it is already effectively on and it saves a small amount of time.

If you need the worksheet to be up to date at any intermediate point then you need to switch it back on.


Regards,