PDA

View Full Version : Consolidating multiple worksheet into one by finding Column Name



vradhak7
03-10-2017, 11:23 AM
Hello Experts,
I have 7 different worksheets from which I need to consolidate the data into one worksheet. The data I am looking for starts from different rows but the number of columns and column name are the same.I have attached the sample file with the 7 worksheets and the summary sheet(where I want the data in that format). How do I automate using VBA ? Also, everytime I have new data populated in the 7 worksheets, the summary sheet has to delete the old data and create a new one. Your expertise would be greatly appreciated.18595

mdmackillop
03-10-2017, 05:33 PM
Sub Test()
Dim Sh As Worksheet
Dim i As Long
Dim tgt As Range
Dim cel As Range
Set Sh = Sheets("Summary")
Sh.Cells(1, 1).CurrentRegion.Offset(1).ClearContents
For i = 1 To 7
Set tgt = Sh.Cells(Rows.Count, 1).End(xlUp)(2)
Set cel = Sheets(CStr(i)).Columns(1).Find("Name")
cel.CurrentRegion.Offset(1).Copy tgt
Next
End Sub