Consolidating 3 tables (from 3 different workbooks) into a single table in a 4th Workbook

[Kaushik Joshi]

Bonjour !

Merci beaucoup....

Hope it works well with a table in the Main Consolidation sheet....so that I can perform some analysis or pick up data by index-match easily from there...

I managed the TRF sheet....

next I would like to put the entire code with my interpretation of what each line/action signifies... just for my understanding... and for anyone who wants to refer... I can see this thread being seen by people hundreds of times...

Thanks so very much!

 

[PCosta87]

Bonjour !

Merci beaucoup....

Hope it works well with a table in the Main Consolidation sheet....so that I can perform some analysis or pick up data by index-match easily from there...

I managed the TRF sheet....

next I would like to put the entire code with my interpretation of what each line/action signifies... just for my understanding... and for anyone who wants to refer... I can see this thread being seen by people hundreds of times...

Thanks so very much!

Don't mention it ... glad I could help

 

[Michael Molotzak]

ok to expand on this question, I am currently trying to write a script that would allow me to extract data from 26 separate tables from different sources, combine them into a single table that would update automatically along with placing the data in the correct order by placing each item at the bottom of it's category (inserting the updates rather than moving them to bottom of the updated table.) is this strategically possible?

 

[Michael Molotzak]

To Clarify, all 26 source wb's (1 workbook for each process) are housed in the same folder on the same shared drive. They are updated daily by numerous associates while performing their individual functions, and the updated info is consolidated for each process and manually uploaded into a single table that is used for production tracking and business sourcing percentage calculations. I am trying to automate the process so that when a new entry is submitted and a WB is saved and closed it will automatically update the main report and insert the data in the correct order at the bottom of the process. This way the directors that need the info have real time data.

 

[Kaushik Joshi]

@Michael, unable to contribute anything to the coding or design part... for being rookie!!!. Would only suggest to put up a model in of dummy Excel files, which is easier to visualise. Currently working on my model to implementing live....its fun n challenging enough for a me...uhh..rookie!

 

[Kaushik Joshi]

@costa
Bonjour !
I understood the method of identifying last row with data and then mark the range up to that last row. for first sheet it pastes data from cell A1 in Consolidation sheet. For subsequent sheet we offset 1 row after lrow on consolidation sheet and copy data identified by A1 : whatever column upto lrow1, lrow2, lrow3 and so on for as many number of files we have.

Question one:
Why do we need lrow in consolidation file at all. Can we not just start from cell A1 if any way clear contents of Thiswrkbk (consolidation sheet) in the beginning itself. Why do we need :
lrow = ThisWrkbk.Sheets("Res").Cells(Rows.Count, "A").End(xlUp).Row

Question Two:
"close save changes = false" .....does what ??

Question Three:
I will be using table in Consolidation sheet and I hope it works. Should I use similar tables for users to enter data or should I just leave open ranges and instruct them to enter data row after row.....

Attaching file with Code and explanations I have worked till now.... I have added a sheet named "Res" also.... given explanations in code under sub res (). Code needs further modification on ranges of copying data.

Question 4
I plan to add 5 to 6 more sheets and re-name those, hence using the method of opening all of them at once and not looping them. Can we automatically lock data after users have entered.....

Any cautions or suggestions are of course welcome. Subsequently I will do an analysis of all consolidated sheets and figure out my monthly inputs. But am still not sure how will I control and account if the users make retrospective changes........so far so good !!!

Have a great day!!

 

[PCosta87]

ok to expand on this question, I am currently trying to write a script that would allow me to extract data from 26 separate tables from different sources, combine them into a single table that would update automatically along with placing the data in the correct order by placing each item at the bottom of it's category (inserting the updates rather than moving them to bottom of the updated table.) is this strategically possible?

Hi,

Yes, from your description I would say it is possible... would need an actual sample file to provide any further assistance.

Upload if possible and I will look at it as soon as I can.

 

[Kaushik Joshi]

@Costa
hi, need help.....sorry for too many questions above!
my code now works fine but table in consolidation sheet gets overwritten and gets converted into a range.... How to let it remain a table. Also I observed that if source sheets are tables then only the first row gets copied in consolidation sheet. If they are ranges it works fine. Attaching file for reference.
Regards,
KJ

 

[PCosta87]

