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