VBA to combine the first sheet of numerous excel files into one master document

Hi,

I wonder if anyone could please help

I have a number of excel files (approx ten, maybe a few more) which each contain a summary sheet on the first tab. (They all have different names but if need be I could rename them Sheet1)

Each summary sheet has a graph, commentary, key stats etc and the idea is to easily combine all the summary pages into one combined report.

I only want the first sheet of each document within the master document (the other data source tabs are not required)

All files are stored in the same folder.

My plan was to put them all in the same folder, create a new excel document containing vba to complete this function.

As an vba novice however, this is proving far more difficult than first anticipated!

If anyone could help in anyway it would be most appreciated!

Many thanks in advance

Thankyou so much for your quick answer! and thankyou for taking the time to help!

I've changed the file path but it says 0 sheets copied. I'm not sure where I could be going wrong? Any ideas?

Sub Consolidate_Summary_Sheets()

Dim strPath As String, strFile As String, counter As Long

strPath = "C:\Users\u25\Desktop\New folder\"
strFile = Dir(strPath & "*.xls*")

Application.ScreenUpdating = False
Do While strFile <> ""
With Workbooks.Open(strPath & strFile)
.Sheets(1).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
ThisWorkbook.ActiveSheet.UsedRange.Value = ThisWorkbook.ActiveSheet.UsedRange.Value
.Close SaveChanges:=False
End With
counter = counter + 1
strFile = Dir
Loop
Application.ScreenUpdating = True

MsgBox counter & " sheets copied. ", vbInformation, "Copy Summary Sheets Complete"

End Sub

Thankyou for all your help and taking the time to help me.

Unfortunately it still doesnt work.

I have all files in one folder.
The vba in a new excel document within this folder.
Different names on the sheet first pages.
The documents saved in Microsoft Excel Worksheet (.xlsx) format

could any of these affect the result do you know?

I changed the name of the file format and it works! awesome! thankyou so much for your help!