Results 1 to 9 of 9

Pulling Data from Multiple workbooks to one consolidated sheet

Threaded View

  1. #1
    Registered User
    Join Date
    10-30-2018
    Location
    Sydney Australia
    MS-Off Ver
    Office 365
    Posts
    4

    Pulling Data from Multiple workbooks to one consolidated sheet

    So I'm trying to pull the data from a specific sheet in a list of work books into one master sheet. The data is in the sheet "Data", and per workbook there is one row and 12 columns. the current formula I have is(I've included the error next to it related line):
    Sub getDataFromWbs()

    Dim wb As Workbook, ws As Worksheet
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'This is where you put YOUR folder name
    Set fldr = fso.GetFolder("C:\Users\<My name>\Desktop\Temp\")
    
    'Next available Row on Master Workbook
    y = ThisWorkbook.Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1 <-------- For this line i keep getting runtime error '9', subscript out of range
    
    'Loop through each file in that folder
    For Each wbFile In fldr.Files
        
        'Make sure looping only through files ending in .xlsx (Excel files)
        If fso.GetExtensionName(wbFile.Name) = "xls" Then
          
          'Open current book
          Set wb = Workbooks.Open(wbFile.Path)
          
          'Loop through each sheet (ws)
          For Each ws In wb.Sheets
              'Last row in that sheet (ws)
              wsLR = ws.Cells(Rows.Count, 1).End(xlUp).Row
              
              'Loop through each record (row 2 through last row)
              For x = 2 To wsLR
                'Put column 1,2,3 and 4 of current sheet (ws) into row y of master sheet, then increase row y to next row
                ThisWorkbook.Sheets("Data").Cells(y, 1) = ws.Cells(x, 1) 'col 1
                ThisWorkbook.Sheets("Data").Cells(y, 2) = ws.Cells(x, 2) 'col 1
                ThisWorkbook.Sheets("Data").Cells(y, 3) = ws.Cells(x, 3) 'col 1
                ThisWorkbook.Sheets("Data").Cells(y, 4) = ws.Cells(x, 4) 'col 1
                y = y + 1
              Next x
              
              
          Next ws
          
          'Close current book
          wb.Close
        End If
    
    Next wbFile
    
    End Sub
    Last edited by Stokess; 10-30-2018 at 07:27 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Multiple workbooks consolidated into single master sheet
    By TRazzo in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-31-2018, 09:16 AM
  2. [SOLVED] VBA to extract data from multiple workbooks into one consolidated document
    By The_Snook in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-19-2018, 04:39 AM
  3. Pulling data from multiple workbooks into one
    By prosemur in forum Excel General
    Replies: 0
    Last Post: 03-25-2014, 10:30 AM
  4. Overall sheet pulling in info from multiple workbooks
    By Seancsn in forum Excel General
    Replies: 4
    Last Post: 04-10-2013, 10:02 AM
  5. Pulling Up Data From Multiple Workbooks
    By gedaliahr in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-24-2013, 01:07 PM
  6. Copy data from multiple workbooks into consolidated/master workbook
    By mobro1234 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2012, 02:42 PM
  7. Pulling Up Data From Multiple Workbooks
    By DomV in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-10-2009, 03:20 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1