Need Help With Macro to set a value in new sheets



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
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


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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question