VBA to sheets

[rspalding]

I'm using the following code but get a runtime 424 error. Also, I would like to the sheet name in c17 to last cell

Private Sub CommandButton1_Click()
lastcell = ThisWorkbook.Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
For i = 17 To lastcell
With YhisWorkbook
newname = ThisWorkbook.Worksheets("Master").Cells(i, 1).Value
.Sheets.Add after:=.Sheets(.Sheets.Count)
ActiveSheet.Name = newname
End With
Next
ThisWorkbook.Worksheets("Master").Activate
ThisWorkbook.Worksheets("Master").Cells(1, 1).Select
End Sub

Thanks for the help...

 

[Michael M]

One error here for starters....

With YhisWorkbook

 

[rspalding]

Michael M,

Thanks for catching the type-o. I looked multiple times and didn't see it. How can I tell the code to look at the corresponding cell in C column and tell it to copy the sheet named in "C"? So A will have the new sheet name but C will tell it which sheet to copy.

Thanks,

 

[rlv01]

If you put this declaration

Option Explicit

At the top of your code module ala:

Option Explicit

Private Sub CommandButton1_Click()
    lastcell = ThisWorkbook.Worksheets("Master").Cells(Rows.Count, 1).End(xlUp).Row
    For i = 17 To lastcell
        With YhisWorkbook
            newname = ThisWorkbook.Worksheets("Master").Cells(i, 1).Value
            .Sheets.Add after:=.Sheets(.Sheets.Count)
            ActiveSheet.Name = newname
        End With
    Next
    ThisWorkbook.Worksheets("Master").Activate
    ThisWorkbook.Worksheets("Master").Cells(1, 1).Select
End Sub

Then the compiler will help you catch errors like "YhisWorkbook" and by forcing you to declare all your variables, it will make it easier for us to help you when you post questions about your code.

BTW, I recommend you use code tags for clarity when posting code