Need Help With Macro to set a value in new sheets

[KennyD]

Hey everybody.

I am using a variant of Dave Petersen's macro code to create a new sheet and
rename that sheet based on a list of values in Sheet_2. Here is what I have
so far:

Sub CreateNameSheets()
' by Dave Peterson
' List sheetnames required in col C in a sheet: Sheet_2
' Sub will copy sheets based on the sheet named as: Sheet1
' and name the sheets accordingly

Dim TemplateWks As Worksheet
Dim ListWks As Worksheet
Dim ListRng As Range
Dim myCell As Range
Dim ListRng2 As Range
Dim myCell2 As Range

Set TemplateWks = Worksheets("Sheet1")
Set ListWks = Worksheets("Sheet_2")
With ListWks
Set ListRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp))
Set ListRng2 = .Range("B2", .Cells(.Rows.Count, "B").End(x1Up))
End With

For Each myCell In ListRng.Cells
TemplateWks.Copy After:=Worksheets(Worksheets.Count)
On Error Resume Next
With ActiveSheet
..Name = myCell.Value
..Range("B2").Value = myCell.Value
End With
If Err.Number = 0 Then
MsgBox "Please fix: " & ActiveSheet.Name
Err.Clear
End If
On Error GoTo 0
Next myCell

End Sub

I need to assign Cell C3 on the newly copied sheet the value that is found
in the list on sheet "Sheet_2", column B, starting with B2. And I am stuck.
I don't know how to make it happen. I can copy "Sheet1" and rename the copy
with out a problem. I can even change the value of Cell B2 to match the new
sheet name of the copy. But I also need to change Cell C3 of the copy to
match an ID# that is found in Column B of "Sheet_2".

Help please. Thank you.

 

[Luke M]

Change this section:
..Range("B2").Value = myCell.Value


To this:
..Range("B2").Value = myCell.Value
..Range("C3").Value = myCell.Offset(0,-1).Value

This code says to look one column to the left of myCell (which is back on
Sheet_2).

 

[KennyD]

Worked like a charm. Now on to my next macro. Creating a summary sheet based
on the new sheets that were created. Any thoughts on this? Thanks.