Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
stumped!!
I have this code that adds sheets to my workbook i would like to add each
sheet name that is added to a sheet in my workbook. sheet name data column y starting at row 2 Private Sub CommandButton3_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet Set ws = wksData Set LastCell = ws.Cells(Rows.Count, "O").End(xlUp) Set Rng = ws.Range("O2", LastCell) For Each cell In Rng If Not cell.Value = "" Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then wksMaster.Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Visible = True ActiveSheet.Name = cell.Value & cell.Offset(0, 1).Value Range("I3").Value = cell.Offset(0, 2).Value End If End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
stumped!!
Haven't delved into your code, but this should work. Below your
ActiveSheet.Name line, place something like this: Dim lRow As Long With Sheets("data") lRow = .Cells(.Rows.Count, "y").End(xlUp).Row .Cells(lRow + 1, "y").Value = ActiveSheet.Name End With jhyatt wrote: I have this code that adds sheets to my workbook i would like to add each sheet name that is added to a sheet in my workbook. sheet name data column y starting at row 2 Private Sub CommandButton3_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet Set ws = wksData Set LastCell = ws.Cells(Rows.Count, "O").End(xlUp) Set Rng = ws.Range("O2", LastCell) For Each cell In Rng If Not cell.Value = "" Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then wksMaster.Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Visible = True ActiveSheet.Name = cell.Value & cell.Offset(0, 1).Value Range("I3").Value = cell.Offset(0, 2).Value End If End If Next End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
stumped!!
Thank you Jw it worked great
"JW" wrote: Haven't delved into your code, but this should work. Below your ActiveSheet.Name line, place something like this: Dim lRow As Long With Sheets("data") lRow = .Cells(.Rows.Count, "y").End(xlUp).Row .Cells(lRow + 1, "y").Value = ActiveSheet.Name End With jhyatt wrote: I have this code that adds sheets to my workbook i would like to add each sheet name that is added to a sheet in my workbook. sheet name data column y starting at row 2 Private Sub CommandButton3_Click() Dim LastCell As Range, Rng As Range, cell As Range Dim ws As Worksheet Set ws = wksData Set LastCell = ws.Cells(Rows.Count, "O").End(xlUp) Set Rng = ws.Range("O2", LastCell) For Each cell In Rng If Not cell.Value = "" Then Set ws = Nothing On Error Resume Next Set ws = Worksheets(cell.Value) On Error GoTo 0 If ws Is Nothing Then wksMaster.Copy after:=Worksheets(Worksheets.Count) ActiveSheet.Visible = True ActiveSheet.Name = cell.Value & cell.Offset(0, 1).Value Range("I3").Value = cell.Offset(0, 2).Value End If End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
This has me stumped | Excel Discussion (Misc queries) | |||
Still stumped | Excel Worksheet Functions | |||
stumped | Excel Worksheet Functions | |||
Still Stumped | Excel Programming | |||
stumped ?? | Excel Worksheet Functions |