Progressing Worksheet

[Paul Morris]

Hi There,

Looking to write a macro to go through each worksheet in a workbook and get data from each, want it to select the first worksheet, get data, select next worksheet, etc till it's gone through each tab.

Any ideas on how to do this?

Cheers,
Paul

 

[Norie]

Paul

Can you give some more information?

To loop through all worksheets you can try this.

Dim wb As Workbook
Dim ws As Worksheet
      
     Set wb = ActiveWorkbook
     
     For Each ws in wb.Worksheets
          ' do something with worksheet ws
     Next ws

 

[just_jon]

Well, you could do something like -

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> WkSh <SPAN style="color:#00007F">As</SPAN> Worksheet
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> WkSh <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    <SPAN style="color:#007F00">' do your stuff here</SPAN>
    <SPAN style="color:#00007F">If</SPAN> WkSh.Name <> "Summary" <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">With</SPAN> WkSh
            .Range("A1:A10").Copy Worksheets("Summary").Cells(Worksheets("Summary").Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">Next</SPAN> WkSh
End <SPAN style="color:#00007F">Sub</SPAN></FONT>