Thread
:
How can I copy sheet, fill in cell from list and rename worksheet?
View Single Post
#
5
Posted to microsoft.public.excel.programming
[email protected]
external usenet poster
Posts: 5
How can I copy sheet, fill in cell from list and rename worksheet?
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
Reply With Quote
[email protected]
View Public Profile
Find all posts by
[email protected]