copying a worksheet to same workbook and renaming it

macro_user

Board Regular
Joined
Mar 9, 2009
Messages
101
hi...
i would like to use a macro which copies a worksheet from a workbook to the same workbook and paste it at the end and then renaming it... everything using a macro...
appreciate the help...
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Welcome to the Board!

How's this:

ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Foo"

Note that there's no error checking to test for a valid or pre-existing sheet name.

Hope that helps,
 
Upvote 0
Welcome to the Board!

How's this:

ActiveSheet.Copy after:=Sheets(Sheets.Count)
ActiveSheet.Name = "Foo"

Note that there's no error checking to test for a valid or pre-existing sheet name.

Hope that helps,

wat if the base sheet which i need to copy is not the active sheet? im running a macro from some other sheet and so, this base sheet will not be the activesheet... wat would i do then?

can i use ActiveSheet.Copy after:=Sheets("Some_Sheet_name")
like, can i specify the sheet name after which i can paste this copied sheet?
is this valid???
 
Upvote 0
try this

Code:
Sub Macro1()
Dim WSCount As Long
WSCount = Worksheets.Count
'
        ActiveWorkbook.Sheets("Sheet2").Copy _
           After:=ActiveWorkbook.Sheets(WSCount)
            Sheets("Sheet2 (2)").Name = "NewSheet" & WSCount + 1
End Sub

change the Sheet2 to the sheet name you are copying
change the NewSheet to the sheet name you wanted it renamed
also I had the NewSheet get a number added to it, so remove that if you don't want it.

HTH
 
Upvote 0
Thank you....
just curious... wat does the underscore(_) do??? why is it needed?
apologies for my knowledge on excel VBA...
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top