VB HELP!! Variable Undefined??

[Ejimenez]

** For some reason VB is stating that the Variable "Sheet" is not
defined and highlighting the Sub CopySheet() function. Does anyone know
why this may occur??





Thank you!!



Sub copysheet()

'create a variable to hold the new name

Dim newshtname



'Make sure that cell is not empty

If IsEmpty(Range("F2").Value) Then

MsgBox "Name required!", vbCritical

Exit Sub

End If



'Assign the new name to the variable

newshtname = Range("F2").Value



'make sure that the proposed name does not already exist

For Each Sheet In ActiveWorkbook.Sheets

If UCase(Sheet.Name) = UCase(newshtname) Then

MsgBox "The sheet name you have input already exists. Please input a new name.", vbCritical

Exit Sub

End If

Next Sheet



'the following will copy the active sheet, place the copy after the
fourth sheet in the workbook, and then rename the sheet to the name that
was input in B4.

ActiveSheet.Copy after:=Sheets(4)

ActiveSheet.Name = newshtname





End Sub



Private Sub CommandButton1_Click()

Sheet1.copysheet

End Sub

 

[patel45]

declare it
Dim newshtname, sheet as Worksheet

 

[pgc01]

** For some reason VB is stating that the Variable "Sheet" is not
defined and highlighting the Sub CopySheet() function. Does anyone know
why this may occur??

I looked through your code and I see the definition of newshtname but I don't see where you have defined the variable Sheet. Can you point it out?

 

[Ejimenez]

Thank you all for respondingI

have decleared the work sheet as "Dim newshtname as Sheet'.. However, it is still not working. Now it is highlighting the Sub Copysheet() and refering to the Newshtname as sheet The error reads"user defined type not defined"

 

[patel45]

can you read this ? don't change it

Dim newshtname, sheet as Worksheet