• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

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

Hi,

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...
Click to expand...

This is correct... if last row number of location 4 is >1 it means that the file has more data than just the headers, meaning we can copy.

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??
Click to expand...

Usually we would just copy and paste after the last used row (as it was in the original code), but with a Table as destination it is not that simple.

You see, when the Table has data (one row at least) there is no problem. However, when it is empty, it will retain an empty row below the headers. If you look for the last row with an empty Table, you will get row 2 and not row 1 as you would without it. That's because Excel interprets that empty row as being in use.
I had to make sure that something had already been copied before starting to paste after the last row. Since we had a variable for each of the locations (lrow1, lrow2, lrow3 and so on) I simply checked if any of the previous locations had any data (lrow(x) >1) and if so i could resume pasting after the last row... if not, I knew I needed to paste from row 2 downwards.

For instance, you could check if "A2" (or any of the cells in the second row) isn't empty and if so paste after the last row. I opted for checking the variables because I wasn't sure if I could rely on that.
Imagine that, for some reason, you had a missing Sr# in the 2nd row of Loc1, or the entire 2nd row for that matter. You would still copy from Loc1 to the destination Table because lrow1 would be >1 (there would be data after row 2) but then the 2nd row in the destination sheet would be empty. Checking for that when copying from any of the following locations would trigger the paste action from row 2 downwards thus deleting the previously copied data from Loc1.

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??
Click to expand...

Not really, no... if none of the previous lrow is >1 it means that no data has been copied yet so we can paste in table 2... in other words, we paste from row 2 downwards.
Pasting in Table2 means pasting immediately below the headers.

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??
Click to expand...

The beauty of Excel is that there are multiple ways of completing a specific task... that being said, slightly tweaked, I'm sure this code will also work.
I'm obviously not saying there aren't any other, perhaps better, ways of doing it :)
 
Hi and Bonjour !!

I adopted the macro in creating data consolidation for trainings held at 10 different locations.
Work around for insertion of blank row and titles in case there was no data is working fine....I like it a lot ! Compliments!
Final file uploaded...
 

Attachments

  • Training Consolidataion-V1.xlsm
    63.7 KB · Views: 1
Hi and Bonjour PCosta87 !
Hope you are doing very well !

In my consolidation model I collect data into
Consolidation file Sheet1 from
Datafile 1 - Sheet 1,
Datafile 2 - Sheet 2,
Datafile 3 - Sheet 3, .....
Datafile n - Sheet n

I would like to now collect Data into a
Consolidation File Sheet1 from
Datafile 1 - Sheet 1, Sheet 2, Sheet 3,
Datafile 2 - Sheet 1, Sheet 2
Datafile 3 - Sheet 1
Datafile n - Sheet 1, Sheet 2,........ Sheet n

Number of Sheets may vary in each file up to 4 sheets.

Attaching a PW free file with a functional Macros that is working for me...
Hope you can show me one example and I can adopt to rest of the Data files.
 

Attachments

  • Consolidataion-V5 - Sample for adapting to Recruitment.xlsm
    249.5 KB · Views: 2
Kaushik Joshi said:
Hi and Bonjour PCosta87 !
Hope you are doing very well !

In my consolidation model I collect data into
Consolidation file Sheet1 from
Datafile 1 - Sheet 1,
Datafile 2 - Sheet 2,
Datafile 3 - Sheet 3, .....
Datafile n - Sheet n

I would like to now collect Data into a
Consolidation File Sheet1 from
Datafile 1 - Sheet 1, Sheet 2, Sheet 3,
Datafile 2 - Sheet 1, Sheet 2
Datafile 3 - Sheet 1
Datafile n - Sheet 1, Sheet 2,........ Sheet n

Number of Sheets may vary in each file up to 4 sheets.

Attaching a PW free file with a functional Macros that is working for me...
Hope you can show me one example and I can adopt to rest of the Data files.
Click to expand...

Hi,

The file is asking for a password... please re-upload without protection if possible.
Also, I would like you to better explain the requirement as I didn't fully understand it.

Thanks
 
Hi am sorry for the Password slip up....I did not save !! Now it should be OK.... This Macro is working well .....

