Compiling data from several worksheets

[m]

Hi,
I am trying to compile data from several worksheets.
My base data are sheets sent to payroll for payment of different costs.
These are sent out every month so going through all sheets and filter and
then copy is not really an option.
Is there a macro that looks up whether or not a value is present in a row
and returns the whole row in a new spreadsheet? The value I am looking for is
“IA3â€.
The basic layout of the tables are as follows:
Reference – Payment type – Payment amount
Ideally it would copy the whole row if the value “IA3†is present to a new
spreadsheet and perhaps add the filename where it found it.
Thanks,
/m

 

[joel]

Can you be more precise.

1) Are you talking about multiple workbooks or worksheets. filename referes
to the workbook
2) Do you want to check every row in every worksheet of the workbook where
the macro is located.
3) Be specific about worksheet names to include or not include.

Macros can search through folder on your compter, go through all worksheets,
creatte new workbooks, create new worksheets.

 

[m]

Hi,
Let’s see what I can do. I’ll try to be more precise.
1. The base data is contained in separate workbooks not worksheets.
2. I would like to check every row of every worksheet of the base data. Do I
have to copy the macro to these workbooks or will it run from the new
workbook?
3. The base data is contained in the worksheet ‘Sheet 1’ in each of the
workbooks.
4. The workbooks are contained in a file structure that looks like this:

a. Payments
i. 2009
1. Jan
2. Feb
ii. 2008
1. Jan
2. Feb

I hope I have provided you with the data needed.
Every piece of help is appreciated.
/m

 

[joel]

I got very simila code working for somebody this morning that had the same
file structure as you. Put this code into a new summary workbook. the code
will automatically open/close all the workbooks in the file structure
(c:\payments) and put the results in the workbook with the macro. I copied
the old row to the new workbook starting in column B. Then put the filename
where the data came from in column A. I didn't test the code put I usually
get these type code right the 1st time especially after I copied 90% from a
previous posting.

Sub Combinebooks()

Root = "c:\Payments"
Set fso = CreateObject _
("Scripting.FileSystemObject")
Set folder = _
fso.GetFolder(Root)

Set SumSht = ThisWorkbook.ActiveSheet
RowCount = 1
For Each sf In folder.subfolders
FName = Dir(sf & "\*.xls")
Do While FName <> ""
Set bk = Workbooks.Open(Filename:=sf & "\" & FName)
For Each sht In bk.Sheets
With sht
If .Range("IA3") <> "" Then
LastCol = .Cells(3, Columns.Count).End(xlUp).Column
Set CopyRange = .Range("A3", .Cells(3, LastCol))
CopyRange.Copy _
Destination:=SumSht.Range("B" & RowCount)
SumSht.Range("A" & RowCount) = sf & "\" & FName
RowCount = RowCount + 1
End If
End With
Next sht
bk.Close savechanges:=False
FName = Dir()
Loop
bk.Close savechanges:=False

Next sf

End Sub