Using VBA to make a copy of the Active Worksheet

[Graebeard]

This driving me insane..

I want to make a copy of the current worksheet into the same workbook and assign a new name to it. Basically, I have a Raw Data worksheet, which I want to duplicate so it can be processed into a final layout. All the other code works fine, I just need to automate the monthly copying of the Raw Data sheets.

Here is the code I've pulled off this and other Forums. Unfortunately, I get an Error 9 when I run it.
---------------------------------------------------------------------------------------------
Sub macro3()

ActiveWorkbook.ActiveSheet.Copy Before:=ActiveWorkbook.Sheets("sheet5")

End Sub
---------------------------------------------------------------------------------------------

I have only one workbook open and that one has 8 named Sheets. Sheet5 is just one of several I've tried with the same results. It turns out that if I don't use the Before:= or After:= qualifier, the sheet is copied into a new workbook.

Any suggestions would be greatly appreciated

Thanks in Advance

 

[Mike LH]

Hi,

Try it like this. This copies the active worksheet to the end of the workbook and renames with the name of the active sheet and Copy


Sub somesub()
Dim ShName As String

With ActiveSheet
ShName = .Name & "Copy"
.Copy After:=Sheets(Worksheets.Count)
End With
Sheets(Worksheets.Count).Name = ShName
End Sub

 

[Graebeard]

Perfect.

Thanks Mike. That did the trick and I'll use it. Don't understand why the other code errored out.


Graebeard