Running the same VBA code for Many Sheets

[gatarossi]

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!!!

 

[Dave Peterson]

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

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

 

[Jim Cone]

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
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



<[email protected]>
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!!!