Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a New Tab-and Naming it from a Cell Name
All I am trying to add a new worksheet and name it what ever the cell value
is from my main sheet. The name will always be a number plus one. Example in my "Inputsheet' in column BA row 9 is a number 1701, so with what I am trying to do is ref BA9 and copy it to BA10 plus one (cell BA10 then should say 1702). I have attached my code which is not working (got part of this code from Ron de Bruin web site): Private Sub CreateNewSheet1_Click() Dim LastRow As Long Dim rng As Range With ActiveSheet LastRow = .Cells(.Rows.Count, "BA").End(xlUp).Row With rng.Parent .Select .Range(LastRow).Select End With LastRow = ActiveCell.FormulaR1C1 = "=R[-1]C+1" Selection.Copy ActiveSheet.Range(LastRow).Select ActiveSheet.Paste End With Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = LastRow ActiveSheet.Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a New Tab-and Naming it from a Cell Name
Hi
Look at this: Private Sub CreateNewSheet1_Click() Dim LastRow As Long LastRow = Cells(Rows.Count, "BA").End(xlUp).Row Range("BA" & LastRow + 1) = Range("BA" & LastRow).Value + 1 NewShName = Range("BA" & LastRow + 1).Value Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = NewShName End Sub Regards, Per "caldog" skrev i meddelelsen ... All I am trying to add a new worksheet and name it what ever the cell value is from my main sheet. The name will always be a number plus one. Example in my "Inputsheet' in column BA row 9 is a number 1701, so with what I am trying to do is ref BA9 and copy it to BA10 plus one (cell BA10 then should say 1702). I have attached my code which is not working (got part of this code from Ron de Bruin web site): Private Sub CreateNewSheet1_Click() Dim LastRow As Long Dim rng As Range With ActiveSheet LastRow = .Cells(.Rows.Count, "BA").End(xlUp).Row With rng.Parent .Select .Range(LastRow).Select End With LastRow = ActiveCell.FormulaR1C1 = "=R[-1]C+1" Selection.Copy ActiveSheet.Range(LastRow).Select ActiveSheet.Paste End With Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = LastRow ActiveSheet.Range("A1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding a New Tab-and Naming it from a Cell Name
Worked great Thanks "Per Jessen" wrote: Hi Look at this: Private Sub CreateNewSheet1_Click() Dim LastRow As Long LastRow = Cells(Rows.Count, "BA").End(xlUp).Row Range("BA" & LastRow + 1) = Range("BA" & LastRow).Value + 1 NewShName = Range("BA" & LastRow + 1).Value Sheets.Add After:=Sheets(Sheets.Count) ActiveSheet.Name = NewShName End Sub Regards, Per "caldog" skrev i meddelelsen ... All I am trying to add a new worksheet and name it what ever the cell value is from my main sheet. The name will always be a number plus one. Example in my "Inputsheet' in column BA row 9 is a number 1701, so with what I am trying to do is ref BA9 and copy it to BA10 plus one (cell BA10 then should say 1702). I have attached my code which is not working (got part of this code from Ron de Bruin web site): Private Sub CreateNewSheet1_Click() Dim LastRow As Long Dim rng As Range With ActiveSheet LastRow = .Cells(.Rows.Count, "BA").End(xlUp).Row With rng.Parent .Select .Range(LastRow).Select End With LastRow = ActiveCell.FormulaR1C1 = "=R[-1]C+1" Selection.Copy ActiveSheet.Range(LastRow).Select ActiveSheet.Paste End With Sheets.Add After:=Sheets(Sheets.Count) Sheets(Sheets.Count).Name = LastRow ActiveSheet.Range("A1").Select End Sub . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding, naming Worksheets sequentially | Excel Programming | |||
Making for adding and naming a new sheet | Excel Discussion (Misc queries) | |||
Adding and naming new worksheets | Excel Programming | |||
Adding and Naming Multiple Worksheets | Excel Worksheet Functions | |||
Naming and Adding Worksheets | Excel Programming |