I am playing with recording a macro to select an entire worksheet, copy and paste it in a new worksheet, same excel workbook, and pasting it there. I figured out the following code for doing so:


Sub CopyWorksheet()
    Sheets("Candidate (1)").Select
    Sheets("Candidate (1)").Copy After:=Sheets(1)
End Sub
What I like about this is the next worksheet is named "Candidate (2)" and the next "Candidate (3)" and so on. This clarifies perfectly for the user.

1) What I don't like about this is worksheet Candidate (3) is between Candidate (1) and Candidate (2) because I've specified to put it after the first worksheet. I've tried to change the code to read before another specific sheet, but It didn't want to cooperate. I have a worksheet in my workbook for Values. In the Microsoft Excel Objects list it's listed as Sheet3 (Values). How do I have it place it before that always? That should always keep the numbering in order (Unless there is a better way to do this).

2) On my UserForm, is there a way for me to provide the user with a field to enter the number of times this should repeat, or loop (I assume this would be an example of a time when looping would occur).