Create new sheet named from the copied sheet

[makis1023]

Hello all,

I need to create a new worksheet (new file) based on a sheet.
I do that using the below code

Sheets("NewSheet").Copy

That it will create a new file with the sheet's contents.
What I need is to rename the newly created file based on a sheet name. I think I have to do that before create the new sheet.
Is this possible?

Thank you

 

[Marc L]

Hello, as it can be done after so for starters :​

  Const S = "NewSheet"
    Dim N$
        N = ActiveWorkbook.Path & "\" & S
        Sheets(S).Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs N, 51
        Application.DisplayAlerts = True

 

[makis1023]

Thank you for your answer. Worked fine but I think that I havent explained it right.
You see the full code is

Sub BuildTemplate()
  Dim i As Long, j As Long, nr As Long
  Dim cell As Variant, f As Range
  Dim Descript As String
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  Sheets.Add(After:=Sheets("Intelligent Lighting m")).Name = "NewSheet"
  Worksheets("NewSheet").Range("A1").Value = "Description"
  Worksheets("NewSheet").Range("B1").Value = "Quantity"
  nr = 1
  Sheets("NewSheet").Range("A2:B150").ClearContents
    For Each cell In Worksheets("Intelligent Lighting m").Range("D2:D65", "D78:D92") 'Loop through all cells in the multirange
      If cell > 0 Then                              'See if anything entered in pieces
        Descript = cell.Offset(0, -1)               'get description from column C
        With Sheets("NewSheet")
          Set f = .Range("A2:A150").Find(Descript, , xlValues, xlWhole)
          If Not f Is Nothing Then
            nr = f.Row
          Else
            nr = nr + 1
            If nr > 150 Then
              MsgBox "Rows are full"
              Exit Sub
            End If
          End If
          .Cells(nr, "A") = Descript                'Populate values in sheet
          .Cells(nr, "B") = cell                    'get pieces from column E
        End With
      End If
    Next cell
  Application.ScreenUpdating = False
  Const S = "NewSheet"
    Dim N$
        N = ActiveWorkbook.Path & "\" & S
        Sheets(S).Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs N, 51
        Application.DisplayAlerts = True
End Sub

I need to be renamed like the sheet I am retreiving the data (Intelligent Lighting m), not the newly created sheet.

 

[Marc L]

Mod to apply within your code :​

  Const S = "Intelligent Lighting m"
    Dim N$
        N = ActiveWorkbook.Path & "\" & S
    

        Sheets.Add(After:=Sheets(S)).Name = "NewSheet"

    
        ActiveSheet.Copy
        Application.DisplayAlerts = False
        ActiveWorkbook.SaveAs N, 51
        Application.DisplayAlerts = True

 

[makis1023]

Yes , I understand that I can do it by hand, is it possible to be automated?
To check that name and retrieve it?
Thank you!