Loop not working - paste values on worksheets

[jwb1012]

Hello, I am using the following macro to paste values on all worksheets that begin with "Labor BOE".

1. Loop through worksheets and find those that start with "Labor BOE..."
2. Select all cells
3. Paste values (Alt+E+S+V)

Unfortunately, it is not looping through my worksheets and doing this action to all "Labor BOE" worksheets. It only works on the active worksheet. Any thoughts on where I am going wrong?

Sub AltESV()
' Scroll through worksheets
' For each worksheet that starts with Labor BOE...
    ' Select entire sheet (all cells)
    ' Paste Values (Alt+E+S+V)

    For Each Sh In ActiveWorkbook.Sheets
        If Left(Sh.Name, 9) = "Labor BOE" Then
                            
            With Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            End with

        End If
    Next Sh
End Sub

 

[My Aswer Is This]

I think I saw this same posting several weeks ago.

So what range are you trying to copy and where are you trying to paste it to.

Not sure what you want here:

With Cells.Select

 

[jwb1012]

Similar issue. This time I am just trying to select the whole sheet. I guess a more efficient way would be cell A1 to lastrow/lastcolumn... but still not sure why it isn't looping through my sheets.

 

[jwb1012]

I recorded this macro.... and added the "with" statement trying to improve my code... not sure if I have this down yet.

This is what it looked like after recording:

Sub AltESV()
' Scroll through worksheets
' For each worksheet that starts with Labor BOE...
    ' Select entire sheet (all cells)
    ' Paste Values (Alt+E+S+V)

    For Each Sh In ActiveWorkbook.Sheets
        If Left(Sh.Name, 9) = "Labor BOE" Then
                            
[U]            Cells.Select
            Application.CutCopyMode = False
            Selection.Copy
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False[/U]

        End If
    Next Sh
End Sub

 

[jwb1012]

And then I am looking to paste it in the exact same place... just to eliminate all formulas

 

[JackDanIce]

Untested (test on a backed-up file), however, try:

Sub HardCodeVals()
    
    Dim wks         As Worksheet
    Dim LastCell()  As Variant
    Const SHEETNAME As String = "Labor BOE*"
    ReDim LastCell(1 To 2)
    
    Application.ScreenUpdating = False
    
    For Each wks In ThisWorkbook.Worksheets
        With wks
            If .Name Like SHEETNAME Then
                With .Cells
                    LastCell(1) = .find("*", , , , xlByRows, xlPrevious).row
                    LastCell(2) = .find("*", , , , xlByColumns, xlPrevious).Column
                End With
                With .Cells(1, 1).Resize(LastCell(1), LastCell(2))
                    .Value = .Value
                End With
                Erase LastCell
            End If
        End With
    Next wks
    
    Application.ScreenUpdating = True
    
End Sub