Prev: Marking found records in Workbook A from Target List in Workbook B
Next: Word wrap and height
From: BeSmart on 13 Mar 2010 23:35
Hi
I've done something wrong here and I can't work out what it is...
Error msg: "Compile Error: With object must be user-defined type, object,
or Variant"

Sub copylplan()
Dim mySheet As String

mySheet = ActiveSheet

With mySheet
.Copy After:=Sheets(Sheets.Count)
End With

With mySheet
.Name = "Extract Plan"
End With
End Sub
--
Thank for your help
BeSmart
From: OssieMac on 13 Mar 2010 23:46
Hi,

Set the variable as a Worksheet then use Set in the line to assign the
ActiveSheet to the variable.

You should realize that the variable remains the original ActiveSheet; it
does not become the new ActiveSheet after the copy and therefore it is the
original worksheet that gets renamed with the following code.

Sub copylplan()

Dim mySheet As Worksheet

Set mySheet = ActiveSheet

With mySheet
.Copy After:=Sheets(Sheets.Count)
End With

With mySheet
.Name = "Extract Plan"
End With

End Sub


If you want to apply the name to the new sheet then as follows.

Sub copylplan()

Dim mySheet As Worksheet

Set mySheet = ActiveSheet

With mySheet
.Copy After:=Sheets(Sheets.Count)
End With

With ActiveSheet
.Name = "Extract Plan"
End With

End Sub


You can then still reference the original worksheet like follows.

mySheet.Activate


--
Regards,

OssieMac



 | 
Pages: 1
Prev: Marking found records in Workbook A from Target List in Workbook B
Next: Word wrap and height