Adding sheet name to this code

mittmo

New Member
Joined
Aug 18, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I'm working with this code another user created a few years ago that extracts data from multiple sheets in a folder to another single workbook. Problem is that I'm not working off of the first sheet, but the 5th sheet. How do I do add that sheet name to this code?

VBA Code:
Public Sub Copy_Values_From_Workbooks()

    Dim matchWorkbooks As String
    Dim destSheet As Worksheet, r As Long
    Dim folderPath As String
    Dim wbFileName As String
    Dim fromWorkbook As Workbook
  
    'Folder path and wildcard workbook files to import cells from
  
    matchWorkbooks = "C:\folder\path\*.xls"                                             'CHANGE THIS
  
    'Define destination sheet
  
    Set destSheet = ActiveWorkbook.Worksheets("Summary")                                'CHANGE THIS
  
    destSheet.Cells.Clear
    r = 0
  
    Application.ScreenUpdating = False
          
    folderPath = Left(matchWorkbooks, InStrRev(matchWorkbooks, "\"))
    wbFileName = Dir(matchWorkbooks)
    While wbFileName <> vbNullString
        Set fromWorkbook = Workbooks.Open(folderPath & wbFileName)
        With fromWorkbook.Worksheets(1)
            destSheet.Range("B8:G8").Offset(r).Value = .Range("Q10:V10").Value
            destSheet.Range("H8:M8").Offset(r).Value = .Range("Q13:V13").Value
            destSheet.Range("N8:S8").Offset(r).Value = .Range("Q16:V16").Value
            destSheet.Range("A8").Offset(r).Value = .Range("B14").Value
            r = r + 1
        End With
        fromWorkbook.Close savechanges:=False
        DoEvents
        wbFileName = Dir
    Wend
  
    Application.ScreenUpdating = True
  
    MsgBox "Finished"
  
End Sub
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
Sorry your question doesn't make sense.
The code below already selects the first worksheet in a workbook. If the worksheet you want to copy is in 5th worksheet then obviously it would extract data from wrong worksheet.
Please review your workbook structure.
VBA Code:
With fromWorkbook.Worksheets(1)

Hope it makes sense.

Biz
 
Upvote 0
You are currently accessing the sheet using the Index no in the line below.
(The index no is effectively the position in the workbook with the sheet furthest to the left being 1 )
VBA Code:
        With fromWorkbook.Worksheets(1)

Simply substitute the index number for the name of the worksheet enclosing it in Quotation marks (")
eg if the sheet name is MySheet then it becomes
VBA Code:
        With fromWorkbook.Worksheets("MySheet")
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top