Hi,
Sorry for the distrubance.Pls forgive me for my mistake.Yes it was my bad.
And thanks for the response...Yes i have tried the code for my Master sheet.But im getting Column error when run the script.
PLease find the below code for your review:
Option Explicit
Sub ConsolidateRandomColumns()
Dim wsData As Worksheet
Dim wsCons As Worksheet
Dim wbSrc As Workbook
Dim Col As Long
Dim NumCols As Long
Dim ColFnd As Long
Dim LastRow As Long
Dim NextRow As Long
Application.ScreenUpdating = False
Set wsCons = ThisWorkbook.Sheets("Consolidated Data")
NumCols = wsCons.Range("1:1").SpecialCells(xlConstants).Columns.Count
NextRow = wsCons.Range("A" & Rows.Count).End(xlUp).Row + 1
Set wbSrc = Workbooks.Open("C:\XXXXXXXX\Source.xls")
On Error Resume Next
For Each wsData In wbSrc.Worksheets
LastRow = wsData.Cells.Find("*", wsData.Cells(Rows.Count, Columns.Count), _
SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
For Col = 1 To NumCols
ColFnd = wsData.Range("1:1").Find(wsCons.Cells(1, Col).Text, _
wsData.Cells(1, Columns.Count), xlValues, xlWhole, _
SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
If ColFnd > 0 Then
wsData.Range(wsData.Cells(2, ColFnd), wsData.Cells(LastRow, ColFnd)) _
.Copy wsCons.Cells(NextRow, Col)
ColFnd = 0
End If
Next Col
NextRow = wsCons.Range("A" & Rows.Count).End(xlUp).Row + 1
Next wsData
'Cleanup
wbSrc.Close False
Set wsCons = Nothing
Set wbSrc = Nothing
Application.ScreenUpdating = True
End Sub
This is the link i have attached my files:
http://www.excelforum.com/excel-prog...using-vba.html
Thanks in advance and sorry for the more difficulties.
Thanks,
Lakshmanan M
LinkBack URL
About LinkBacks
laxmananm 
Register To Reply
Originally Posted by jmac1947
icon below to give reputation feedback, it is appreciated.
Bookmarks