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
wrote:
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
--
Dave Peterson