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.

 

[My Aswer Is This]

I may be able to help you if you're willing to answer some questions.
If I understand you correctly you have:
1. A list of sheet names. Where is this list of sheet names?
2. You want to search through your workbook and see if that sheet name already exist.
3. I f the sheet name does not already exist. You want a new blank sheet created and given this name.
4. Then you want to the script to copy row (1) of Sheet named ("POs") and paste this row into row one of all sheets.
We can do all of this at one time no need to stop each time and select a name from the drop down list the script will do this automatically
If this correct in what you want.

 

[serraostudent1]

I may be able to help you if you're willing to answer some questions.
If I understand you correctly you have:
1. A list of sheet names. Where is this list of sheet names?
2. You want to search through your workbook and see if that sheet name already exist.
3. I f the sheet name does not already exist. You want a new blank sheet created and given this name.
4. Then you want to the script to copy row (1) of Sheet named ("POs") and paste this row into row one of all sheets.
We can do all of this at one time no need to stop each time and select a name from the drop down list the script will do this automatically
If this correct in what you want.

Yes this is exactly it. The list is located on a separate worksheet. I was using a drop down from it. I was stopping because the names may be too long and the renaming step gets a blank tab or numbered tab created as a result. Any ideas around this?

 

[My Aswer Is This]

I asked the question:Where is this list of sheet names?

Your answer was:
"The list is located on a separate worksheet"

That is not being specific.
I need to know the sheet name and exactly where on the sheet.
If you said come over for dinner tonight.
I asked where do you live and your answer was U.S.A that would not help me very much.

 

[serraostudent1]

The list is on the next worksheet within the same workbook and its name is "List". Starts in column A1

If I said come over for dinner tonight, would you? lol you cracked me up with that analogy. I appreciate you!

 

[My Aswer Is This]

I will get back with you. It may be several hour. Football just started.

 

[Michael M]

Does this do what you want ??
You also should get in the habit of Dimming your variables !
AND
In future please use code tags when posting code......see my sig block !

Sub TesttSheet()
dim rep as worksheet
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)
Sheets("POs").Rows("1:1").Copy Sheets(VendorName).Range("A1")
End Sub

 

[serraostudent1]

Thanks Mike I appreciate what you suggested above. I used it and received a Type Mismatch error. Did I do something incorrectly?

 

[serraostudent1]

Please help if you can.

 

[My Aswer Is This]

I have not been able to figure this out as yet.