What I mean to say is....up till now I had been consolidating Data from 10 files. Each file had a SINGLE sheet named ADD.
Data in all 10 files (from 10 sheets) got consolidated into 1 Consolidation File into a sheet named ADD_Consolidation.
Now I want to consolidate some Recruitment data, where each Data File will have one or more ( up to 3 ) sheets named ADD1, ADD2, ADD3.....
and I want my consolidation Sheet Macro to visit these multiple sheets (instead of a single sheet as earlier) and pick up information from all those and consolidated into 1 consolidation file.

I thought of the following solution if for example My Bangalore location file had one more sheet named Add1 also for consolidation data:

Original code with only 1 sheet to visit looks as follows :

Dim lrow1, lrow2, lrow3, lrow4, lrow5, lrow6, lrow7, lrow8, lrow9, lrow10 As Integer
'integer is a whole number, a number which is not a fraction
Dim ThisWrkbk As Workbook
Set ThisWrkbk = ThisWorkbook
On Error Resume Next
ThisWrkbk.Sheets("add").[Table2].Delete shift:=xlUp

With Workbooks("Loc1-Bangalore.xlsx")

lrow1 = .Sheets("Add").Columns("A").Cells(Rows.Count).End(xlUp).Row
If lrow1 > 1 Then
.Sheets("Add").Range("A2:n" & lrow1).Copy
ThisWrkbk.ActiveSheet.[Table2].PasteSpecial xlPasteValues
.Close savechanges = False
Else
.Close savechanges = False
End If

End With

......But if I have to collect Data from another sheet named "Add_1" in the same Bangalore location...then my code would look as below....:

Dim lrow1, lrow1a, lrow2, lrow3, lrow4, lrow5, lrow6, lrow7, lrow8, lrow9, lrow10 As Integer
'integer is a whole number, a number which is not a fraction
Dim ThisWrkbk As Workbook
Set ThisWrkbk = ThisWorkbook
On Error Resume Next
ThisWrkbk.Sheets("add").[Table2].Delete shift:=xlUp

With Workbooks("Loc1-Bangalore.xlsx")

lrow1 = .Sheets("Add").Columns("A").Cells(Rows.Count).End(xlUp).Row
If lrow1 > 1 Then
.Sheets("Add").Range("A2:n" & lrow1).Copy
ThisWrkbk.ActiveSheet.[Table2].PasteSpecial xlPasteValues
.Close savechanges = False
Else
.Close savechanges = False
End If


lrow1a = .Sheets("Add1").Columns("A").Cells(Rows.Count).End(xlUp).Row
If lrow1 > 1 Then
.Sheets("Add1").Range("A2:n" & lrow1).Copy
ThisWrkbk.ActiveSheet.[Table2].PasteSpecial xlPasteValues
.Close savechanges = False
Else
.Close savechanges = False
End If

End With

....

Then will have to provide for the row

if lrow2 > 1 or lrow1 > 1 or lrow1a Then

As we proceed further down the program to collect data from more sheets....

Will this work or you would have some other suggestion.....
 
Sheet Add1 with new integer lrow1a will have a code as below: (the above is wrong)


lrow1a = .Sheets("Add1").Columns("A").Cells(Rows.Count).End(xlUp).Row
If lrow1a > 1 Then
.Sheets("Add1").Range("A2:n" & lrow1a).Copy
ThisWrkbk.ActiveSheet.[Table2].PasteSpecial xlPasteValues
.Close savechanges = False
Else
.Close savechanges = False
End If
 

Attachments

  • Consolidataion-V5 - Sample for adapting to Recruitment.xlsm
    237.1 KB · Views: 6
Hi,

I went back to get the LOC files and also took the time to improve the code...
I believe I was able to make it simpler to understand and also to implement for multiple locations.

