Adding a New Tab-and Naming it from a Cell Name

  • Thread starter Thread starter caldog
  • Start date Start date
C

caldog

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
 
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
 
Worked great Thanks
Per Jessen said:
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 said:
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
Click to expand...

.
Click to expand...
 
Back
Top