replacing sheet("XX").Select with for Loop Worksheet Name

I don't understand what you are trying to do - can you post your actual code?

There shouldn't really ever be a need to select anything

My code is finding the worksheet with the "001" name and creating a pivot table with the range.

But I have to re click on the worksheet so i can convert column F into a number

Sub Macro1()
'
' Macro1 Macro
'

'

For Each wsh In ThisWorkbook.Worksheets
    If (left(wsh.name,3)= "001") Then 
        Set rng = wsh.Range("A1").CurrentRegion
        Exit For
    End If
Next
Sheets.Add
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Rng, Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="Sheet1!R1C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12
    Sheets("Sheet1").Select
    ActiveSheet.Shapes.AddChart.Select
    ActiveWorkbook.ShowPivotChartActiveFields = True
    ActiveChart.ChartType = xlColumnClustered
    ActiveWorkbook.ShowPivotChartActiveFields = False
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Item")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Location")
        .Orientation = xlPageField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
        "PivotTable1").PivotFields("Qty"), "Sum of Qty", xlSum
    ActiveChart.ChartType = xlLine
    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.NumberFormat = "#,##0.00"
    Selection.TickLabels.NumberFormat = "#,##0"
    ActiveChart.SetElement (msoElementLegendBottom)
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Select
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Inv Graph"
    Sheets("Sheet1").Select
    Range("B8").Select
   Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Style = "Comma"
    Selection.NumberFormat = "_(* #,##0.0_);_(* (#,##0.0);_(* ""-""??_);_(@_)"
    Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""??_);_(@_)"
    Sheets("Sheet1").Name = "Inv Graph Table"

    Sheets("001_Table").Select
    Range("F1").Select
    Range("A2", Cells(Rows.Count, 1).End(xlUp)).Offset(, 5).Select
    Range("A2", Cells(Rows.Count, 1).End(xlUp)).Offset(, 5).TextToColumns 'convert the text to Numbers format
    Selection.NumberFormat = "#,##0"

Sorry I still really don't understand what you're trying to do/what the problem is.

There's no need to select anything, maybe:

With Sheets("001_Table")
    .Range("F1").NumberFormat = "#,##0"
    With .Range("A2", Cells(Rows.Count, 8).End(xlUp)).Offset(, 5)
        .TextToColumns
        .NumberFormat = "#,##0"
    End With
End With

Sorry for being unclear but I am trying to make a universal code that will work with my other excel files. So i can just copy and paste the macro into the files without changing the tab name

Since these are the two tab name for each workbook. So that why I created a for loop to search for 001 in the tab name.

Sheets("001_Table").Select

and

Sheets("001_List").Select

---------- Post added at 10:01 AM ---------- Previous post was at 10:00 AM ----------

I used the following code below which worked out

activesheet.next.select