Using VBA to make a copy of the Active Worksheet

Graebeard

New Member
Joined
Mar 21, 2015
Messages
9
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Mike LH

Well-known Member
Joined
Mar 17, 2015
Messages
566
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

New Member
Joined
Mar 21, 2015
Messages
9
Perfect.

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


Graebeard (y)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,005
Messages
5,526,233
Members
409,689
Latest member
martin_br

This Week's Hot Topics

Top