Running the same VBA code for Many Sheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gatarossi@ig.com.br - 14 Feb 2008 00:42 GMT

Dear all,

I have a lot of sheets in my workbook and I need to run the same code
in each sheet, but one variable of my VBA code is exactly the name of
the sheet.

I have a sheet with the name of all sheets that I will use:

For example:
A
1 dog
2 cat
3 fish

Then I'm trying to create a variable with the name of the sheet:

Sub test()

Dim xlsht As Excel.Worksheet
Dim xlsht2 As Excel.Worksheet

prodline = xlsht2.Cells(2, 1)

Set xlsht = Sheets(prodline)

xlsht.Select

End Sub

But it doesn't work!!!!

How can I do it???

Thanks a lot!!!

Reply to this Message

Dave Peterson - 14 Feb 2008 01:07 GMT

I don't see where you set xlsht2 to anything.

So
prodline = xlsht2.Cells(2, 1)
looks like it cause an error.

> Dear all,
>
[quoted text clipped - 30 lines]
>
> Thanks a lot!!!

Signature

Dave Peterson

Reply to this Message

Jim Cone - 14 Feb 2008 01:15 GMT

You need to declare prodline as a string...
Dim prodline as String
You also need to tell Excel what xlsht refers to...
Set xlsht = worksheets("name of sheet with titles on it")
Then...

Sub AirCode
Dim prodline as string
Dim rCell as range
Dim xlsht As Worksheet
Set xlsht = Worksheets("Titles Sheet")

For Each rCell in xlsht.Range("A1:A3)
prodline = rCell.Value
Worksheets(prodline).Range("B3").Value = "Mush"
Next' rCell
End Sub

Signature

Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

<gatarossi@ig.com.br>
wrote in message
Dear all,
I have a lot of sheets in my workbook and I need to run the same code
in each sheet, but one variable of my VBA code is exactly the name of
the sheet.
I have a sheet with the name of all sheets that I will use:

For example:
A
1 dog
2 cat
3 fish

Then I'm trying to create a variable with the name of the sheet:
Sub test()
Dim xlsht2 As Excel.Worksheet
prodline = xlsht2.Cells(2, 1)
Set xlsht = Sheets(prodline)
xlsht.Select
End Sub

But it doesn't work!!!!
How can I do it???
Thanks a lot!!!

Reply to this Message