I found the following code to be useful when I need to copy a worksheet from and to whithin a workbook. However, I cannot find a way to name newly copied sheets as desired using the following code. All works except the line below sht.Name = ml

This returns the error "Variable or with block not set."

If I remove the line, all sheets are created. I figure, If I can create then, I should be able to rename them.

THE CODE:


Sub CopyX_click()


Dim Sht As Worksheet, i As Integer
Dim iCount As Integer
iCount = Worksheets.Count
Application.ScreenUpdating = False
ml = ("act")

        For i = 1 To 3
            'Set Sht = Sheets("Sheet3")
            Worksheets("sheet3").Copy After:=Sheets(iCount)
            iCount = iCount + 1
            Sht.Name = ml
        Next

        Set Sht = Nothing
        Application.ScreenUpdating = True

End Sub

Is there anyone able in answering this one?

Help!

Of course is there anyone to help you :D


this code should run:

Dim Sht As Worksheet, i As Integer
Dim iCount As Integer
iCount = Worksheets.Count
Application.ScreenUpdating = False
ml = ("act")

        For i = 1 To 3
            'Set Sht = Sheets("Sheet3")
            Worksheets("sheet3").Copy After:=Sheets(iCount)
            iCount = iCount + 1
            ActiveSheet.Name = ml
        Next

        Set Sht = Nothing
        Application.ScreenUpdating = True

regards
crack

I hope this is good english, because this tongue is not my native language.
Please improve me if my syntax is wrong.
Thanks

Sub WatchMeWhipNayNay()
Dim WB As Workbook, WS As Worksheet

Set WB = Application.ActiveWorkbook
Set WS = WB.ActiveSheet

'Get Last Sheet Position
LastSheetCount = WB.Worksheets.Count

'Add New Sheet after Last Sheet Position
WB.Worksheets.Add After:=WB.Sheets(LastSheetCount)

' Get the Last Sheet, factoring the new sheet
ExpandedSheetsCount = WB.Worksheets.Count

'Get the Current Name of the Last Sheet
NewSheetName = WB.Sheets(ExpandedSheetsCount).Name

'Change the Name of the Last Sheet to the desired Name.
WB.Sheets(ExpandedSheetsCount).Name = "Watch Me Whip, Watch me Nay Nay"
'Or WB.Worksheets(NewSheetName).Name = "Watch Me Whip, Watch me Nay Nay"

End Sub

@stepdev & @Crack - Your code generates an error for the simple reason below.

Each iteration of the for loop creates a sheet then gives it a name, ml = "act"
This will generate an error after the 1st iteration as a sheet named "act" already exists, after the 1st iteration.

The only way out is to use this : ActiveSheet.Name = ml + iCount
This way each name is unique.

If you plan to use your code elsewhere, I advise you don't hard-code the upper limit of your sheets to 3, in fact I'd completely do away with the for-loop in favor of the ForEach-loop over the sheets collection.

Note: I didn't verify if you 1st need to cast the iCount variable into string before adding to to ml string variable but I do not think it is necessary.