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

  • Thread starter Thread starter radlee
  • Start date Start date
R

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
 
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
 
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
 
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!
 
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
 
THANK YOU! That worked flawlessly!

lee


Dave said:
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
Click to expand...
 
Back
Top