VBA to pull data from multiple excel sheets and multiple workbooks to a different consolidated worksheet

[lcaindoy]

Hi guys,

Hope you can help me out, I'm a newbie here but I'm always looking for ways to reduce amount of time in getting data.

What I'm try to do right now is to pull worksheet data with same worksheet name from different workbooks and consolidate into a different workbook one a worksheet.

 

[lcaindoy]

I had a look at the one that gave you a syntax error, https://chandoo.org/wp/consolidate-data-from-different-excel-files-vba/

It works fine for me. Please can you re-look at it & advise where the error is?
It may be a simple set up issue - like the worksheet name?

I have modified it a tiny bit to suit what I guess you may have. Description follows, based on a download from Chandoo's site. The sample file is set up to include the names of the cells to be copied from each of the files. Such as A2 to G10 for the first record. I'm guessing this would be better if the code worked out the cells to copy. So I've edited the code. The bit I've changed is within the "========" marks. Also I've set within the code the name of the worksheet you're copying from. This would be better in the table that defines what is happening but I've taken the expedient (lazy really) option & simply hard coded it into the code - rather than modify both the table and the code.

        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True        Set dataWB = ActiveWorkbook
        
'=================
        'Range(strCopyRange).Select 'I've edited this row & replaced it by the four following
         dataWB.Worksheets("Master").Activate
        With Range("A1").CurrentRegion
            .Offset(1).Resize(.Rows.Count - 1).Select
        End With
'=================
        Selection.Copy
        
        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select

To summarise,
1. download the file from Chandoo's site
2. fill in the table to suit your file names & worksheet names
3. edit the code per above

If it doesn't do what you want, please advise specifics. thanks, Fazza

Hi Fazza,

I understand now after downloading, I needed other things than that of chandoo. The ff are.
1. My data in each worksheet sources has no specific end row. For Chandoo's excel, in Column E, he is specifying an end cell to be copied. My data varies every month so it have lesser or more number of rows on next month. I don't know if it is possible to have a code where I do not need to limit or give a specified Data range end cell.
2. For Copy to sheet Column G in Chandoo's excel file, my number of rows varies for each month, so I wanted to copy data of the next worksheet after the last row data of the first worksheet.
3. For Copy to sheet Column F in Chandoo's excel file, just to add an information I want to copy all sheets in one consolidating sheets. For example in chandoo's excel I want it all in MasterData Worksheet

Thank you for responding on my queries although I know it is somehow unclear.

 

[Fazza]

your latest points
1. this was covered in my posting by the edit I did to the VBA. It should do what you described. You can leave column E blank
2. that doesn't matter. it does what you want without modification
3. then enter "MasterData" in every row for the seven files.

so, Chandoo's file as modified by my post should do everything. OK?

 

[lcaindoy]

your latest points
1. this was covered in my posting by the edit I did to the VBA. It should do what you described. You can leave column E blank
2. that doesn't matter. it does what you want without modification
3. then enter "MasterData" in every row for the seven files.

so, Chandoo's file as modified by my post should do everything. OK?

Hi Fazza,

Currently this is the coding as per you edited coding and chandoo combined.

Public strFileName As String
Public currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String
Sub GetData()
Dim strWhereToCopy As String, strStartCellColName As String
Dim strListSheet As String

strListSheet = "List"

On Error GoTo ErrH
Sheets(strListSheet).Select
Range("B2").Select

'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
Set currentWB = ActiveWorkbook
Do While ActiveCell.Value <> ""

strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
strWhereToCopy = ActiveCell.Offset(0, 4).Value
strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)

Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
Set dataWB = ActiveWorkbook

Range(strCopyRange).Select
Selection.Copy

currentWB.Activate
Sheets(strWhereToCopy).Select
lastRow = LastRowInOneColumn(strStartCellColName)
Cells(lastRow + 1, 1).Select

Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
Application.CutCopyMode = False
dataWB.Close False
Sheets(strListSheet).Select
ActiveCell.Offset(1, 0).Select
Loop
Exit Sub

ErrH:
MsgBox "It seems some file was missing. The data copy operation is not complete."
Exit Sub
End Sub
Public Function LastRowInOneColumn(col)
'Find the last used row in a Column: column A in this example
'http://www.rondebruin.nl/last.htm
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
LastRowInOneColumn = lastRow
End Function



