repeat procedure on three sheets

[288enzo]

I have a bit of code that I need to use with Sheet1, Sheet2, and Sheet3.
Is there a more efficient way of doing it without having to select each sheet individually?

Thank you

    Sheets("Sheet1").Select
    Range("B4:E20").Copy
    Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    Columns("F:Q").Delete
    Select Case strArea
    Case 83
    Rows("20").Delete
    End Select
    Sheets("Sheet2").Select
    Range("B4:E20").Copy
    Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    Columns("F:Q").Delete
    Select Case strArea
    Case 83
    Rows("20").Delete
    End Select
    Sheets("Sheet3").Select
    Range("B4:E20").Copy
    Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    Columns("F:Q").Delete
    Select Case strArea
    Case 83
    Rows("20").Delete
    End Select

 

[My Aswer Is This]

Try this:
I assume str

Sub My_Script()
'Modified  9/30/2021  6:41:22 PM  EDT
Application.ScreenUpdating = False

With Sheets("Sheet1")
    .Range("B4:E20").Copy
    .Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    .Columns("F:Q").Delete
    Select Case strArea
    Case 83
        .Rows("20").Delete
    End Select
    End With
   
    With Sheets("Sheet2")
     .Range("B4:E20").Copy
     .Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    .Columns("F:Q").Delete
   
    Select Case strArea
        Case 83
        .Rows("20").Delete
    End Select
    End With
   
    With Sheets("Sheet3")
        .Range("B4:E20").Copy
        .Range("B4:E20").PasteSpecial Paste:=xlPasteValues
        .Columns("F:Q").Delete
            Select Case strArea
                Case 83
                    .Rows("20").Delete
            End Select
  End With
Application.ScreenUpdating = True
 
  End Sub

 

[288enzo]

Try this:

Sub My_Script()
'Modified  9/30/2021  6:41:22 PM  EDT
Application.ScreenUpdating = False

With Sheets("Sheet1")
    .Range("B4:E20").Copy
    .Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    .Columns("F:Q").Delete
    Select Case strArea
    Case 83
        .Rows("20").Delete
    End Select
    End With
   
    With Sheets("Sheet2")
     .Range("B4:E20").Copy
     .Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    .Columns("F:Q").Delete
   
    Select Case strArea
        Case 83
        .Rows("20").Delete
    End Select
    End With
   
    With Sheets("Sheet3")
        .Range("B4:E20").Copy
        .Range("B4:E20").PasteSpecial Paste:=xlPasteValues
        .Columns("F:Q").Delete
            Select Case strArea
                Case 83
                    .Rows("20").Delete
            End Select
  End With
Application.ScreenUpdating = True
 
  End Sub

I like what you did, you taught me a few new tricks. Thank you. What I thought could be done was something like-
Not any sort of VB here, just thinking out loud

For Sheets1,Sheets2,Sheets3
Do
.Range("B4:E20").Copy
.Range("B4:E20").PasteSpecial Paste:=xlPasteValues
.Columns("F:Q").Delete
Select Case strArea
Case 83
.Rows("20").Delete
End Select

Thank you

 

[My Aswer Is This]

I like what you did, you taught me a few new tricks. Thank you. What I thought could be done was something like-
Not any sort of VB here, just thinking out loud

For Sheets1,Sheets2,Sheets3
Do
.Range("B4:E20").Copy
.Range("B4:E20").PasteSpecial Paste:=xlPasteValues
.Columns("F:Q").Delete
Select Case strArea
Case 83
.Rows("20").Delete
End Select

Thank you

I assume strArea is a named range
Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[GWteB]

How about ...

Sub Example288enzo()
    Dim Sht As Variant
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With ThisWorkbook.Worksheets(Sht)
            .Range("B4:E20").Value = .Range("B4:E20").Value
            .Columns("F:Q").Delete
            Select Case strArea
            Case 83
                .Rows("20").Delete
            End Select
        End With
    Next
End Sub

 

[My Aswer Is This]

I'm curious.
Why are you using a case statement when your always using 83

 

[My Aswer Is This]

How about ...

Sub Example288enzo()
    Dim Sht As Variant
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With ThisWorkbook.Worksheets(Sht)
            .Range("B4:E20").Value = .Range("B4:E20").Value
            .Columns("F:Q").Delete
            Select Case strArea
            Case 83
                .Rows("20").Delete
            End Select
        End With
    Next
End Sub

I will say this is a nicer way.

 

[GWteB]

In this case the OP could have used

If strArea = 83 then
    .Rows("20").Delete
End If

but since we don't have al the OP's code, we do not know what strArea is referring to.

 

[My Aswer Is This]

In this case the OP could have used

If strArea = 83 then
    .Rows("20").Delete
End If

but since we don't have al the OP's code, we do not know what strArea is referring to.

Yes I know about case statements but he used case 38 several time in the script as only once would have worked it looks like to me.

 

[GWteB]

he used case 38 several time in the script as only once would have worked it looks like to me.

Then you're missing the point of it all, since the OP was looking for more densed code to act on three different sheets, so each time there's a check needed to see whether row 20 on that particular sheet has to be deleted or not.