I have a daily reporting system in which I have a macro to make a new sheet
as well as clear some information from the previous sheet in a workbook with
muliple sheets of different names besides DR(1) etc. Since the number of days
per job is variable, I need to add something into the macro to copy the
previous day information to the new sheet instead of reverting back to the
original to make a copy of it. The macro that I use now is below and you can
see it copys DR(1) when I would like it to copy from DR(6) if that sheet
happens to be in the workbook. I'm not sure what to add in and everthing
tried to date has not worked.

Sub NewDay()

Sheets("DR(1)").Copy After:=Sheets("DR(1)")
Range("U1") = Range("U1") + 1
Range("G12:O31,Q44:V49,H47:H49,K46:K49,N46:N49").Select
Selection.ClearContents
Range("G12").Select
End Sub:

Regards and thanks in advance.