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

 

[288enzo]

​

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

Sensitive information, sorry.

 

[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

Oddly it didn't work, but when I changed ThisWorkbook.Worksheets(Sht) to Sheets(Sht) it did.

    Dim Sht As Variant
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With Sheets(Sht)
            .Range("B4:E20").Value = .Range("B4:E20").Value
            .Columns("F:Q").Delete
        End With
    Next

 

[GWteB]

I should have noticed that, glad you solved it. Well done!

 

[288enzo]

I should have noticed that, glad you solved it. Well done!

Thank you, I'm really enjoying all that I'm learning.

I do have a question though. I tried adding in

.Range("A1:E12").Select

to the code and am getting an error. Select method of Range class failed

Curious, why doesn't this work?

Dim Sht As Variant
            For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
                With Sheets(Sht)
                    .Range("B4:E20").Value = .Range("B4:E20").Value
                    .Columns("F:Q").Delete
                    .Rows("13:20").Delete
                    .Range("A1:E12").Select
                End With
            Next

 

[GWteB]

You can only select ranges (or shapes on your sheets) if that particular sheet is the active sheet, meaning it's displayed on your screen. There can be just one active sheet at the time. In the provided code there's nothing that's been activated or selected, so it depends what's manually happened before the macro runs which sheet is the active sheet.
That said, it's almost never necessary to perform a Select method in your code.

 

[288enzo]

You can only select ranges (or shapes on your sheets) if that particular sheet is the active sheet, meaning it's displayed on your screen. There can be just one active sheet at the time. In the provided code there's nothing that's been activated or selected, so it depends what's manually happened before the macro runs which sheet is the active sheet.
That said, it's almost never necessary to perform a Select method in your code.

The intent was that when all was said and done the applicable information on each sheet would already be selected for the user to quickly copy and paste into an email if they desired.

 

[GWteB]

Then you require an extra line:

                   .Rows("13:20").Delete
                   .Activate
                   .Range("A1:E12").Select

 

[288enzo]

Then you require an extra line:

                   .Rows("13:20").Delete
                   .Activate
                   .Range("A1:E12").Select

That's great, thank you.

Hopefully you can help me because I'm coming across something very odd.

When 80 or 87 is selected the following works:

    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With Sheets(Sht)
        .Range("B4:E20").Value = .Range("B4:E20").Value
        .Columns("F:Q").Delete
        End With
    Next

But when 82, 83, or 84 are selected then it doesn't; however it does for Sheets4, 5, and 6.

Option Explicit
Sub Start()
    Dim strArea As String
    Dim strQtr As String
    Dim x As Long
    Dim Sht As Variant
    strArea = InputBox("Area? (80,82,83,84,87)")

    Select Case strArea
        Case 80, 87
    For x = 1 To 3
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Sheet" & x
    Next
        Case 82, 83, 84
    For x = 1 To 6
        ActiveSheet.Copy After:=Sheets(Sheets.Count)
        ActiveSheet.Name = "Sheet" & x
    Next
    End Select
    Select Case strArea
        Case 82
            Sheets("Sheet1").Rows("13:27").Delete
            Sheets("Sheet2").Rows("13:27").Delete
            Sheets("Sheet3").Rows("13:27").Delete
            Sheets("Sheet4").Rows("4:12").Delete
            Sheets("Sheet5").Rows("4:12").Delete
            Sheets("Sheet6").Rows("4:12").Delete
        Case 83, 84
            Sheets("Sheet1").Rows("17:26").Delete
            Sheets("Sheet2").Rows("17:26").Delete
            Sheets("Sheet3").Rows("17:26").Delete
            Sheets("Sheet4").Rows("4:16").Delete
            Sheets("Sheet5").Rows("4:16").Delete
            Sheets("Sheet6").Rows("4:16").Delete
      End Select
    
    Sheets("Sheet1").Select
        Call Quarterly_Learning_ALL(strArea, strQtr)
        Range("A2") = "=A4 & "" Overview"""
        Range("A2").Font.Color = RGB(192, 0, 0)
    Sheets("Sheet2").Select
        Call ABE(strArea)
        Range("A2") = "=A4 & "" ABE"""
        Range("A2").Font.Color = RGB(192, 0, 0)
    Sheets("Sheet3").Select
        Call vILT(strArea)
        Range("A2") = "=A4 & "" vILT"""
        Range("A2").Font.Color = RGB(192, 0, 0)

' not working when 82, 83, or 84 is selected.  Meaning, values aren't being pasted and columns are not deleted
    For Each Sht In Array("Sheet1", "Sheet2", "Sheet3")
        With Sheets(Sht)
        .Range("B4:E20").Value = .Range("B4:E20").Value
        .Columns("F:Q").Delete
        End With
    Next
' **************************************************        

    Select Case strArea
        Case 82, 83, 84
            Sheets("Sheet1").Select
             Call Quarterly_Learning_ALL(strArea, strQtr)
              Range("A2") = "=A4 & "" Overview"""
              Range("A2").Font.Color = RGB(192, 0, 0)
            Sheets("Sheet2").Select
              Call ABE(strArea)
              Range("A2") = "=A4 & "" ABE"""
              Range("A2").Font.Color = RGB(192, 0, 0)
            Sheets("Sheet3").Select
              Call vILT(strArea)
              Range("A2") = "=A4 & "" vILT"""
              Range("A2").Font.Color = RGB(192, 0, 0)
            
            For Each Sht In Array("Sheet4", "Sheet5", "Sheet6")
              With Sheets(Sht)
               .Range("B4:E20").Value = .Range("B4:E20").Value
               .Columns("F:Q").Delete
            End With
            Next
    End Select
End Sub

 

[GWteB]

But when 82, 83, or 84 are selected then it doesn't; however it does for Sheets4, 5, and 6.

Unless you're about to telling us what you're trying to achieve, you're the only one that knows.
Without insight in your data and without knowing what the expected result is no one is able to help you.

 

[288enzo]

Unless you're about to telling us what you're trying to achieve, you're the only one that knows.
Without insight in your data and without knowing what the expected result is no one is able to help you.

Thank you anyhow, I'll keep playing around with it. Really is driving me crazy though. Have a great day!