MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Finding and retrieving data on multiple worksheets

  Asked By: Raju    Date: Jan 11    Category: MS Office    Views: 815

I have a problem, I am currently using a formula to retrieve data from
a merged worksheet, however this is not ideal as I have to create a
new merge sheet weekly to add updates.

Is there a way that I can find a date and then get excel to retrieve
all rows with this date in the Workbook onto one worksheet?

I currently use =IF(ISERROR(INDEX(Merge!$A$1:$AA$1800,SMALL(IF
($A$5=Merge!$AA$1:$AA$1800,ROW(Merge!$AA$1:$AA$1800)," "),ROW
$AA$1:$AA$1800,ROW(Merge!$AA$1:$AA$1800)," "),ROW(1:1)),5)) for each
column I want to receive. There can be up to 100 valid rows at anyone
time and the data is mainly text based.



5 Answers Found

Answer #1    Answered By: Shaun Thomas     Answered On: Jan 11

You would probably find  it easier to use an autofilter and then (if you need
to)copy the visible rows  to the other sheet.

Answer #2    Answered By: Akins Massri     Answered On: Jan 11

however I am trying to automate the process, it takes
too long to autofilter every sheet  and then copy and paste it to a
different sheet.

I currently use vba to merge  all sheets, but there has to be a way
of using vba to actually search find  then retrieve  them instead of
merging and then using a formula. So much easier pressing a button.

Answer #3    Answered By: Sally Pierce     Answered On: Jan 11

Get the vba to set the autofilter and thenuse that to do the data  selection.

Answer #4    Answered By: Erma Henry     Answered On: Jan 11

Here is the VBA code and I have attached a file with worksheets  named: *Merge
and Transfer data.xls*
It has 2 worksheets: "Merged Data" and "Results".

The "Merged Data" sheet  has some sample data. The macro takes input from the
user for the date  info in the format "mm/dd/yy". (You may change this part
of the code to suit your needs). The extracted records are written in
"Results" sheet (This sheet is empty to start with). If the date is not
found in the data, the macro returns the message: "No Records Found for

Sub Extract_From_MergedData()
Sheets("Merged Data").Select
DateStr = InputBox("Enter Date to be searched as ""mm/dd/yy"" format: ", _
"Get Date for extracting Data", Now())
DateSrch = CDate(DateStr)
d = 2
LR = Cells.SpecialCells(xlCellTypeLastCell).Row
LC = Cells(1, 1).End(xlToRight).Column
For r = 2 To LR
If Cells(r, 2) = DateSrch Then
Range(Cells(r, 1), Cells(r, LC)).Copy _
Destination:=Sheets("Results").Cells(d, 1)
d = d + 1
End If
Next r
If d = 2 Then
MsgBox "No Records Found for " & DateStr & "....."
Exit Sub
End If
Range(Cells(1, 1), Cells(1, LC)).Copy _
Destination:=Sheets("Results").Cells(1, 1)
MsgBox "DONE!!..."
End Sub

Answer #5    Answered By: Fabiola Ferrrari     Answered On: Jan 11

Having a few problems, cant get it running to find  anything, also
didnt get your samples. Anychance you could send the samples to my
email address?

Didn't find what you were looking for? Find more on Finding and retrieving data on multiple worksheets Or get search suggestion and latest updates.