Get Values from Cells from other sheets

Question

I was make a small VBA code which get me a list with all sheets names from my workbook, My question is:
How can I get values from Sheet7 ("B4:B5"),Sheet8 ("B4:B5"),Sheet ("B4:B5") ..... into Sheet6 which is present the following code:

Private Sub Worksheet_Activate()
 Call task
End Sub
Sub task()
Call ThisWorkbook.speedOn
    Dim pws As Worksheet
    Dim x, N As Integer
    Dim browLastCol As Range
    Set pws = Sheets("Sheet6")
    'Set browLastCol = pws.Range(Cells(3, 4), Cells(3, 4).End(xlToRight))
    Set browLastCol = pws.Range(Cells(9, 2), Cells(9, 2).End(xlDown))
    
        x = 9
        
        pws.Range(Cells(3, 2), Cells(3, 2).End(xlToRight)).Clear

 
   For Each ws In Worksheets
    If ws.Name <> "Sheet1" Then
     If ws.Name <> "Sheet2" Then
       If ws.Name <> "Sheet3" Then
            If ws.Name <> "Sheet4" Then
             If ws.Name <> "Sheet5" Then
               If ws.Name <> "Sheet6" Then
               
                    Sheets("Sheet6").Cells(x, 2) = ws.Name ' List woksheets name 
                    
                     x = x + 1
                      Sheets("Sheet6").Range("A3").RowHeight = 40
                      
                      
                      With browLastCol
                               .RowHeight = 26
                               'Some code removed
                     End With
                      
                     
                 End If
                End If
             End If
         End If
      End If
   End If
Next ws
 Call ThisWorkbook.speedOff
End Sub

Possible duplicate of [Get values from other sheet using VBA](https://stackoverflow.com/questions/4148765/get-values-from-other-sheet-using-vba) — HackSlash, Sep 30 '19 at 16:27
Welcome to StackOverflow. You should find the answer to this question already exists in multiple places. Please read this blog post on how to ask a question: https://stackoverflow.com/help/how-to-ask — HackSlash, Sep 30 '19 at 16:29

score 1 · Accepted Answer · answered Sep 30 '19 at 16:31

Others have already shared a duplicate solution, but I would consider using Select Case here to get rid of all of those IF statements.

For Each ws in Worksheets
    Select Case ws.Name
        Case "Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6"
            'Do Nothing (leave this blank)
        Else
            Sheets("Sheet6").Cells(x, 2) = ws.Name 
            x = x + 1
            Sheets("Sheet6").Range("A3").RowHeight = 40

            With browLastCol
                .RowHeight = 26
                'Some code removed
            End With

    End Select
Next ws

Get Values from Cells from other sheets

1 Answers1