I have the below 2 sets of code and I am trying to combine them into 1 (as a newbie not sure if this is doable or if there is a better way to do this?). The outcome should be that:
A new sheet is created when the Transfer Sub runs, and;
The formatting and forumlas from the ws named "Template" are copied to the new sheet and any additional new sheets that are created when the FormatNewSheet sub runs.
Please could yo help me merge the 2 or suggest a better way?
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
Sub FormatNewSheet(ws As Worksheet) Dim wsTemplate As Worksheet Set wsTemplate = Worksheets("Bob") Application.EnableEvents = False Application.ScreenUpdating = False Application.CutCopyMode = False 'Copy the range from the template wsTemplate.Range("D5:G10").Copy 'Paste the format to the new range ws.Select ws.Range("D5:G10").Select Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False Application.EnableEvents = True Application.CutCopyMode = xlCopy Application.ScreenUpdating = True End Sub