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 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,

Last request, could you include a code that will prompt a message that the generation of data is already complete.

 

[Fazza]

glad you sorted it out. well done

I don't know exactly what is wanted regarding a message

if it is simply advising job done, then a new line

msgbox Prompt:="Generation of data is complete", Buttons:=vbinformation, Title:="Job done"

if it is conditional somehow, such as something has to be checked, please advise details.

there might be other changes too
- clear old data before reading in new data
- add an extra column or two to the results that references where the data came from or when it was loaded, etc
- report how many records are returned (this could be a countif formula next to the table that defines what to do)
- etc, etc

 

[lcaindoy]

glad you sorted it out. well done

I don't know exactly what is wanted regarding a message

if it is simply advising job done, then a new line

msgbox Prompt:="Generation of data is complete", Buttons:=vbinformation, Title:="Job done"

if it is conditional somehow, such as something has to be checked, please advise details.

there might be other changes too
- clear old data before reading in new data
- add an extra column or two to the results that references where the data came from or when it was loaded, etc
- report how many records are returned (this could be a countif formula next to the table that defines what to do)
- etc, etc

Hi Fazza,

I think it would be good if there is clear old date reading in new data, is this possible? or too much of a hassle? if it is I can just manually remove old data.

 

[Fazza]

not a hassle
it is the sort of thing that should be in code
in fact generally, have the code do as much as possible

revisions near top of code. cheers

    strListSheet = "List"    
    On Error GoTo ErrH
    
    '==================
    'you might add this too, to stop screen flickering & speed up execution
    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
    
    '==================
    'Added 18-Jun-18 to clear old data from "Master" worksheet
    currentWB.Worksheets("Master").Range("A1").CurrentRegion.Offset(1).ClearContents
    '==================
    
    Do While ActiveCell.Value <> ""

 

[lcaindoy]

not a hassle
it is the sort of thing that should be in code
in fact generally, have the code do as much as possible

revisions near top of code. cheers

    strListSheet = "List"    
    On Error GoTo ErrH
    
    '==================
    'you might add this too, to stop screen flickering & speed up execution
    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
    
    '==================
    'Added 18-Jun-18 to clear old data from "Master" worksheet
    currentWB.Worksheets("Master").Range("A1").CurrentRegion.Offset(1).ClearContents
    '==================
    
    Do While ActiveCell.Value <> ""

Hi Fazza,

Like earlier, thank you for making this work possible. Cheers!

 

[Fazza]

you're welcome