Question one:
Why do we need lrow in consolidation file at all.

You don't... I used it the first time and just forgot to delete it in this second version =) (it does no harm however )

Question Two:
"close save changes = false" .....does what ??

It closes workbook discarding any changes. This is because sometimes Excel prompts you to select save/discard changes when you close, which temporarily halts the code execution until you answer... this prevents that from happening)

Question Three:
I will be using table in Consolidation sheet and I hope it works. Should I use similar tables for users to enter data or should I just leave open ranges and instruct them to enter data row after row.....

Using tables is always a good practice since it can be referred to by name, which is actually very convenient when programming... it would, for instance, eliminate the need to "find" the last row (lrow1, lrow2, lrow3) since you can just select the range by name.

Question 4
I plan to add 5 to 6 more sheets and re-name those, hence using the method of opening all of them at once and not looping them. Can we automatically lock data after users have entered.....

I didn't totally understand this one... however, protecting data, sheets or even entire workbooks is definitely possible. You can even allow only specific actions when data is protected.

I'm sorry it has been taking so long to answer but work is sometimes overwhelming... I will see your other post as soon as I can.

 

[Kaushik Joshi]

@ PCosta,
Again thanks for those answers.....
I don't intend to bother you on weekend and its unspokenly understood that you may choose to reply at your convenience on Monday or later......I totally understand...
Cannot help feeling....curious while learning and asking so many questions....and you have been very patient !! thats kind of you!

Attaching the code that is now working the way I want it to. i.e. it preserves the Table format and does a Paste Special when it looks up the data from sources and comes to last row each time by offsetting in the consolidation sheet.
I have tried to work my own solution for what I asked in my last post....

Please do check....and advise if there is some overcoding or some thing can be done better....

Guess its now ready for scaling up and users.
One question - although Tables bring in programming convenience, they may be messed up by users accidentally, hence I am preferring the data entry in plain ranges and not in Tables....is that a good practice in your opinion??

thanks again!!!

 

[PCosta87]

@ PCosta,
Again thanks for those answers.....
I don't intend to bother you on weekend and its unspokenly understood that you may choose to reply at your convenience on Monday or later......I totally understand...
Cannot help feeling....curious while learning and asking so many questions....and you have been very patient !! thats kind of you!

Attaching the code that is now working the way I want it to. i.e. it preserves the Table format and does a Paste Special when it looks up the data from sources and comes to last row each time by offsetting in the consolidation sheet.
I have tried to work my own solution for what I asked in my last post....

Please do check....and advise if there is some overcoding or some thing can be done better....

Guess its now ready for scaling up and users.
One question - although Tables bring in programming convenience, they may be messed up by users accidentally, hence I am preferring the data entry in plain ranges and not in Tables....is that a good practice in your opinion??

thanks again!!!

Hi,

The only thing I would change is (using the code for "ADD" as example):

    lrow = ThisWrkbk.Sheets("ADD").Columns("A").Cells(Rows.Count).End(xlUp).Row
    ThisWrkbk.Sheets("ADD").Range("2:" & lrow).Offset(1, 0).Delete shift:=xlUp

to

    ThisWrkbk.Sheets("add").[table2].ClearContents

or, if you wish to delete the range (and not only its contents), to:

    On Error Resume Next
    ThisWrkbk.Sheets("add").[table2].Delete shift:=xlUp

Since you are using Tables in the consolidation workbook, there is no need to find the last row, simply refer to the Table by name and either clear its contents or delete (with delete, it will throw you an error if the Table is already empty... hence the use of the "On error" statement)

About not using the tables in the "LOC" workbooks... either way works. It is up to you

 

[Kaushik Joshi]

I changed to :
OnErrorResumeNext
ThisWrkbk.Sheets("add").[table2].Delete shift:=xlUp
it works fine...
Then I scaled up to 8 sheets, changed a few table fields and then changed the structure of the program...
Now I am getting Clibboard msg "There is a large amount of info on clipboard..etc." for certain number of times....and I have to say "no" for those many nbr of times for it to work fine. I have to say Yes or No to this msgbox and proceed.

I think we are at the last bit for this to keep working....! Hopefully!
In case users change this month's data while adding data for next month, I dont know how I will control such user behaviour.....I guess I will keep updated tables of current month and compare with user inputs of next month to clearly identify changes/editing/additions....with the help of a dual Match-Index table to table....

