Help with VBA code to copy and rename a sheet sequentially

Hi everybody! I'm an experienced Excel user but I'm new to VBA. I've been able to cobble together some VBA code for a workbook I'm working on but I can't get past this last part. I've spent the last couple of hours searching online for an answer but none of the code I've been able to find will work for me. Here's the code that I have so far:

Sub NewSheet4()
'
' NewSheet4 Macro
'

'
Application.ScreenUpdating = False
Sheets("Daily_Worksheet").Select
Sheets("Daily_Worksheet").Copy After:=Sheets(2)
ActiveSheet.Move After:=Sheets(ActiveWorkbook.Sheets.Count)
ActiveSheet.Shapes("Button 5").Select
Selection.Cut
Range("A7:L9,B10,A13:H22,A25:H38,J25:L38").Select
Range("J25").Activate
ActiveWindow.SmallScroll Down:=15
Range("A7:L9,B10,A13:H22,A25:H38,J25:L38,A41:I47").Select
Range("A41").Activate
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-24
Range("N16").Select
ActiveSheet.Name = "Daily_Worksheet" & HighestNum + 1
Application.ScreenUpdating = True
End Sub

The section at the end in bold is what I'm having trouble with. I have a template worksheet called "Daily_Worksheet" and I want to copy that sheet, move it to the end of all existing sheets, clear out certain values, and then name it "Daily_Worksheet#" where the # is a sequential number starting at 1 and continuing as needed. I've been able to get everything else to happen except for the new worksheet's name. Does anyone have any suggestions??

Thanks!!

HighestNum doesnt mean anything which is why you got the error. A couple of questions:

1. Initially will you just have 1 sheet, named "Daily_Worksheet" and then evertime you run the macro you want to add 1 more worksheet to the workbook for many many worksheets?

2. What ranges specifically do you want to clear?

I’m assuming here. And you know what they say about that. I’m thinking you want to copy the completed form and clear the original. Try this on a test workbook and see if this is what you are after.

Please Login or Register  to view this content.

What I'm trying to do is take a worksheet that's been filled out by a user, make a copy of it with a specific name, and then clear out certain parts of it so the user can fill out a new copy of the form for a different date. The ranges that need to be cleared are in the code from my original post. I'll try the suggested code and see how it works.

Thanks for the input!

billstpierre79 - Your code didn't quite do what I wanted it to do but I used it to adjust my original code a bit and it works now! Thanks for the help!!