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

 

[Marc L]

(removed as I misread)

 

[Marc L]

Is there a more efficient way of doing it without having to select each sheet individually?

    Dim N%
    
    For N = 1 To 3
        With Sheets("Sheet" & N)
            .[B4:E20] = .[B4:E20]
            .Columns("F:Q").Delete
             If strArea = 83 Then .Rows(20).Delete
        End With
    Next

 

[288enzo]

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

I can understand the question, I hadn't include all only because I didn't think it was important. My mistake.

The full code that I'm trying to repeat over three sheets is -

    Range("B4:E20").Copy
    Range("B4:E20").PasteSpecial Paste:=xlPasteValues
    Columns("F:Q").Delete
    Range("G1").Font.Color = vbWhite
    Select Case strArea
    Case 80
    Rows("17:20").Delete
    Range("A1:E16").Select
    Case 82
    Rows("13:20").Delete
    Range("A1:E12").Select
    Case 83
    Rows("20").Delete
    Range("A1:E19").Select
    Case 84
    Rows("17:20").Delete
    Range("A1:E16").Select
    Case 87
    Rows("17:20").Delete
    Range("A1:E16").Select
    End Select

 

[288enzo]

    Dim N%
   
    For N = 1 To 3
        With Sheets("Sheet" & N)
            .[B4:E20] = .[B4:E20]
            .Columns("F:Q").Delete
             If strArea = 83 Then .Rows(20).Delete
        End With
    Next

This looks amazing. I have a few questions to better understand.

Dim N% - what does the % imply?

.[B4:E20] = .[B4:E20] - on the surface this looks like I'm simply copying and pasting over the same set of cells, what I'm trying to accomplish is copying a range of cells that is made up of formulas, and paste the end result (values). If this is what your code does, what in the code is telling it to paste the values? It's very interesting

If strArea = 83 Then .Rows(20).Delete - I hadn't included the full "Case" list. Here would I use ElseIf for all the others?

    Select Case strArea
    Case 80
    Rows("17:20").Delete
    Range("A1:E16").Select
    Case 82
    Rows("13:20").Delete
    Range("A1:E12").Select
    Case 83
    Rows("20").Delete
    Range("A1:E19").Select
    Case 84
    Rows("17:20").Delete
    Range("A1:E16").Select
    Case 87
    Rows("17:20").Delete
    Range("A1:E16").Select
    End Select

Thank you very much for your help on this

 

[288enzo]

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

This is definitely what I was asking for, and I'm happy to say that I actually understand most if it.

 

[288enzo]

    Dim N%
   
    For N = 1 To 3
        With Sheets("Sheet" & N)
            .[B4:E20] = .[B4:E20]
            .Columns("F:Q").Delete
             If strArea = 83 Then .Rows(20).Delete
        End With
    Next

.[B4:E20] = .[B4:E20] - this seems to be deleting everything in the range B4:E20

 

[GWteB]

This is definitely what I was asking for, and I'm happy to say that I actually understand most if it.

Glad to help and thanks for letting me know.

 

[Marc L]

Dim N% - what does the % imply?

Integer as you can check in VBA help …​

​

.[B4:E20] = .[B4:E20] - on the surface this looks like I'm simply copying and pasting over the same set of cells, what I'm trying to accomplish is copying a range of cells that is made up of formulas, and paste the end result (values)

.[B4:E20] = .[B4:E20] - this seems to be deleting everything in the range B4:E20

For safety you can add the properties like .[B4:E20].Formula = .[B4:E20].Value2 …​

​

If strArea = 83 Then .Rows(20).Delete - I hadn't included the full "Case" list. Here would I use ElseIf for all the others?

No, the better is to keep the Select Case block …​

​

 

[288enzo]

Integer as you can check in VBA help …​

​

For safety you can add the properties like .[B4:E20].Formula = .[B4:E20].Value2 …​

​

No, the better is to keep the Select Case block …​

​

Thank you for the breakdown.

I'm not sure what I'm doing wrong, but after adding the code it doesn't seem to be deleting the columns or changing the formulas to values.

Option Explicit
Sub Start()
    Dim strQtr As String
    Dim strArea As String
    Dim x As Long
    Dim Sht As Variant
    Dim N%

    For N = 1 To 3
        With Sheets("Sheet" & N)
        .[B4:E20].Formula = .[B4:E20].Value2
        .Columns("F:Q").Delete
        End With
    Next

 

[Marc L]

The better is you link the workbook on a files host website like Dropbox as I'm a very beginner in guessing anything …​