select a sheet by using a variable

[smiler44]

I need to search through Column A of 24 sheets (sheets 2 to 25) to see if the contents of column A of sheet 1 appears.

I am trying to select each sheet in turn using a variable, I'm sure this is simple but it has me beaten. Can you help?

sheet2.select works so I thought the code would be along the lines of :-

sub
dim shht as string
dim sht as string
dim stt as string

shht = sheet
sht = 2
stt = shtt + sht

stt.select

end sub

thanks smiler44

 

[chergh]

That really isn't the way to do it. What you can do instead is loop through the worksheet collection. I've also included an example of how to search and loop through a column searching for something.

It's written off the top of my head so may well contain a few mistakes as I don't have excel at home to test it.

option explicit

sub blah

dim ws as worksheet 'used to loop through worksheets collection
dim ws1 as worksheet 'use to set refernce to page which contains values you want to find
dim wb as workbook

dim firstAddress as string

dim rng as range

set wb = thisworkbook

set ws1 = wb.worksheets("Sheet1") 



for each ws in wb.worksheets

  if ws.name <> ws1.name
   with ws.range("A")

       set rng = .find(what:=ws1.range("A1"), _
                           lookat:= xlwhole 'change xlwhole to xlpart if you only need a partial match
       If not rng is nothing then 'this checks to see if any matches are found
           firstAddress = rng.address 'records the first cell found
                  
                   'code here to do what you want to do when you find a duplicate

           do

               set rng = .findnext(rng)

               'code here to do what you want to do when you find a 

           loop while not rng is nothing  and rng.address <> firstAddress

         end if
   end with
end if
next ws ' this moves to the next worksheet and the search is repeated
  end sub

 

[smiler44]

Chergh,
Thank you for the reply it has been very helpful. Although I could not make the code work entirley I managed to make it select each tab in turn. I could not make it act on each sheet once selected.

thank you again smiler44

 

[Bilbo_Baggins_Esq]

I think you may need to try working with this when you're switching from one sheet to another and needing to work with that sheet

ActiveWorkbook.ActiveSheet.ws