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

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

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

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



wrote:

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

--

Dave Peterson

THANK YOU! That worked flawlessly!

lee


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



wrote:

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

--

Dave Peterson