Trouble Pasteing into New recently named worksheet

[serraostudent1]

Excel 2013 VBA Assistance needed.

New user to VBA so be patient. I have written code (with some help from videos) to look at a given name in Worksheet POs. Count the number of existing worksheets. If the name is not existing, create a new sheet with the new name, then return to Worksheet POs. I must return to Worksheet POs so that I can select a new name from the drop down list and run the macro again.

Now here is where the trouble lies. Once I have added all new worksheets and return to POs I want to copy and paste the header row into each new worksheet. Problem, how do I tell Excel VBA terms, use the value located in the specified cell (AY2), select that named worksheet, then paste the header row?

Here is what I have so far...

Sub TesttSheet()
'
' TesttSheet Macro
'


'






Sheet_name_to_create = Sheets("POs").Range("AF2").Value

VendorName = Sheets("POs").Range("AF2").Value



For rep = 1 To (Worksheets.Count)

If LCase(Sheets(rep).Name) = LCase(Sheet_name_to_create) Then
MsgBox "This Sheet already exists Roxanne!"
Exit Sub
End If
Next

Sheets.Add After:=Sheets(Sheets.Count)
Sheets(ActiveSheet.Name).Name = Sheet_name_to_create

Sheets(ActiveSheet.Name).Select


Sheets("POs").Select
Sheets("POs").Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Sheets(ActiveSheet.Name).Name = VendorName

The bolded item above is not working. I need help, desperately, with fixing it. Your assistance is appreciated.

 

[Michael M]

Sorry, the rep variable was dimmed incorrectly

Sub TesttSheet()
Dim rep As Integer
Sheet_name_to_create = Sheets("POs").Range("AF2").Value
VendorName = Sheets("POs").Range("AF2").Value
For rep = 1 To (Worksheets.Count)
If LCase(Sheets(rep).Name) = LCase(Sheet_name_to_create) Then
MsgBox "This Sheet already exists Roxanne!"
Exit Sub
End If
Next rep
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = VendorName
Sheets("POs").Rows("1:1").Copy Sheets(VendorName).Range("A1")
End Sub

 

[My Aswer Is This]

I'm waiting to see if Michaels script works for you.

 

[serraostudent1]

Just returned after a long day! Michael Thanks That did it perfectly. @My Answer Is This, I extend my thanks to you as well for your efforts. To my other helpers I appreciate your time very much and trust that all your future endeavors are successful! Thanks to this forum, novice users have someone to turn to.
Again, Michael, you Rock!

 

[My Aswer Is This]

I was trying to get the script to run through all the sheet names in your drop down list at one swoop but then stopped when I saw you were happy with Michaels plan.
I was not able to get my ideal to work but I'm sure I'm missing something. I have done double loops before but I have not figured out how to do this one.
Michaels plan works well but must be done one at a time.
If your Happy I'm Happy.
I will probable keep working at it. I do not like defeat.

 

[Michael M]

A little less junk !!!

Option Compare Text
Sub TesttSheet()
Dim ws As Worksheet, vendor_name As String
VendorName = Sheets("POs").Range("AF2").Value
    For Each ws In Worksheets
        If ws.Name = VendorName Then
            MsgBox "This Sheet already exists Roxanne!"
            Exit Sub
        End If
    Next ws
Sheets.Add(After:=Sheets(Sheets.Count)).Name = VendorName
Sheets("POs").Rows("1:1").Copy Sheets(VendorName).Range("A1")
End Sub

 

[My Aswer Is This]

Sort of like me. I'm always looking at my scripts later to see how I could have made them better.

A little less junk !!!

Option Compare Text
Sub TesttSheet()
Dim ws As Worksheet, vendor_name As String
VendorName = Sheets("POs").Range("AF2").Value
    For Each ws In Worksheets
        If ws.Name = VendorName Then
            MsgBox "This Sheet already exists Roxanne!"
            Exit Sub
        End If
    Next ws
Sheets.Add(After:=Sheets(Sheets.Count)).Name = VendorName
Sheets("POs").Rows("1:1").Copy Sheets(VendorName).Range("A1")
End Sub

 

[My Aswer Is This]

Assuming what you said in post #5 is still true. You said
The list is on the next worksheet within the same workbook and its name is "List". Starts in column A1

You can run the below script and it will look through your whole list of names all at one time.
I have taken Michaels script and modified it.

Option Compare Text
Sub Make_New_Sheets()
Dim ws As Worksheet, vendor_name As String
Dim Lastrow As Long
Lastrow = Sheets("List").Cells(Rows.Count, "A").End(xlUp).Row

    For i = 1 To Lastrow
        VendorName = Sheets("List").Cells(i, 1).Value
            For Each ws In Worksheets
                If ws.Name = VendorName Then
                    GoTo J
                End If
            Next ws
            Sheets.Add(After:=Sheets(Sheets.Count)).Name = VendorName
            Sheets("List").Rows("1:1").Copy Sheets(VendorName).Range("A1")
J:
    Next
End Sub