Macro to go through a list

[tonywatsonhelp]

Hi Everyone,

I have this code which currently runs on a sheet called "Amav"
I'd like to Make a list of sheet names down column A (From 2 to last row) and have the macro run on each name
How do I adjust the code so it does this?
Thanks
Tony

Sub Text_to_Numbers()

sht = "Amav"
           With sht.Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row) 'text to numbers
            .NumberFormat = "General"
            .Value = .Value
            End With

End Sub

 

[AC PORTA VIA]

try this
Sub Dosomething()

' From - vba run macro on all sheets - Bing
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call RunCode
Next
Application.ScreenUpdating = True
End Sub
Sub RunCode()
With Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row) 'text to numbers
.NumberFormat = "General"
.Value = .Value
End With
End Sub

 

[tonywatsonhelp]

Hi Act Porta,
Thanks for your help but I really need to make a list of the sheet names Down column A and only run on those as I said above, I'm not looking to do this on every sheet.
Thanks Anyway
Tony

 

[AC PORTA VIA]

If you have a list of sheets already in column A try this

Sub Dosomething2()
Dim sheet_name As Range
 For Each sheet_name In Sheets("sheet1").Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)
  If sheet_name.Value = "" Then
   Exit For
   Else
   Sheets(sheet_name.Value).Select
Call RunCode
End If
Next sheet_name

Application.ScreenUpdating = True
End Sub
Sub RunCode()
With Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row) 'text to numbers
.NumberFormat = "General"
.Value = .Value
End With
End Sub

 

[bebo021999]

Try to create list of sheet's name from cell A1 sheet Amv, then text to numbers for all sheets :

Sub Text_to_Numbers()
Dim sht As Worksheet
For i = 1 To Sheets.Count
    Sheets("Amv").Range("A" & i).Value = Sheets(i).Name
Next
    For Each sht In Sheets
        With sht.Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row)
            .NumberFormat = "General"
            .Value = .Value
        End With
    Next
End Sub

In case list of sheet's name is helper to run macro, then it can be removed out:

Sub Text_to_Numbers()
Dim sht As Worksheet
    For Each sht In Sheets
        With sht.Range("I2:I" & Cells(Rows.Count, "I").End(xlUp).Row)
            .NumberFormat = "General"
            .Value = .Value
        End With
    Next
End Sub

 

[bebo021999]

Dupplicate post removed.

 

[bebo021999]

Dupplicate post removed.

 

[tonywatsonhelp]

Bebo,
this is great thanks so much
Tony