VBA Copy Paste to each new worksheet with formatting and formulas

Dear Forum,

I have the below code running in my project that transfers data from a worksheet called "Update Quality Check Data" to other weeksheets based on user names by 1 of 2 ways, either:

• By seeing the user name of the worksheet already exists and just copying the relevant data; or,
• By creating a new worksheet with the user name as the ws name and copying the data from the data shee

What I would like to add would be when a new user sheet is created the format and forumlas from the first usersheet are copied into the new sheets and each additional user sheet that is created.

I have seen many threads to copy paste and the arguements between clipboard and pastespecial but now I am rather confused and not sure how to do this for sheets that do not currently exist. Could some please help me?

I have attached a sample sheet, so I would just want the formulas and borders copied from the worksheet called Bob forum.ozgrid.com/index.php?attachment/69687/to each new sheet that is created.

Public Sub transfer()
     
     
    Dim ws As Worksheet, wsName As Worksheet
    Dim lRow As Long, lPaste As Long
    Dim sName As String
     
     
    Set ws = Worksheets("Update Quality Check Data")
     
     
    With ws
        For lRow = 2 To .Cells(Rows.Count, 1).End(xlUp).Row
            sName = .Cells(lRow, 2)
            On Error GoTo NoSheettFound
Jumper:
            Set wsName = Worksheets(sName)
            On Error GoTo 0
            lPaste = wsName.Cells(Rows.Count, 3).End(xlUp).Row + 1
            .Cells(lRow, 1).Copy Destination:=wsName.Cells(lPaste, 3)
            .Cells(lRow, 3).Copy Destination:=wsName.Cells(lPaste, 4)
        Next lRow
    End With
     
     
    Exit Sub
     
     
NoSheettFound:
    Set wsName = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    wsName.Name = sName
    ws.Select
    GoTo Jumper
End Sub

Re: VBA Copy Paste to each new worksheet with formatting and formulas

Could someone please advise if this might be the correct fix to my question?, I only need to know how to change "Bob" in the code below so that the formmating would copy to each new sheet with a different name. Does VBA work with wild cards like "*" for example?:

range("B2:B5002").Copy Destination:=Sheets("Bob").Cells(startrow, 2)