[RESOLVED][VBA] Help with macro to copy worksheet

[newtovba]

Hi All,
I'm trying to create a macro that copies the current worksheet and places the copied ws before it. I've gotten as far as below, but that only copies the ws and places it in the second to last tab. Thanks in advance for any help.


Sub Copysheet()

Dim i As Integer
Dim p As Integer
On Error GoTo out
i = InputBox("How many copies would you like?", "Making Copies")

Application.ScreenUpdating = False
p = 0
Do
ActiveSheet.Copy Before:=Sheets(Sheets.Count)
p = p + 1
Loop Until p = i
Application.ScreenUpdating = True
Exit Sub
out:
MsgBox "Copy Cancelled"
Application.ScreenUpdating = True
End Sub

[LexGixxer]

Change

ActiveSheet.Copy Before:=Sheets(Sheets.Count)

to
ActiveSheet.Copy Before:=Sheets("NameofOriginalWorksheet")

[newtovba]

I cant seem to get it to work. It goes to "copy cancelled"

[LexGixxer]

Post the amended code for me.

[opus]

@LexGixxer
I take a guess, he did paste in exactly what you posted!

[newtovba]

I should have said it does work, but by taking the name of worksheet, it will only allow me to copy that one specific worksheet over (I need to copy the current ws (which is then renamed and updated), and then copied again at a later date


Sub Copysheet()

Dim i As Integer
Dim p As Integer
On Error GoTo out
i = InputBox("How many copies would you like?", "Making Copies")

Application.ScreenUpdating = False
p = 0
Do
ActiveWorkbook.Sheets("February 12").Copy Before:=ActiveWorkbook.Sheets("February 12")
p = p + 1
Loop Until p = i
Application.ScreenUpdating = True
Exit Sub
out:
MsgBox "Copy Cancelled"
Application.ScreenUpdating = True
End Sub

[opus]

If need to work with the ActiveSheet just use ".ActiveSheet" instead of ".Sheets(xxxx)".

[newtovba]

Got it. Thanks.

A little new at this

[westconn1]

(I need to copy the current ws (which is then renamed and updated), and then copied again at a later date

how can the code know when these changes have been made and to now copy the worksheet?
unless you make these changes within the code, then copy again