copying a worksheet to same workbook and renaming it

[macro_user]

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...

 

[Smitty]

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,

 

[macro_user]

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???

 

[texasalynn]

try this

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

 

[macro_user]

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

 

[texasalynn]

the underscore continues the code to the next line

 

[macro_user]

Thank u sooooo much...