Use the following, replacing the "D:\Loc(...).xlsx" with your files:
Code: 
Sub Add()

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
          
    Workbooks.Open "D:\Loc1.xlsx"
    Workbooks.Open "D:\Loc2.xlsx"
    Workbooks.Open "D:\Loc3.xlsx"

    Dim lrow, r As Integer
    Dim ws As Worksheet

    On Error Resume Next

    ThisWorkbook.Sheets("Add").[Table2].Delete shift:=xlUp

    With Workbooks("Loc1.xlsx")
        For Each ws In .Sheets
            If Left(ws.Name, 3) = "Add" Then
                lrow = ws.Columns("A").Cells(Rows.Count).End(xlUp).Row
                r = ThisWorkbook.Sheets("ADD").[Table2].SpecialCells(xlCellTypeConstants).Count
                If lrow > 1 Then
                    ws.Range("A2:N" & lrow).Copy
                    If r > 0 Then
                        ThisWorkbook.Sheets("ADD").Cells(ThisWorkbook.Sheets("ADD").[Table2].Rows.Count + 2, 1).PasteSpecial xlPasteValues
                    Else
                        ThisWorkbook.Sheets("ADD").[Table2].PasteSpecial xlPasteValues
                    End If
                End If
            End If
        Next ws
        .Close False
    End With

    With Workbooks("Loc2.xlsx")
        For Each ws In .Sheets
            If Left(ws.Name, 3) = "Add" Then
                lrow = ws.Columns("A").Cells(Rows.Count).End(xlUp).Row
                r = ThisWorkbook.Sheets("ADD").[Table2].SpecialCells(xlCellTypeConstants).Count
                If lrow > 1 Then
                    ws.Range("A2:N" & lrow).Copy
                    If r > 0 Then
                        ThisWorkbook.Sheets("ADD").Cells(ThisWorkbook.Sheets("ADD").[Table2].Rows.Count + 2, 1).PasteSpecial xlPasteValues
                    Else
                        ThisWorkbook.Sheets("ADD").[Table2].PasteSpecial xlPasteValues
                    End If
                End If
            End If
        Next ws
        .Close False
    End With

    With Workbooks("Loc3.xlsx")
        For Each ws In .Sheets
            If Left(ws.Name, 3) = "Add" Then
                lrow = ws.Columns("A").Cells(Rows.Count).End(xlUp).Row
                r = ThisWorkbook.Sheets("ADD").[Table2].SpecialCells(xlCellTypeConstants).Count
                If lrow > 1 Then
                    ws.Range("A2:N" & lrow).Copy
                    If r > 0 Then
                        ThisWorkbook.Sheets("ADD").Cells(ThisWorkbook.Sheets("ADD").[Table2].Rows.Count + 2, 1).PasteSpecial xlPasteValues
                    Else
                        ThisWorkbook.Sheets("ADD").[Table2].PasteSpecial xlPasteValues
                    End If
                End If
            End If
        Next ws
        .Close False
    End With
  
    ThisWrkbk.Sheets("Analysis").PivotTables("PivotTable1").PivotCache.Refresh
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

As you can see, you can simply copy/paste the "With... End With" part, replacing the Loc file each time, and you should be good to go.
It will loop through all sheets in each workbook and if the sheet name starts with "Add" it will copy to the consolidation table (if there is anything to copy, that is).

It should be quite simple to change it for the other DEL or TRF_MOB sheets
 
Last edited:
Wow.....You are damn good at this. This was fast. Will have a look and revert and let you know how it goes.....

Thanks and have a nice evening !!
 
Bonjour M. Costa !
J'espere que ca vous trouve en tres bonne santee!! oui j'hazard pratiquer mon francais un peu !!

j'ai besoin d'une tres petite aide......

Est ce possible de mettre-a-jour la date et l'heure sur chaque ligne ou l'utilisateur aura change ou ajoute des donnees......
......... c'est difficile en Francais, quand meme avec complement pour tous les Francofone qui doivent ecrire Anglais !!

in short, short can you tell me the code to have the date and time against each record or row when the users have changed or updated data....

hope you understand my French too !!
Bonne Journée et beaucoup de remerciement pour votre gentillesse!
 
Consolidation Macro Program you helped me with is working file...
I have to know date/time when the users are edit of enter data. Is there a way?
Sorry if i got carried away by my French ! :)
 
You must log in or register to reply here.
Back
Top