For now need help with Clipboard msg plz!!.....have uploaded scaled up file....this has something to do with .Close savechanges = False as it once gave an error and on debugging stopped on this line..

 

[PCosta87]

Hi,

Just one thing before getting to the message part... you don't need

    Application.CutCopyMode = False

You can delete it...

As for the message, not sure what is causing it as I was not able to reproduce it here. In any case, try:

    Application.DisplayAlerts = False
...code here...
    Application.DisplayAlerts = True

Use in the same way as "Application.ScreenUpdating"

 

[Kaushik Joshi]

I used below in every With Statement.....and it worked....

Application.CutCopyMode = False
.Close savechanges = False

 

[Kaushik Joshi]

Hi,

Just one thing before getting to the message part... you don't need

    Application.CutCopyMode = False

You can delete it...

As for the message, not sure what is causing it as I was not able to reproduce it here. In any case, try:

    Application.DisplayAlerts = False
...code here...
    Application.DisplayAlerts = True

Use in the same way as "Application.ScreenUpdating"

I think I tried Application.CutCopyMode = False before reading your message.
Is there a big disadvantage in this???

OK will delete it and try with Application.DisplayAlerts = False / True...
this may stop displaying alerts that are even necessary.....dont you think this could be a disadvantage??
will try anyway...

 

[PCosta87]

I think I tried Application.CutCopyMode = False before reading your message.
Is there a big disadvantage in this???

OK will delete it and try with Application.DisplayAlerts = False / True...
this may stop displaying alerts that are even necessary.....dont you think this could be a disadvantage??
will try anyway...

Hi,

You are correct... it will disable all messages!
It isn't a problem as long as you enable it back at the end of the code... during the time the code is running you are in control which means there shouldn't be any "unexpected" alerts.
Necessary alerts can be dealt with the same way we did with the "save changes" when closing the workbooks.
In any case, if for some reason there is an alert you wish to display, either use the "CutCopyMode" as you said, after each paste, or, in the same way, use the "DisplayAlerts" just before and after the parts that you know you don't want any alerts to be displayed... it doesn't necessarily need to be at the start/end of the code.

Anyway, it seems you got it.
Let me know if there is anything else I can help with.

 

[Kaushik Joshi]

hi,
thanks for explaining. I finally have launched it live on 10 files with 10 unique location names. have requested our IT Team to define access rights for each file on a Shared Directory.

This has been quite a learning for me as its my First VBA project !! Thanks for staying with me all along !

For now i have no major alerts to display during the running of code hence i have done "DisplayAlerts" at beginning and end of code.....it works fine.
Also tested the whole thing with dummy data and its working fine.

My only worry now is that at the end of each period I will maintain a Frozen Data base as on Month-end-date in another table. Once i update for next month...... I will need to compare the Updated Data with Frozen data of Last Month to know what has been reported. Like that I can also ascertain if someone makes changes on old reported data, other than updating (adding) for the month. I am proposing to build this checking mechanism with a match index between two tables as my Headers are unique and in rows I will have a Unique ID for each row. Do you agree.......i.e. If I have successfully communicated !!! No worries any way!! for now I am good to go.....will post again on progress !!

Cheers and Have a Nice day!

 

[PCosta87]

Hi,

I agree, using the unique ID's is probably the best way of doing it.

Cheers

 

[Kaushik Joshi]

@ Costa,
Hi,
Implementation is working reasonably ok
I think Reconciling Tables to track changes with unique ID is tedious and time consuming.....hence I want to drop that idea...
Instead of that can I lock "all rows in LOC files where users have entered data"at the click of a button.....may be i will have to write a procedure for locking already entered data with a dialogue box prompting are you sure Yes? or Cancel?.... with .....This allows users to enter data only from next blank row and existing data is all locked. In case there are changes I want users to discuss and I can make those for users centrally.....Is this asking for too much?? can we do this easily...

 

[Kaushik Joshi]

also facing a bug.....if users do not have anything to enter it fetches Headers and a blank row.....

