VB HELP!! Variable Undefined??

Ejimenez

New Member
Joined
Nov 10, 2012
Messages
14
** 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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
** 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?
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top