Create a new tab, copy data from another tab, and paste the data (with formatting) in the new tab

user_18101125 0
Viewed 106

I have a tab called "Overview" where it asks me for the name of a project.
Once I write the project name, I want a macro to grab the project name, create a new tab, and change the name of the new tab to the project name.
Afterwards, I would like for the macro to go to another tab (let's call this tab "Template"), copy the whole worksheet, and paste the data into the newly created tab. I would like the formatting from the "Template" tab to flow to the new tab as well.

Sub AddNewTab()
 
Dim ws As Worksheet
Set ws = Worksheets("I DON'T KNOW WHAT TO WRITE HERE")
    
Rem Set working worksheets
Set WshSrc = ThisWorkbook.Worksheets("Source")
Set WshTrg = ThisWorkbook.Worksheets("Target")
    
'Create new tab based on project name
Sheets.Add(After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)).Name = Range("I2")

'Copy data from Template tab
Worksheets("Template").Range("A1:H12").Copy

Worksheets("I DON'T KNOW WHAT TO WRITE HERE").Range("A1").PasteSpecial

'hide gridlines in a worksheet with the project name
ws.Activate
ActiveWindow.DisplayGridlines = False

End Sub

Cell I2 in the "Overview" page is where the project name will be written.

vba excel
2 Answers
Option Explicit

Sub AddNewTab()
    Dim n As Long
    With ThisWorkbook
        n = .Sheets.Count
        .Sheets("Template").Copy after:=.Sheets(n)
        .Sheets(n + 1).Name = .Sheets("Overview").Range("I2")
    End With
End Sub
user_127045930

Copy to a New Worksheet

Option Explicit

Sub AddNewTab()
 
    Dim wb As Workbook: Set wb = ThisWorkbook
    
    ' Lookup
    Dim lws As Worksheet: Set lws = wb.Worksheets("Overview")
    Dim lCell As Range: Set lCell = lws.Range("I2")
    
    ' Source
    Dim sws As Worksheet: Set sws = wb.Worksheets("Template")
    Dim srg As Range: Set srg = sws.Range("A1:H12")
    
    ' Write the destination name to a variable.
    Dim dName As String: dName = CStr(lCell.Value)
    
    ' Check if a sheet with the destination name already exists.
    Dim dsh As Object ' also cover charts
    On Error Resume Next
        Set dsh = wb.Sheets(dName)
    On Error GoTo 0
    If Not dsh Is Nothing Then ' sheet already exists
        MsgBox "A sheet with the name '" & dName & "' already exists.", _
            vbCritical
        Exit Sub
    'Else ' sheet doesn't exist; do nothing  
    End If
    
    ' Add and rename the destination worksheet by using a variable
    ' (it automatically becomes the 'ActiveSheet').
    Dim dws As Worksheet
    Set dws = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
    dws.Name = dName
    
    ' Copy the range.
    srg.Copy dws.Range("A1")
    
    ActiveWindow.DisplayGridlines = False

    MsgBox "New worksheet added.", vbInformation

End Sub
user_98140690
Related