With Workbooks("Loc1-Bangalore.xlsx")
lrow1 = .Sheets("Add").Columns("A").Cells(Rows.Count).End(xlUp).Row
.Sheets("Add").Range("A2:n" & lrow1).Copy
ThisWrkbk.ActiveSheet.Range("A2").PasteSpecial xlPasteValues

'Application.CutCopyMode = False
.Close savechanges = False
End With

Is there something that will throw it out of "With...Loc1" statement if the lrow1 in the above example nbr detected is Row Nbr 1 and start with next "With....Loc2..." ......and so on.....copy only there is Data in 2nd row....
hope you understand.....
I am not in a hurry for this... and above post.....

thanks and cheers for the day!

 

[PCosta87]

Hi,

Sorry for the delayed response...

may be i will have to write a procedure for locking already entered data with a dialogue box prompting are you sure Yes? or Cancel?.... with .....This allows users to enter data only from next blank row and existing data is all locked. In case there are changes I want users to discuss and I can make those for users centrally.....Is this asking for too much?? can we do this easily

Well, for this you will need a bit of code to lock the cells, and trigger that through a worksheet event... before closing the workbook is probably the best way to do it.

also facing a bug.....if users do not have anything to enter it fetches Headers and a blank row

I didn't predict this situation... It should be a relatively easy fix though!

I will look into it

 

[Kaushik Joshi]

@Costa,
Greetings!
No Problem for delayed response!! I have decided to keep the code as it is and run it only to collect monthly data.... I don't want to upgrade this anymore as it brings in more programing, validation and network complexities which currently I am unable to handle at the moment !!! I am fine with current code which takes care of my monthly activity of pulling user data. I will manually update a cumulative database at my central location. thanks for help till now....the monthly report has saved me a lot of manual work and e mail exchange.....and chances of errors have reduced.....requesting you to please help in bug fix for "Headers and blank row" in case no data is entered by users...you can see the sheet MOB_TRF where this has occurred as a sample. Attaching consolidation file which contains code that is running well...

Cheers and thanks !

 

[PCosta87]

Hi,

This should do it... please refer to the attached file.
I couldn't build upon the code of the file you uploaded because it was password protected so I used the last one I uploaded.

Let me know if it works for you.

 

[Kaushik Joshi]

ooops ! sorry for not providing PW...will look at this. thanks a lot !!

Good Day!

 

[Kaushik Joshi]

@ PCosta87,

Hi
your code works fine....now if the sheet is blank we do not get the headers.....
It took time for me to replicate the code you gave for sheet "add" to the other 2 sheets viz.....Del and Trf_Mob.....

Thanks to this code I was able to collect data last month by keeping all files on a server location and my colleagues fill up at respective locations by accessing the servers.....I also put some validations in Location sheets so the data collected is uniform......
This month also it should go well with the improved code....cheers and thanks for that!!

Can you please help me to interpret below code for my better understanding of logic ...

For example if we look at Location 4.....

With Workbooks("Loc4-HP.xlsx")
lrow4 = .Sheets("Add").Columns("A").Cells(Rows.Count).End(xlUp).Row
If lrow4 > 1 Then
.Sheets("Add").Range("A2:n" & lrow4).Copy

If lrow3 > 1 Or lrow2 > 1 Or lrow1 > 1 Then
ThisWrkbk.ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Else
ThisWrkbk.ActiveSheet.[Table2].PasteSpecial xlPasteValues
End If


my understanding and questions...

It defines and locates lrow4 which is the last row of location 4 and if it is More than 1, it copies range A2 to n till last row...

Question 1....Once copied.......why do you check again for lrow3, lrow2, lrow1 also as greater than 1 ?? I did not understand why this condition??

then I understood that we have to offset 1 row and then paste whatever has been copied in this workbook active sheet (which is Add sheet in Consolidation file )

Question 2....
If the or condition is false...... we simply paste in Table2 ....Does it mean the data will just get appended automatically to the last row of the table??


Question 3..... not about above....but for a different consolidation need...

I have one more need of consolidation .....I need to consolidated approximately 50 to 100 rows appended each month at 10 locations to a Trainings Sheet (annually about 600 to 800 training rows) and 500 to 1000 rows appended each month for same locations in Employees Sheet (annually approximately 8000 to 12000 rows)......Eventually I do a lot of calculations on this data and prepare a dashboard.

Would it be a good idea to adopt the code you gave for this job too??


cheers!