Loop through worksheets - convert used range to values - NOT LOOPING THROUGH SHEETS :(

[jwb1012]

Hello, I am attempting to loop through all of my worksheets and if the worksheet begins with "Labor BOE" I would like to copy/paste everything as values (there are formulas all over the sheet that I want to paste as values). Unfortunately, all of my attempts only work on the current "Labor BOE" worksheet that is open and doesn't loop through the others to paste values. Any thoughts?



Last row determined by column T.
Last column = Column U (aka Column 21)

Attempt #1: works on active sheet, doesn't loop through all sheets

Sub Paste_Values_2()
    'STEP 1: Define variables
    Dim Sh As Worksheet
    Dim A As Long
    Dim Dest_End_Row As Long
    
        For A = 1 To Sheets.Count
            If Left(Sheets(A).Name, 9) = "Labor BOE" Then
                Dest_End_Row = Sheets(A).Cells(Rows.Count, "T").End(xlUp).Row
                    With Sheets(A).Range(Cells(1, 1), Cells(Dest_End_Row, 21))
                        .Value = .Value
                    End With
            End If
        Next
End Sub

Attempt 2:

Sub Paste_Values_2()
    'STEP 1: Define variables
    Dim Sh As Worksheet
    Dim A As Long
    Dim Dest_End_Row As Long
    
        For Each Sh In ActiveWorkbook.Worksheets
            If Left(Sh.Name, 9) = "Labor BOE" Then
                Dest_End_Row = Sh.Cells(Rows.Count, "T").End(xlUp).Row
                    With Range(Cells(1, 1), Cells(Dest_End_Row, 21))
                        .Value = .Value
                    End With
            End If
        Next Sh
End Sub

 

[MARK858]

Try...

Sub Paste_Values_2()
    'STEP 1: Define variables
    Dim Sh As Worksheet
    Dim A As Long
    Dim Dest_End_Row As Long
    
        For Each Sh In ActiveWorkbook.Worksheets
            If Left(Sh.Name, 9) = "Labor BOE" Then
                Dest_End_Row = Sh.Cells(Rows.Count, "T").End(xlUp).Row
                    With [COLOR="#FF0000"]Sh.[/COLOR]Range([COLOR="#FF0000"]Sh.[/COLOR]Cells(1, 1), [COLOR="#FF0000"]Sh.[/COLOR]Cells(Dest_End_Row, 21))
                        .Value = .Value
                    End With
            End If
        Next Sh
End Sub

or

Sub Paste_Values_2()
    'STEP 1: Define variables
    Dim Sh As Worksheet
    Dim A As Long
    Dim Dest_End_Row As Long
    
        For A = 1 To Sheets.Count
            If Left(Sheets(A).Name, 9) = "Labor BOE" Then
                Dest_End_Row = Sheets(A).Cells(Rows.Count, "T").End(xlUp).Row
                    With [COLOR="#FF0000"]Sheets(A).[/COLOR]Range([COLOR="#FF0000"]Sheets(A).[/COLOR]Cells(1, 1), [COLOR="#FF0000"]Sheets(A).[/COLOR]Cells(Dest_End_Row, 21))
                        .Value = .Value
                    End With
            End If
        Next
End Sub

 

[Michael M]

you could also simply activate each sheet.....but I'd go with Marks solution

Sub Paste_Values_2()
    'STEP 1: Define variables
    Dim Sh As Worksheet, A As Long, Dest_End_Row As Long
        For Each Sh In Worksheets
         sh.activate
          If Left(Sh.Name, 9) = "Labor BOE" Then
                Dest_End_Row = Sh.Cells(Rows.Count, "T").End(xlUp).Row
                    With Range(Cells(1, 1), Cells(Dest_End_Row, 21))
                        .Value = .Value
                    End With
            End If
        Next Sh
End Sub

 

[jwb1012]

Thanks to both of you. Mark's solution works perfectly.

 

[My Aswer Is This]

Try this:

Sub Convert_Values()
Application.ScreenUpdating = False
Dim i As Long
    
        For i = 1 To Sheets.Count
            If Left(Sheets(i).Name, 9) = "Labor BOE" Then
                With Sheets(i).UsedRange
                .Value = .Value
                End With
            End If
        Next
Application.ScreenUpdating = True
End Sub