For this:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN>
<SPAN style="color:#00007F">Function</SPAN> SheetCompiler()
<SPAN style="color:#00007F">Dim</SPAN> MB1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Message Box 1</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> MB2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Message Box 2</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> SN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Sheet Number</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> X <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Byte</SPAN> <SPAN style="color:#007F00">'Utility variable</SPAN>
<SPAN style="color:#00007F">Dim</SPAN> HR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> <SPAN style="color:#007F00">'Header Right</SPAN>
<SPAN style="color:#007F00">'Written by Aaron Bush 12/05/05 Free for Public Use</SPAN>
MB1 = MsgBox("Add sheet labels?", 65572, "Sheet Compiler")
SN = ActiveSheet.Index
<SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.MultiUserEditing <SPAN style="color:#00007F">Then</SPAN> ActiveWorkbook.ExclusiveAccess
ActiveWorkbook.Sheets.Add Before:=Worksheets(SN)
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">Resume</SPAN> <SPAN style="color:#00007F">Next</SPAN>
NameSheet:
ActiveSheet.Name = "Compiled Sheet " & Format(Date, "mmddyy")
<SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
MB2 = MsgBox("Default name already in use, delete?", 65572, "Sheet Compiler")
<SPAN style="color:#00007F">If</SPAN> MB2 = 6 <SPAN style="color:#00007F">Then</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
Sheets("Compiled Sheet " & Format(Date, "mmddyy")).Delete
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
Err.Clear
<SPAN style="color:#00007F">GoTo</SPAN> NameSheet
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Err.Clear
ActiveSheet.Name = InputBox("Default name already in use, please enter a name for compilation sheet:", "Sheet Compiler", "Compiled Sheet " & Format(Date, "mmddyy"))
<SPAN style="color:#00007F">If</SPAN> Err.Number <> 0 <SPAN style="color:#00007F">Then</SPAN>
Application.DisplayAlerts = <SPAN style="color:#00007F">False</SPAN>
ActiveSheet.Delete
Application.DisplayAlerts = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Function</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> 0
Sheets(SN + 1).Rows(1).Copy
ActiveSheet.Paste
<SPAN style="color:#00007F">If</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN>
HR = Mid(Columns(Range("IV1").End(xlToLeft).Offset(0, 1).Column).Address, 2, InStr(Columns(Range("IV1").End(xlToLeft).Offset(0, 1).Column).Address, ":") - 2)
Range(HR & "1") = "Sheet Name"
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">On</SPAN> <SPAN style="color:#00007F">Error</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> TheEnd
<SPAN style="color:#00007F">For</SPAN> X = SN + 1 <SPAN style="color:#00007F">To</SPAN> Sheets.Count
Sheets(X).Range("2:" & Sheets(X).Range("A65536").End(xlUp).Row).Copy
Sheets(SN).Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
<SPAN style="color:#00007F">If</SPAN> MB1 = 6 <SPAN style="color:#00007F">Then</SPAN> Range(HR & Range(HR & "65536").End(xlUp).Offset(1, 0).Row, Range("a65536").End(xlUp).Offset(0, Columns(HR).Column - 1).Address) = Sheets(X).Name
<SPAN style="color:#00007F">Next</SPAN> X
TheEnd:
End <SPAN style="color:#00007F">Function</SPAN>
</FONT>