simple macro to copy a sheet, object undefined error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Excel-General - 18 Jan 2008 21:05 GMT

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,

Reply to this Message

Dave Peterson - 18 Jan 2008 21:26 GMT

How about:

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

> I'm still having trouble with objects, but i can't figure out which
> one.
[quoted text clipped - 8 lines]
>
> tnx,

Signature

Dave Peterson

Reply to this Message

Excel-General - 18 Jan 2008 22:01 GMT

> How about:
>
[quoted text clipped - 21 lines]
>
> Dave Peterson

thanks,

Reply to this Message

Excel-General - 18 Jan 2008 22:47 GMT

> How about:
>
[quoted text clipped - 7 lines]
> > I'm still having trouble with objects, but i can't figure out which
> > one.

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

> > Sub CopySheet()
> > Dim wb As Workbook
[quoted text clipped - 9 lines]
>
> Dave Peterson

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

Reply to this Message

Dave Peterson - 19 Jan 2008 00:57 GMT

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

<<snipped>>

> Hi, I revised it slightly to rename the new sheet however it doesn't
> rename the sheet. g_Fname is a global variable.
[quoted text clipped - 10 lines]
> ws.Name = "m_Fname"
> End Sub

Signature

Dave Peterson

Reply to this Message

Excel-General - 19 Jan 2008 01:24 GMT

> 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
[quoted text clipped - 16 lines]
> > I would like it to name the new sheet moved to index 2 with the global
> > first name. Thanks.

thanks,

> > Sub CopySheet()
> > Dim wb As Workbook
[quoted text clipped - 9 lines]
>
> Dave Peterson

Reply to this Message

Excel-General - 19 Jan 2008 01:25 GMT

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

Reply to this Message

Dave Peterson - 19 Jan 2008 01:39 GMT

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

<<snipped>>

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

Signature

Dave Peterson

Reply to this Message