Create New Sheet and Rename

Hello,

I have been trying to adapt a macro I found on excel macro forums, and I keep getting the same error. Basically I have 3 sheets. 1- Main, 2- Input, 3 - Receipt.

1 - Main, B Column has the values I need to create new sheets. So one sheet per row.

Ideally the macro creates a copy of 2- Input and renames it "Input - B2", creates a copy of 3 - Receipt and renames it "Receipt - B2". and so on for each row which has value entered in column B. Except B1(unnecessary headers)

This is the code I have trouble with:

Sub SampleAddInput()
Dim ws As Worksheet, tmpSht As Worksheet
Dim LastRow As Long, i As Long, j As Long

Set ws = Sheets("Main")

With ws
LastRow = .Range("B" & .Rows.Count).End(xlUp).Row

If LastRow < 2 Then Exit Sub

For i = 2 To LastRow
If DoesSheetExist(.Range("B" & i).Value) Then
Set tmpSht = Sheets(.Range("B" & i).Value)
Else
Sheets("Input").Copy After:=Sheets("Input")
Set tmpSht = ActiveSheet
tmpSht.Name = .Range("B" & i).Value


End If
Next
End With
End Sub

I can't figure out how to add the text "Input -". I've tried & "Input" , & ("Input") .Text, etc.

Any help would be appreciated.

Thank you
Andy

Please Login or Register  to view this content.

Thank you! :D