Copy certain columns from all the files and paste in a Summary workbook

Yes its possible.

Do you want the name of the file from where the data was copied?

I believe this macro will need to reside in the summary file and i assume that the header will already be present. So i would only need to help you populate the rest of the data, right?

Search for "Looping through workbooks in a folder" to get you started.

Try this code

Option Explicit
Sub cons_data()

Dim Master As Workbook
Dim sourceBook As Workbook
Dim sourceData As Worksheet
Dim CurrentFileName As String
Dim myPath As String

Application.ScreenUpdating = False

'The folder containing the files to be recap'd
myPath = "D:\Test"

'Finds the name of the first file of type .xls in the current directory
CurrentFileName = Dir(myPath & "\*.xls")

'Create a workbook for the recap report
Set Master = ThisWorkbook

Do
    Workbooks.Open (myPath & "\" & CurrentFileName)
    Set sourceBook = Workbooks(CurrentFileName)
    Set sourceData = sourceBook.Worksheets(1)
    
        With sourceData
            lrow = .Range("B" & .Rows.Count).End(xlUp).Row
            .Range("B2:C" & lrow).Copy Master.Worksheets(1).Range("B" & Rows.Count).End(xlUp).Offset(1, 0)
            .Range("N2:O" & lrow).Copy Master.Worksheets(1).Range("N" & Rows.Count).End(xlUp).Offset(1, 0)
            .Range("Z2:Z" & lrow).Copy Master.Worksheets(1).Range("Z" & Rows.Count).End(xlUp).Offset(1, 0)
            .Range("AP2:AQ" & lrow).Copy Master.Worksheets(1).Range("AP" & Rows.Count).End(xlUp).Offset(1, 0)
        End With
       
    sourceBook.Close
  
'Calling DIR w/o argument finds the next .xlsx file within the current directory.
CurrentFileName = Dir()
Loop While CurrentFileName <> ""

MsgBox "Consolidation complete"

Application.ScreenUpdating = True

End Sub

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

Change

CurrentFileName = Dir(myPath & "\*.xls")

to

CurrentFileName = Dir(myPath & "\*.xlsx")

Np ...If you are satisfied with the solution(s) provided, please mark your thread as Solved.

Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

What error are you getting?

Ok, yes, you have to declare dim lrow as long.

Can you attach the file or a sample of what you are using?