Macro for specific worksheets

Hi

I'm trying to amend my macro to only get data from a specific worksheet in multiple workbooks in a specific folder.

I have very basic knowledge so am lost with it.

This is the first bit of my code which currently means all worksheets in all workbooks are accessed rather than "sheet 1" which is all I want.

Any help much appreciated.

Sub allworkbooks()
Dim MySummary As Workbook
Dim MySummarySheet As Worksheet
Dim ThisSheet As Worksheet
Dim Thisbook As String
Dim SumRowCt As Long
Dim MyPath As String
On Error Resume Next
MyPath = "c:\Users\Claire\"

You could try this:

Public Sub AllWorkbooks()

          Dim wkbSrc As Workbook
          Dim wkbDest As Workbook: Set wkbDest = ActiveWorkbook ' assumes currently active workbook is where the data is going
          Dim wksSrc As Worksheet
          Dim wksDest As Worksheet: Set wksDest = ActiveSheet ' assumes currently active worksheet is where the data is going to be pasted into
          Dim strFilePath As String, strFileExtension As String, strFileName As String, strSheetName As String
          
          strFilePath = "C:\Users\Claire\" ' the Specific Folder
          strFileExtension = ".xlsx" ' assumes the source files ("the Multiple Workbooks") all have the .xlsx extension. Change if necessary.
          strFileName = Dir(strFilePath & "*" & strFileExtension)
          
          strSheetName = "Sheet1" ' this is the name of the specific worksheet that occurs in the multiple workbooks in the Specific Folder
          
          Do While Len(strFileName) > 0
                    
                    Set wkbSrc = Workbooks.Open(strFilePath & strFileName)
                    Set wksSrc = wkbSrc.Sheets(strSheetName)

                    ' insert your code here that moves data from wksSrc to wksDest

                    wkbSrc.Close False
                    strFileName = Dir
          
          Loop
          
End Sub