Populating file name against each row of Data before consolidation

[Kaushik Joshi]

hi Macro Ninjas,
I am using the below macro to consolidate data from 3 different files Source files into a Single Consolidated file.
Each source file has variable number of sheets with a standard number of Data columns.
In column B of each file in each sheet, there appears name of the file....AdAstra, HRFort, Manushya.
Before the Macro proceeds with copying and consolidating data from all source files....
I want it to copy the file name of each Source file and paste it in front of each record in the Source file itself (at column B) on every sheet and then Consolidate.
In Consolidated file, copied data will look like column B (attached ).
Since the data in column B is nothing but the File name of Source file, I want the Source file users to avoid manually populating data in column B in each sheet.
I feel this should be easily possible for someone very used to VBA coding...
Please help..

My consolidation code is as follows:

Sub Joining()

Application.DisplayAlerts = False
Application.ScreenUpdating = False


Workbooks.Open "C:\Users\K6246948\Desktop\DEL-2016-06\GNO-HRIS Reports Automated\HR Analytics\AMG_Recruit_Analysis\Ad Astra.xlsx"
Workbooks.Open "C:\Users\K6246948\Desktop\DEL-2016-06\GNO-HRIS Reports Automated\HR Analytics\AMG_Recruit_Analysis\HRFort.xlsx"
Workbooks.Open "C:\Users\K6246948\Desktop\DEL-2016-06\GNO-HRIS Reports Automated\HR Analytics\AMG_Recruit_Analysis\Manushhya.xlsx"


Dim lrow, r As Integer
Dim ws As Worksheet

On Error Resume Next

ThisWorkbook.Sheets("Consolidation").[Table1].Delete shift:=xlUp

With Workbooks("Ad Astra.xlsx")
For Each ws In .Sheets
lrow = ws.Columns("A").Cells(Rows.Count).End(xlUp).Row
r = ThisWorkbook.Sheets("Consolidation").[Table1].SpecialCells(xlCellTypeConstants).Count
If lrow > 1 Then
ws.Range("A2:AJ" & lrow).Copy
If r > 0 Then
ThisWorkbook.Sheets("Consolidation").Cells(ThisWorkbook.Sheets("Consolidation").[Table1].Rows.Count + 2, 1).PasteSpecial xlPasteValues
Else
ThisWorkbook.Sheets("Consolidation").[Table1].PasteSpecial xlPasteValues
End If
End If
'End If
Next ws
.Close False
End With

With Workbooks("HRFort.xlsx")
For Each ws In .Sheets
lrow = ws.Columns("A").Cells(Rows.Count).End(xlUp).Row
r = ThisWorkbook.Sheets("Consolidation").[Table1].SpecialCells(xlCellTypeConstants).Count
If lrow > 1 Then
ws.Range("A2:AJ" & lrow).Copy
If r > 0 Then
ThisWorkbook.Sheets("Consolidation").Cells(ThisWorkbook.Sheets("Consolidation").[Table1].Rows.Count + 2, 1).PasteSpecial xlPasteValues
Else
ThisWorkbook.Sheets("Consolidation").[Table1].PasteSpecial xlPasteValues
End If
End If
'End If
Next ws
.Close False
End With


With Workbooks("Manushhya.xlsx")
For Each ws In .Sheets
lrow = ws.Columns("A").Cells(Rows.Count).End(xlUp).Row
r = ThisWorkbook.Sheets("Consolidation").[Table1].SpecialCells(xlCellTypeConstants).Count
If lrow > 1 Then
ws.Range("A2:AJ" & lrow).Copy
If r > 0 Then
ThisWorkbook.Sheets("Consolidation").Cells(ThisWorkbook.Sheets("Consolidation").[Table1].Rows.Count + 2, 1).PasteSpecial xlPasteValues
Else
ThisWorkbook.Sheets("Consolidation").[Table1].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

 

[Kaushik Joshi]

Attached please find consolidated data file.

 

[GraH - Guido]

An alternative possibility is doing the consolidation with a Power Query. Having filenames and sheets names in a final result (a data table) is like a walk in the park.
Furthermore if you save the Power Query as a connection only, you can use it directly in the Pivot. Refresh the Pivot -> Refresh the Power Query.
It depends on the version of Excel you are using if PQ is something for you.
If you upload 2 sample files to consolidate, I can provide a basic PQ you can then discover. Or google Power Query and merge files. Plenty of video tutorials available. And it is way easier to learn then VBA.

 

[vletm]

Kaushik Joshi
If You would like to run
then check something like this sample ...
Notes:
1) That should do after ... copy&paste, if before then 'no work'
2) because NO data-file, cannot test this!
3) You should verify rr-variables value = row which something would copy!
4) I cleaned 'some parts of that code'
5) ... use code tags as written in Tip if You really need to paste any code!

 

[Kaushik Joshi]

Hello GraH,
Thank you for your reply on Power query that is very much an interesting possibility and perhaps the way forward for me. I am attaching 3 data files and 1 consolidation file, that I am now using with a Macro.
I am interested in using Power query option on consolidation.
Requesting help on getting started with setting up Power Query in Excel 2016 and using the same on consolidating 3 files I have attached.
Actually I do have a Power Pivot option in my menu...

regards,
KJ

 

[Kaushik Joshi]

Hello GraH,
Thank you for your reply on Power query that is very much an interesting possibility and perhaps the way forward for me. I am attaching 3 data files and 1 consolidation file, that I am now using with a Macro.
I am interested in using Power query option on consolidation.
Requesting help on getting started with setting up Power Query in Excel 2016 and using the same on consolidating 3 files I have attached.
Actually I do have a Power Pivot option in my menu...

regards,
KJ

uploading files....

 

[GraH - Guido]

uploading files....

I will take a closer look later today. Now I just viewed the sample source files your provided. I would advise to use a stricter naming convention for the sheets. Now I see S.O, SO, SOT, with or without hyphen (-). If you would use the hyphen in a consistent way e.g. "SO/ASI - UserName" that would be beneficial for the PQ logic.
If you are 100% sure no "fake" sheets will ever appear in those workbooks, then it might not be required to filter on sheet names. But it always better to build in some safe guards.
This will make more sense once you see the PQ.

 

[GraH - Guido]

Hi Kaushik Joshi
Like this it is possible.
I'm guessing from what you tell me you are using version 2016. Look on the ribbon for data and query -> open editor (look in something named Get and Transform: it is the new name of PQ.)
To test and see what I've done, put your files in a folder ToMerge. On your D-drive if possible. if not, you need to change the source step and change the patch of this "Folder.Files("D:\ToMerge")". You'll get an error anyway, and you will be guided to this step when you click on "Go To Error"'.
At the right-side of the PQ screen you can see the applied transformation step. When you click on it, in the middle part you get a preview of the intermediate result.

PS: there is another option where you load each of the files individually and then append them. Note that append is the correct term for this transformation in PQ to consolidate files. Merge is to expand table in the width (you add new data to the right, like with LOOKUP functions).