VBA for copy/paste values of all workbooks in a folder

[trentbaby9]

I'm trying to find and figure out the vba code needed to copy selected cells from all workbooks in a folder and paste the values only into the new "master summary" workbook, incrementing each pasted value to the next available line. I've tried several different codes but still can't seem to get any of them to work.

Folder = Test Folder
Files in Folder: file1.xlsx, file2.xlsx, file3.xlsx
Each file has multiple worksheets, but I only need to copy five cells (A11, B11, C11, D11, E11) from the worksheet labeled Summary.

I then need to paste values only into the "master summary" workbook on the next available line so that no data is overwritten.

Any help is greatly appreciated!

 

[Fluff]

Ok, in that case will A11 on the summary sheets always have a value? Or can it sometimes be blank?

 

[trentbaby9]

Any of the cells can sometimes be blank or zero.

Eventually, all of the A11 cells will have a name in them, but for now, only one of them does.

 

[Fluff]

In that case how about

Sub trentbaby()
   Dim Pth As String, Fname As String
   Dim Wbk As Workbook
   Dim ws As Worksheet
   Dim NxtRw As Long
   
   Application.ScreenUpdating = True
   Pth = "C:\MrExcel\Fluff\"
   Fname = Dir(Pth & "*.xls*")
   Set ws = ThisWorkbook.Sheets("Sheet1")
   NxtRw = ws.Cells.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Offset(1).Row
   Do While Fname <> ""
      Set Wbk = Workbooks.Open(Pth & Fname)
      ws.Range("A" & NxtRw).Resize(, 5).Value = Wbk.Sheets("Summary").Range("A11:E11")
      NxtRw = NxtRw + 1
      Wbk.Close False
      Fname = Dir()
   Loop
End Sub

 

[trentbaby9]

That one didn't bring in any data.

 

[Fluff]

I forgot to add the Value part again

      ws.Range("A" & NxtRw).Resize(, 5).Value = Wbk.Sheets("Summary").Range("A11:E11")[COLOR=#ff0000].Value[/COLOR]

 

[trentbaby9]

Lol, adding the .Value worked! I am so grateful to you Fluff; it works exactly how I needed it to!

Thanks for taking the time to help me

 

[Fluff]

Glad to help & thanks for the feedback

 

[MKZ]

Alright, I think it might be close.
On my 2021 Excel, I'm running into :

Run-time error '91':
Object variable or With block variable not set

NxtRw = ws.Cells.Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Offset(1).Row

Would you have any recommendations on this one?

 

[MKZ]

This issue will take place if the destination workbook is fully blank