How can I copy sheet, fill in cell from list and rename worksheet?
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 |
How can I copy sheet, fill in cell from list and rename worksheet?
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 |
How can I copy sheet, fill in cell from list and rename worksheet?
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 |
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 |
All times are GMT +1. The time now is 12:50 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com