How can I copy sheet, fill in cell from list and rename worksheet?

[radlee]

Ok...I need to know how to do the following.

I have a worksheet that I need to copy, fill in cell I6 with a value
from a worksheet named Employees (first cell is C79) and then rename
the newly created sheet with the value that is in cell I6 of that newly
created worksheet. I need this to repeat a copy until it it gets
through cell C108 of the Employees worksheet.

Thank you oh wise and mighty Excel gurus.

Lee

 

[Zoddy]

Try this

Sub CopyInfo()
Dim shtnme As String
Sheets("Employees").Select
Range("C78").Select
For M = 1 To 30
ActiveCell.Offset(1, 0).Select
gg = ActiveCell.Value
Sheets.Add After:=ActiveSheet
ActiveSheet.Name = "Temp"
[i6] = gg
ActiveSheet.Name = [i6]
Sheets("Employees").Select
Next
End Sub

You will still have any existing sheets at the end if there were any to the right of "Employees"

regards

Zoddy

 

[Dave Peterson]

Maybe something like:

Option Explicit
Sub testme()

Dim CopyWks As Worksheet
Dim EmpWks As Worksheet
Dim NewWks As Worksheet

Dim RngNames As Range
Dim myCell As Range

Set CopyWks = Worksheets("sheettocopy")
Set EmpWks = Worksheets("Employees")

Set RngNames = EmpWks.Range("C79:c108")

For Each myCell In RngNames.Cells
CopyWks.Copy _
after:=Sheets(Sheets.Count)
Set NewWks = ActiveSheet
With NewWks
.Range("i6").Value = myCell.Value
On Error Resume Next
.Name = myCell.Value
If Err.Number <> 0 Then
MsgBox "Please fix sheet: " & .Name & " manually!"
Err.Clear
End If
On Error GoTo 0
End With
Next myCell

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[radlee]

This works great.. but being a rookie, I stated something incorrectly.
The worksheet should be named for what is in I5. I6 is what I am
populating from C79:c108, but the worksheet name will come from I5 of
the newly created worksheet. THANKS FOR YOUR HELP!

 

[Dave Peterson]

If I5 is a constant, then you'll have trouble. If I5 is a formula, then this
should work:

Change:
..Name = myCell.Value
to
..Name = .range("i5").Value

 

[radlee]

THANK YOU! That worked flawlessly!

lee

If I5 is a constant, then you'll have trouble. If I5 is a formula, then this
should work:

Change:
.Name = myCell.Value
to
.Name = .range("i5").Value