Macro to copy worksheet/formatting, sequential naming

[msk37]

I'm a complete newbie to VBScript, and I've had no luck after about a week of searching and trial-and-error attempts...

I am creating a quality check spreadsheet where a user submits data into a cell range. I'm trying to minimize the risk of user error, so I'm automating just about everything in the document and locking everything else. I'll try to keep my questions as organized as possible:

Page generation

• Linked to a button at the bottom of each page (worksheets are printed off and I'd like to keep them at 1 page length per sheet).
• Name the new sheet sequentially (Page1, Page2, etc - Sheet1, Sheet2, etc also works)
• Copy the first page into the newly generated one (formatting, equations, etc)

I've pretty much been searching around for code that looks like it would work, copying it, and tweaking it to meet my needs. Haven't had too much luck at combining the separate elements though. For example: I was able to create a copied page with a button and was able to name it using Application.InputBox code, but I couldn't automate the naming process with a MessageBox. I've tried a For loop (i=2, create the page, Next i), but that doesn't work when I don't know how many pages I will need from the start.​

Cursor movement on subsequent pages

• Within a specific range of cells, move the cursor over, then over/down (I got this part just fine using the .offset code)
• Apply this to every sheet in the workbook (including newly generated sheets)

I wasn't able to get the code to work on newly generated sheets. It worked fine when I was in Sheet1 (with Sheet1 code), but when I tried applying the Worksheet_Change to the workbook using Workbook_Change, the code went seemingly ignored in new sheets.​

Clearly, I'm doing something wrong (unfortunately, I was doing this on my laptop, which I'm not supposed to bring into work- I can post the code later if needed). I'd absolutely love if someone could write up fresh code (mine is a mess/broken and I'm kind of embarrassed to post it), but I'm also willing to take advice/hints/whatever I can get.

 

[msk37]

Update (didn't see an edit option on the original post):
I played around with some code and started from scratch again. It is much neater than my prior mess.

'Module1
Sub AddPage()

'========== GET USER INPUT ==========

'Ask user if they want to generate another page for measurements
Dim SheetQuestion As Integer
SheetQuestion = MsgBox("Do you want to add another page?", vbYesNo, "Add Page?")

'If user selects No, cancel the script. Otherwise, proceed to PAGE CREATION.
If SheetQuestion = vbNo Then

MsgBox "You selected No." & vbCrLf & "No new sheet will be added.", 64, "Cancel"​

Exit Sub​

End If

'========== PAGE CREATION ==========

'Copy the cells and formatting of Page 1
Worksheets("Page 1").Cells.Copy

'Creates a new sheet arranged at the end
Dim NewSheet
Set NewSheet = Sheets.Add(After:=Sheets(Worksheets.Count), Count:=1, Type:=xlWorksheet)
NewSheet.Name = "Page " & Worksheets.Count

'Creates content on new sheet
With ActiveSheet

.Paste 'Pastes copied cells (appearance, formatting, and formulas)​

'.Goto Range("D18:G41", "I18:K41").ClearContents 'Clears old data, time/date stamps, and user input​

'.Goto Range("A16"), False 'Scrolls down to the table​

'.Goto Range("D18"), True 'Selects the first diameter cell for recording more data​

End With

End Sub

So far, this works pretty well towards what I want when placed in a Module rather than the Workbook sections. It asks the user if they want to create a new page, names it automatically and sequentially, and copies/pastes the cells from Page 1 into any new page. I am still working on tweaking the selection criteria in the new page (the commented out areas towards the end of the above code) to scroll down to the table and select the first data entry cell. That code worked perfectly before, but is having troubles in the new script. I'm sure I'll work it out soon enough.

I'm still unsure on how to best call the cursor movement. Should I do it in a Module or the Workbook? Or write the sub as a Workbook_Change or Workbook_Open? I'm pretty clueless on that part. It worked fine when I applied it as a change in the sheet code, but I need it through the whole workbook. Shamelessly 'borrowed' code I found elsewhere was pretty hit-and-miss for me. I'll keep plugging away at it since I usually solve it only after I ask for help...