Problem now is when I run it, it's not finding my file path

 

[Fazza]

1. how do you know it is not finding your file path?

the code posted is not correct?? I don't see the edit I proposed

Public strFileName As StringPublic currentWB As Workbook
Public dataWB As Workbook
Public strCopyRange As String


Sub GetData()
    Dim strWhereToCopy As String, strStartCellColName As String
    Dim strListSheet As String
    
    strListSheet = "List"
    
    On Error GoTo ErrH
    
    Application.ScreenUpdating = False
    
    Sheets(strListSheet).Select
    Range("B2").Select
    
    'this is the main loop, we will open the files one by one and copy their data into the masterdata sheet
    Set currentWB = ActiveWorkbook
    Do While ActiveCell.Value <> ""
        
        strFileName = ActiveCell.Offset(0, 1) & ActiveCell.Value
        strCopyRange = ActiveCell.Offset(0, 2) & ":" & ActiveCell.Offset(0, 3)
        strWhereToCopy = ActiveCell.Offset(0, 4).Value
        strStartCellColName = Mid(ActiveCell.Offset(0, 5), 2, 1)
        
        Application.Workbooks.Open strFileName, UpdateLinks:=False, ReadOnly:=True
        Set dataWB = ActiveWorkbook
        
'=================
        dataWB.Worksheets("Master").Activate
        'Range(strCopyRange).Select 'I've edited this row & replaced it by the three following
        With Range("A1").CurrentRegion
            .Offset(1).Resize(.Rows.Count - 1).Select
        End With
'=================
        Selection.Copy
        
        currentWB.Activate
        Sheets(strWhereToCopy).Select
        lastRow = LastRowInOneColumn(strStartCellColName)
        Cells(lastRow + 1, 1).Select
        
        Selection.PasteSpecial xlPasteValues, xlPasteSpecialOperationNone
        Application.CutCopyMode = False
        dataWB.Close False
        Sheets(strListSheet).Select
        ActiveCell.Offset(1, 0).Select
    Loop
    Exit Sub
    
ErrH:
    MsgBox "It seems some file was missing. The data copy operation is not complete."
    Exit Sub
End Sub


Public Function LastRowInOneColumn(col)
    'Find the last used row in a Column: column A in this example
    'http://www.rondebruin.nl/last.htm
    Dim lastRow As Long
    With ActiveSheet
    lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
    End With
    LastRowInOneColumn = lastRow
End Function

 

[Fazza]

it seems like we're going around in circles a little

this should be as simple as Chandoo's post with one modification

please re-start at the post that had that & retry from there

 

[lcaindoy]

it seems like we're going around in circles a little

this should be as simple as Chandoo's post with one modification

please re-start at the post that had that & retry from there

I have pasted the above code. This is the prompt error message

Sorry, we couldn't find 'H:\Balance FolderBalance1.xlsx'. Is it possible it was moved, renamed or deleted?

File path: H:\Balance Folder
File name: Balance1

I think it could not find because the path because the file path and the file name is combined into 1

 

[Fazza]

Great. That is very clear. Thank you

See Chandoo's file. It has column "C" with full path "C:\test"
You need to set up your copy of Chandoo's file for Chandoo's code to work.
So in column "C" I'd expect "H:\Balance Folder"
Not "H:\Balance Folder" as posted above.

BTW, similarly, column "B" to contain "Balance1.xlsx". Not "Balance1"

OK?

If that doesn't work, please open the file "Balance1..." and in VBA's immediate window enter "? activeworkbook.fullname"
and post back with the results. thanks

 

[Fazza]

I see errors in my post. I don't know how I did that! Revised below

See Chandoo's file. It has column "C" with full path "C:\test\"
You need to set up your copy of Chandoo's file for Chandoo's code to work.
So in column "C" I'd expect "H:\Balance Folder\"
Not "H:\Balance Folder" as posted above.

 

[lcaindoy]

I see errors in my post. I don't know how I did that! Revised below

See Chandoo's file. It has column "C" with full path "C:\test\"
You need to set up your copy of Chandoo's file for Chandoo's code to work.
So in column "C" I'd expect "H:\Balance Folder\"
Not "H:\Balance Folder" as posted above.

Hi Fazza,

All is good now, thank you very much for your help

 

[lcaindoy]

Couldn't have worked out without your patience. Thank you for sharing you knowledge