In a workbook I have a master form (sheet 1) and a summary log (sheet 2) and behind these I am saving copies of the unique completed forms per event / incident.
1. User enters data on sheet 1 (master form).
2. Click macro button to copy select data to sheet 2 (summary log). Code not shown.
2.a Copy completed sheet 1 with unique # in L1:M1 (e.g."0213-100") to back of workbook via paste special to only copy text and formatting (1 form snapshot per event / incident).
2.b Rename copied sheet/tab (now sheet 3+) with the unique # in L1:M1 (e.g. "0213-100").
2.c Clear master form and assign next sequential # (per # list in log), ready for next use.
2.d Save changes.
Need some help please with:
Step 2.a - to make the paste special work for text and formatting only
Step 2.b - the sheet/tab renaming (e.g. "0213-100") as it is currently retaining the name of the master form.
Sub Numbering() Dim lNum As Long Dim lShtLast As Long With Sheet2 lNum = WorksheetFunction.Max(.Range("B3:B500")) + 1 .Cells(.Rows.Count, "B").End(xlUp)(2, 1) = lNum 'If there's an entry in cells L1:M1, then... If Len(Range("M1")) = 0 Then MsgBox "There is no MIR # to create a separate tab from.", vbExclamation, "New MIR Tab Editor" Exit Sub Else '...copy the active sheet to the end of the workbook and 'name it as the text in cells L1:M1. ActiveSheet.Copy After:=Worksheets(Worksheets.Count) With ActiveSheet .Name = Range("L1:M1").Value .Shapes("cmdCreateTab").Delete End With End If End With End Sub