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
 

Some videos you may like

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
** 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

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

Well-known Member
Joined
Jul 15, 2012
Messages
1,953
can you read this ? don't change it

Dim newshtname, sheet as Worksheet
 

Watch MrExcel Video

Forum statistics

Threads
1,123,171
Messages
5,600,124
Members
414,365
Latest member
UUR

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
Top