Copy data from multiple sheets and paste into a single one

[gimbox16]

Hello,
I'm very new to VBA and I've seen that there are many similar questions to mine, but none fits exactly my needs.
I have a workbook with several sheets; my main sheet "Final" has in column A all the names of all the other sheets (e.g. 18MP2, 23ST4, etc). The sheets with such names all have the same structure. I would like to copy from each of these sheets the range A2:G2 and paste it in the sheet "Final" in the range B:G according to the match between the value in col A (of sheet "Final") and the name of the sheet from which the data is copied. Below you can see a pic of how the workbook looks.

I'd appreciate any help!

 

[davesexcel]

If you are copying A:G then the paste rand would be B:H?

I used the sheet named "Final" as shown in your description
We should also make sure the sheet exists.

Sub Get_R()
    Dim sh As Worksheet
    Dim rng As Range, c As Range

    Set sh = Sheets("Final")
    With sh
        Set rng = .Range("A2:A" & .Cells(.Rows.Count, "A").End(xlUp).Row)
        For Each c In rng.Cells
            If SheetExists(c.Value) Then
                c.Offset(, 1).Resize(, 7) = Sheets(c.Value).Range("A2:G2").Value
            Else
                c.Offset(, 1) = c & " does not exist"
            End If
        Next c
    End With
End Sub

Function SheetExists(SheetName As String)
    On Error GoTo no:
    WorksheetName = Worksheets(SheetName).Name
    SheetExists = True
    Exit Function
no:
    SheetExists = False
End Function

 

[mohadin]

Hi,
What about

Sub test()
Dim i
Dim a, b As Variant
    With Sheets("Final")
        a = .Cells(2, 1).Resize(.Cells(Rows.Count, 1).End(xlUp).Row - 1)
        ReDim b(1 To UBound(a))
        For i = 1 To UBound(a)
            With Sheets(a(i, 1))
                b(i) = .Cells(2, 1).Resize(, 7)
            End With
        Next
        .Cells(2, 2).Resize(UBound(a), 7) = Application.Transpose(Application.Transpose(b))
    End With
End Sub