simple macro to copy a sheet, object undefined error

[Excel-General]

I'm still having trouble with objects, but i can't figure out which
one.

Sub CopySheet()
Dim wb As Workbook
Set wb = ThisWorkbook

wb.Sheets(2).Copy Type:=xlWorksheet, after:=Sheets(2)

End Sub

tnx,

 

[Dave Peterson]

How about:

Option Explicit
Sub CopySheet()
Dim wb As Workbook
Set wb = ThisWorkbook
wb.Sheets(2).Copy after:=wb.Sheets(2)
End Sub

 

[Excel-General]

How about:

Option Explicit
Sub CopySheet()
Dim wb As Workbook
Set wb = ThisWorkbook
wb.Sheets(2).Copy after:=wb.Sheets(2)
End Sub

thanks,

 

[Excel-General]

How about:

Option Explicit
Sub CopySheet()
Dim wb As Workbook
Set wb = ThisWorkbook
wb.Sheets(2).Copy after:=wb.Sheets(2)
End Sub

I revised it slightly to rename the copied sheet. It works but it
doens't rename the sheet. How can I fix it so it names my new sheet
with a global variable name. Thanks
Hi, I revised it slightly to rename the new sheet however it doesn't
rename the sheet. g_Fname is a global variable.
I would like it to name the new sheet moved to index 2 with the global
first name. Thanks.

Sub CopySheet()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook

wb.Sheets(2).Copy after:=Sheets(2)
Set ws = wb.Sheets(3)
ws.Name = "m_Fname"
End Sub

 

[Dave Peterson]

You didn't qualify the "after:=" sheets. You didn't use g_Fname anywhere in
your code. And if you use "before:=" instead of "after:=", then the sheet will
be in the correct location. (Or use after:=wb.sheets(1))

Sub CopySheet()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook

wb.Sheets(2).Copy before:=wb.Sheets(2)
Set ws = wb.Sheets(2)
ws.Name = g_Fname
End Sub

Excel-General wrote:

 

[Excel-General]

You didn't qualify the "after:=" sheets. You didn't use g_Fname anywhere in
your code. And if you use "before:=" instead of "after:=", then the sheet will
be in the correct location. (Or use after:=wb.sheets(1))

Sub CopySheet()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook

wb.Sheets(2).Copy before:=wb.Sheets(2)
Set ws = wb.Sheets(2)
ws.Name = g_Fname
End Sub

Excel-General wrote:

<<snipped>>


thanks,

 

[Excel-General]

You didn't qualify the "after:=" sheets. You didn't use g_Fname anywhere in
your code. And if you use "before:=" instead of "after:=", then the sheet will
be in the correct location. (Or use after:=wb.sheets(1))

Sub CopySheet()
Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook

wb.Sheets(2).Copy before:=wb.Sheets(2)
Set ws = wb.Sheets(2)
ws.Name = g_Fname
End Sub

Excel-General wrote:

<<snipped>>

That was really smart by the way to do it before and put it in front
of itself.

 

[Dave Peterson]

Sometimes, it's an extra pair of eyes helping to get what you need.