VBA - "Indirect" needed

[surkdidat]

Good afternoon

On Sheet 1 of my spreadsheet I have in column B, starting in B2, down to B22 the list of names of the individual tabs.

B2 = AAA
B3 = BBB
B4 = CCC

etc.

What I need please, is some lovely VBA coding that uses an INDIRECT (or whatever the equivalent is in VBA) that looks up that name, and then performs a particular function on that sheet ( I already have that code to hand), then it goes to B3, B4 etc, until it hits a blank cell. As this is a report that is going to be run regularly, the individual tab names will be changing, there I cannot simply have code which states the name of the sheets.

Many thanks

 

[Fluff]

How about something like this

Sub Shts()

    Dim Cl As Range
    
    For Each Cl In Range("B2", Range("B2").End(xlDown))
        With Sheets(Cl.Value)
            'some
            'code
            'here
        End With
    Next Cl

End Sub

 

[surkdidat]

Many thanks Fluff for that.
However, what I THOUGHT would work DOESNT!!
What I need is to go into each of the tabs and on columns E and F use text to columns to convert the data into DMY format.
Is that possible please?
Here's hoping

 

[Rick Rothstein]

On Sheet 1 of my spreadsheet I have in column B, starting in B2, down to B22 the list of names of the individual tabs.

B2 = AAA
B3 = BBB
B4 = CCC

etc.

Does your list contain the names on every tab? If yes, great... if not, is the list of tab names that are not on the list the same every time If so, can you list what they are? Also, what about Sheet 1 which contains the list... is it on the list?

 

[surkdidat]

Hi Rick
Yes, on Sheet1 - (which is my Master Page) - it list every tab in my workbook. In AAA BBB CCC etc in Columns E and Columns F has a "date" column" but its in the format 22.05.17, so when I run a query on the master (sheet1) it displays 00/01/1900 (trying to find earliest date).

QUOTE=Rick Rothstein;4915788]Does your list contain the names on every tab? If yes, great... if not, is the list of tab names that are not on the list the same every time If so, can you list what they are? Also, what about Sheet 1 which contains the list... is it on the list?[/QUOTE]

 

[Rick Rothstein]

Hi Rick
Yes, on Sheet1 - (which is my Master Page) - it list every tab in my workbook.

It lists every sheet in the workbook? No sheets are omitted, not even Sheet1 (the Master Page)? If it turns out that Sheet1 is the only sheet not listed, what is the name in its tab?

 

[surkdidat]

Sorry, to clarify it does not list Sheet1 (Just named Sheet1). All other worksheets are named.

 

[Rick Rothstein]

Sorry, to clarify it does not list Sheet1 (Just named Sheet1). All other worksheets are named.

Good, that means you do not need to maintain the list of tab names on Sheet1 at all... the code will simply iterate all the sheets that are not named Sheet1 and apply the TextToColumns method to Columns E and F on each worksheet no matter what its name is...

[table="width: 500"]
[tr]
	[td]Sub ConvertStringDatesToRealDates()
  Dim WS As Worksheet
  For Each WS In Worksheets
    On Error GoTo NoDates
    If WS.Name <> "Sheet1" Then
      WS.Range("E:E").TextToColumns Range("E1"), xlDelimited, xlDoubleQuote, False, False, False, False, False, False, "", Array(1, 4)
      WS.Range("F:F").TextToColumns Range("F1"), xlDelimited, xlDoubleQuote, False, False, False, False, False, False, "", Array(1, 4)
    End If
Continue:
  Next
  Exit Sub
NoDates:
  Resume Continue
End Sub[/td]
[/tr]
[/table]

 

[surkdidat]

Many thanks, that has saved me so much work

Good, that means you do not need to maintain the list of tab names on Sheet1 at all... the code will simply iterate all the sheets that are not named Sheet1 and apply the TextToColumns method to Columns E and F on each worksheet no matter what its name is...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Sub ConvertStringDatesToRealDates()
  Dim WS As Worksheet
  For Each WS In Worksheets
    On Error GoTo NoDates
    If WS.Name <> "Sheet1" Then
      WS.Range("E:E").TextToColumns Range("E1"), xlDelimited, xlDoubleQuote, False, False, False, False, False, False, "", Array(1, 4)
      WS.Range("F:F").TextToColumns Range("F1"), xlDelimited, xlDoubleQuote, False, False, False, False, False, False, "", Array(1, 4)
    End If
Continue:
  Next
  Exit Sub
NoDates:
  Resume Continue
End Sub
[/TD]
[/TR]
</tbody>[/TABLE]