Experts kindly help me....
We receive data sheets daily from 20+ locations and I paste the data in the
same workbook on daily basis (having row1 as title row, always the same
format), same data files come everyday with yesterday's additional info.
(no.of.rows will differ to each sheet run from 70 to 500. I have a macro to
extract data from each file to my workfile.

Now I want a macro to run and extract data* in the 1st sheet (query) from
all the 20+ sheets.
Criteria will be :
3rd column 7th column 8th column
(3) Location (7) Biziness (8) date
When we input Location, Biziness & date from/to in Sheet1 cells b3, c3, d3, e3
We need data to be extracted from all sheets and put in sheet1
cells to pick from each row will be : col3, col7, col8, col4, col5, col9,
col10

procedure should be :

select sheet1
For row2 to LastRow
check col3,col7,col8
if col3 = b3 and col7=c3 and col8 is between d3 & e3 is true
extract from that row col3, col7, col8, col4, col5, col9, col10
else
ignore that row
endif
set pointer to next row
next for
next sheet til last sheet

Thanks in advance..

Sub ProcessData()
Dim sh1 as Worksheet, sh as Worksheet
Dim b3, c3, dtStart as Date, dtend as Date
Dim cell as Range, rng as Range, rw as Long
set sh1 = Worksheets(1)
b3 = sh1.Range("B3")
c3 = sh1.range("C3")
dtstart = sh1.range("D3")
dtEnd = sh1.Range("E3")
sh1.Range("4:65536").ClearContents
rw = 4
for each sh in worksheets
if sh.Name < sh1.name then
set rng = sh.Range(sh.Cells(2,1),sh.Cells(rows.count,1).End( xlup))
for each cell in rng
if cell.offset(0,2) = b3 and _
cell.offset(0,6) = c3 and _
cell.offset(0,7) = dtStart and
cell.offset(0,7) <= dtEnd then
sh1.cells(rw,1) = cell.offset(0,2)
sh1.cells(rw,2) = cell.offset(0,6)
sh1.cells(rw,3) = cell.offset(0,8)
sh1.cells(rw,4) = cell.offset(0,3)
sh1.cells(rw,5) = cell.offset(0,4)
sh1.cells(rw,6) = cell.offset(0,8)
sh1.cells(rw,7) = cell.offset(0,9)
rw = rw + 1
end if
next cell
Next sh
End sub

--
regards,
Tom Ogilvy




"Eddy Stan" wrote:

Experts kindly help me....
We receive data sheets daily from 20+ locations and I paste the data in the
same workbook on daily basis (having row1 as title row, always the same
format), same data files come everyday with yesterday's additional info.
(no.of.rows will differ to each sheet run from 70 to 500. I have a macro to
extract data from each file to my workfile.

Now I want a macro to run and extract data* in the 1st sheet (query) from
all the 20+ sheets.
Criteria will be :
3rd column 7th column 8th column
(3) Location (7) Biziness (8) date
When we input Location, Biziness & date from/to in Sheet1 cells b3, c3, d3, e3
We need data to be extracted from all sheets and put in sheet1
cells to pick from each row will be : col3, col7, col8, col4, col5, col9,
col10

procedure should be :

select sheet1
For row2 to LastRow
check col3,col7,col8
if col3 = b3 and col7=c3 and col8 is between d3 & e3 is true
extract from that row col3, col7, col8, col4, col5, col9, col10
else
ignore that row
endif
set pointer to next row
next for
next sheet til last sheet

Thanks in advance..