Pull values from other sheets to a summary sheet in workbook

Hi Marina

It is quite difficult to create a macro without seeing the source but I can be guessing what you are looking for, please see below

Option Explicit

Sub CopytoSummary()

Dim wks As Worksheet
Dim CopyRng As Range
Dim DestSht As Worksheet
Dim LastFreeColumn As Long
Dim DestRowNo As Byte
Dim c As Range

'below "with" speed up the macro
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With

'setting the destination as sheet1 you can use sheet name instead
Set DestSht = Sheet1

'looping through each wks in workbook
For Each wks In ActiveWorkbook.Worksheets
   
    'if wks is different than destination will go through
    If wks.Name <> DestSht.Name Then
        Set wks = wks
       
        'Select source range
        With wks
            Set CopyRng = .Range("A16:A30, K4, K8, J30")
        End With
       
       
        With DestSht
           
            'setting the last free destination column
            LastFreeColumn = .Cells(1, 1600).End(xlToLeft).Column + 1
            DestRowNo = 1
           
            'Copy eacjh cell to Summary
            For Each c In CopyRng
                c.Copy .Cells(DestRowNo, LastFreeColumn)
                DestRowNo = DestRowNo + 1
            Next c
       
        'column width
        .Columns(LastFreeColumn).ColumnWidth = 8
        End With
    End If

Next wks

'set the application back to normal
With Application
    .CutCopyMode = False
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub