passing a variable

bdee1

Board Regular
Joined
Feb 17, 2003
Messages
105
i am trying to create a sub which will be passed a sheet name and then select, and clear the contents of the sheet it was passed.

the code i am using for the sub is:

Code:
Sub ClearSheet(sheetName)
'
' selectSheet Macro
' Macro recorded 2/12/2004 by BDEE1
'

'
    Sheets(sheetName).Select
    Cells.Select
    Selection.ClearContents
End Sub

and the code i use to call the sub is:
Code:
Call ClearSheet(sheet1)

however when i try to run the macro i get the following error:
"Run-time error '9':
Subscript out of Range"

and when I click debug, it returns me to the line in my sub that says:

Code:
Sheets(sheetName).Select


is there something different i need to dodo pass a variable to my sub?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
and the code i use to call the sub is:

code:
--------------------------------------------------------------------------------
Call ClearSheet(sheet1)
--------------------------------------------------------------------------------




however when i try to run the macro i get the following error:
"Run-time error '9':
Subscript out of Range"

You would need to pass the variable as "Sheet1", so it would be

Code:
Call ClearSheet("Sheet1")
 

bdee1

Board Regular
Joined
Feb 17, 2003
Messages
105
ah ha! i figured it was some stupid syntax problem liek that.

Thanks!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,931
Messages
5,621,661
Members
415,849
Latest member
PhoenixRising2015

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