VBA Loop through selected range of sheets

[acharsanthosh]

Hello Folks,

I have a workbook with 45 sheets and all the sheet names listed in 45th Tab naming "Other Data".

Now I want to run a for each loop based on the sheet list name range in "Other Data Tab".

Can anyone please help me out with this?

Am able to run the loop for all the worksheets as below

Sub loopforeach

Dim ws as Worksheet

For Each ws in Worksheets

Next

End Sub

Looking forward for your help!!

Thank you

San

 

[MickG]

Assuming the sheet names are in column "A" of sheet "Other Data" , try this.

[COLOR="Navy"]Sub[/COLOR] MG20Dec54
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, ShtRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Other Data")
[COLOR="Navy"]Set[/COLOR] Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]With[/COLOR] Sheets(Dn.Value)
      [COLOR="Navy"]Set[/COLOR] ShtRng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] ShtRng
               MsgBox R.Address(external:=True)
               '[COLOR="Green"][B] Do something Here with column "A" range for Individual sheets[/B][/COLOR]
   
            [COLOR="Navy"]Next[/COLOR] R
   [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[acharsanthosh]

Thanks A lot Mick.

But it is throwing an error saying "Compile error: Expected End with"

 

[Joe4]

Looks like he just forgot one closing "End With" statement.
It should be between the "Next Dn" and "End Sub" rows.

 

[MickG]

Sorry, the "End With" was originally at the end of the sub, and I forgot to copy it.
Try this:-

[COLOR="Navy"]Sub[/COLOR] MG20Dec07
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Range, ShtRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Other Data")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]With[/COLOR] Sheets(Dn.Value)
      [COLOR="Navy"]Set[/COLOR] ShtRng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
            [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] R [COLOR="Navy"]In[/COLOR] ShtRng
               MsgBox R.Address(external:=True)
               '[COLOR="Green"][B] Do something Here with column "A" range for Individual sheets[/B][/COLOR]
   
            [COLOR="Navy"]Next[/COLOR] R
   [COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick