Since my data is local, I tried with the example1_more_sheets().
Changed the path / range from a1:j1 to a1:s500
It worked but grabbed all data to one sheet (as said in example). But my
requirement is to combine all sheet1s, sheet2s, sheet3s....
See - advance (sheet1) is of one format, deposit (sheet2) is of different
format, creditors (sheet3) is one format, and so on.. I cannot use if
everything come to one sheet. Further data will not be there in all sheets so
we need to check some value in each row of a column until it finds "LLINE"
(last line).

I said mike's formula is working, but it has no criteria check, either value
or "LLINE", so I got struck there. My problem is a typical one I know... can
u something about it please..


"Ron de Bruin" wrote:

What have you try ?????


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Eddy Stan" wrote in message ...
I checked, but there is no criteria checking to grab particulars row...

Can any one modify the mike's code, please....

"Ron de Bruin" wrote:

Start here

http://www.rondebruin.nl/copy3.htm

Click on this link on that page
http://www.rondebruin.nl/copy3tip.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Eddy Stan" wrote in message ...
Hi,
I tried with Mike's code (multiple file question) given below, it works for
a fixed range and for the 1st sheet of workbook.
But I want to combine advance(sheet1),deposits(sheet2), creditors(sheet3),
so on...Sheet names are unique. Validation must be done at h column starting
row 6 for value & grab the row until value = "LLINE" or BLANK, similarly it
should check value in g column for deposit sheet, i column in prepaid sheet,
& so on... The consol file should have data for each sheet from all files (in
their respective sheets advance, deposit,..).
Hope I explained... Can any one modify his code to check sheet names, cell
values & help me.. thanks in advance.
I am using excel 2002.

Mike's code:
Sub DAC_Report()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
'MyPath = "C:\Data"
'ChDrive MyPath
'ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A3:F53")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

basebook.Worksheets(1).Cells(rnum, "G").Value = mybook.Name
' This will add the workbook name in column D if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
Columns("G:G").Font.Size = 8
Columns("G:G").Font.Bold = True
' ChDrive SaveDriveDir
' ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub


Eddy Stan