Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With Macro to set a value in new sheets
Hey everybody.
I am using a variant of Dave Petersen's macro code to create a new sheet and rename that sheet based on a list of values in Sheet_2. Here is what I have so far: Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col C in a sheet: Sheet_2 ' Sub will copy sheets based on the sheet named as: Sheet1 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim ListRng2 As Range Dim myCell2 As Range Set TemplateWks = Worksheets("Sheet1") Set ListWks = Worksheets("Sheet_2") With ListWks Set ListRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp)) Set ListRng2 = .Range("B2", .Cells(.Rows.Count, "B").End(x1Up)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next With ActiveSheet ..Name = myCell.Value ..Range("B2").Value = myCell.Value End With If Err.Number = 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub I need to assign Cell C3 on the newly copied sheet the value that is found in the list on sheet "Sheet_2", column B, starting with B2. And I am stuck. I don't know how to make it happen. I can copy "Sheet1" and rename the copy with out a problem. I can even change the value of Cell B2 to match the new sheet name of the copy. But I also need to change Cell C3 of the copy to match an ID# that is found in Column B of "Sheet_2". Help please. Thank you. :) -- Nothing in life is ever easy - just get used to that fact. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With Macro to set a value in new sheets
Change this section:
..Range("B2").Value = myCell.Value To this: ..Range("B2").Value = myCell.Value ..Range("C3").Value = myCell.Offset(0,-1).Value This code says to look one column to the left of myCell (which is back on Sheet_2). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "KennyD" wrote: Hey everybody. I am using a variant of Dave Petersen's macro code to create a new sheet and rename that sheet based on a list of values in Sheet_2. Here is what I have so far: Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col C in a sheet: Sheet_2 ' Sub will copy sheets based on the sheet named as: Sheet1 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim ListRng2 As Range Dim myCell2 As Range Set TemplateWks = Worksheets("Sheet1") Set ListWks = Worksheets("Sheet_2") With ListWks Set ListRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp)) Set ListRng2 = .Range("B2", .Cells(.Rows.Count, "B").End(x1Up)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next With ActiveSheet .Name = myCell.Value .Range("B2").Value = myCell.Value End With If Err.Number = 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub I need to assign Cell C3 on the newly copied sheet the value that is found in the list on sheet "Sheet_2", column B, starting with B2. And I am stuck. I don't know how to make it happen. I can copy "Sheet1" and rename the copy with out a problem. I can even change the value of Cell B2 to match the new sheet name of the copy. But I also need to change Cell C3 of the copy to match an ID# that is found in Column B of "Sheet_2". Help please. Thank you. :) -- Nothing in life is ever easy - just get used to that fact. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Need Help With Macro to set a value in new sheets
Worked like a charm. Now on to my next macro. Creating a summary sheet based
on the new sheets that were created. Any thoughts on this? Thanks. -- Nothing in life is ever easy - just get used to that fact. "Luke M" wrote: Change this section: .Range("B2").Value = myCell.Value To this: .Range("B2").Value = myCell.Value .Range("C3").Value = myCell.Offset(0,-1).Value This code says to look one column to the left of myCell (which is back on Sheet_2). -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "KennyD" wrote: Hey everybody. I am using a variant of Dave Petersen's macro code to create a new sheet and rename that sheet based on a list of values in Sheet_2. Here is what I have so far: Sub CreateNameSheets() ' by Dave Peterson ' List sheetnames required in col C in a sheet: Sheet_2 ' Sub will copy sheets based on the sheet named as: Sheet1 ' and name the sheets accordingly Dim TemplateWks As Worksheet Dim ListWks As Worksheet Dim ListRng As Range Dim myCell As Range Dim ListRng2 As Range Dim myCell2 As Range Set TemplateWks = Worksheets("Sheet1") Set ListWks = Worksheets("Sheet_2") With ListWks Set ListRng = .Range("C2", .Cells(.Rows.Count, "C").End(xlUp)) Set ListRng2 = .Range("B2", .Cells(.Rows.Count, "B").End(x1Up)) End With For Each myCell In ListRng.Cells TemplateWks.Copy After:=Worksheets(Worksheets.Count) On Error Resume Next With ActiveSheet .Name = myCell.Value .Range("B2").Value = myCell.Value End With If Err.Number = 0 Then MsgBox "Please fix: " & ActiveSheet.Name Err.Clear End If On Error GoTo 0 Next myCell End Sub I need to assign Cell C3 on the newly copied sheet the value that is found in the list on sheet "Sheet_2", column B, starting with B2. And I am stuck. I don't know how to make it happen. I can copy "Sheet1" and rename the copy with out a problem. I can even change the value of Cell B2 to match the new sheet name of the copy. But I also need to change Cell C3 of the copy to match an ID# that is found in Column B of "Sheet_2". Help please. Thank you. :) -- Nothing in life is ever easy - just get used to that fact. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using the same macro on different sheets | Excel Worksheet Functions | |||
macro for same value in 2 different sheets | Excel Discussion (Misc queries) | |||
Add sheets using macro | Excel Worksheet Functions | |||
macro for new sheets | Excel Worksheet Functions | |||
macro/new sheets | Excel Discussion (Misc queries) |