Hi Everyone,

Here is what want to do:
I need to collection information with X amounts of sheet in one particular
folder that meet a particular criteria (let's say find all those line/records
that are December in a certain column range) and then store all of those
information in one Sheet name Summary).

Basically what I want is a summary sheet of all the workbooks in on folder.
I did a little research in the Discussion group, but mostly are just
collecting ALL data in workbooks and put them in one workbook in different
sheet. However, for my purpose, I also need the Macro/Program to search for
Certain Criteria before copying the Line over and Pending to ONE sheet only.

Is it possible to do this?

THANK YOU FOR ANY SUGGESTION/ADVISE.
Neon520

Try something like this

Sub GetData()

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
NewRowCount = 1
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
With Sht
OldRowCount = 1
Do While .Range("A" & OldRowCount) < ""
If .Range("A" & OldRowCount) = "December" Then
.Rows(OldRowCount).Copy _
Destination:= NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
Loop

End Sub


"Neon520" wrote:

Hi Everyone,

Here is what want to do:
I need to collection information with X amounts of sheet in one particular
folder that meet a particular criteria (let's say find all those line/records
that are December in a certain column range) and then store all of those
information in one Sheet name Summary).

Basically what I want is a summary sheet of all the workbooks in on folder.
I did a little research in the Discussion group, but mostly are just
collecting ALL data in workbooks and put them in one workbook in different
sheet. However, for my purpose, I also need the Macro/Program to search for
Certain Criteria before copying the Line over and Pending to ONE sheet only.

Is it possible to do this?

THANK YOU FOR ANY SUGGESTION/ADVISE.
Neon520

Hi Joel,

Thank you for your reply, and I'm sorry to bother you again.
But I tried your code several times/ways, it didn't work out for me.

Here is the modified code I use for myself:

Sub Transfer()
'
' Transfer Macro
'
' Keyboard Shortcut: Option+Cmd+x
'

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "/Users/Neon/Desktop/TEST FOLDER"
FName = Dir(Folder & "Workbook1.xls")
NewRowCount = 1
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
With Sht
OldRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If .Range("B" & OldRowCount) = "December" Then
..Rows(OldRowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
Loop

End Sub

First of all, I'm a Mac user so the file/folder directory is a little
different from PC. Second of all I only change File name to Workbook1.xls (I
also tried it with the * on it, not work). Thirdly, I changed the column "A"
to "B".

Can you tell what I did wrong here? I place everything in a folder called
TEST FOLDER on the desktop.

Thank you,
Neon520



"Joel" wrote:

Try something like this

Sub GetData()

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
NewRowCount = 1
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
With Sht
OldRowCount = 1
Do While .Range("A" & OldRowCount) < ""
If .Range("A" & OldRowCount) = "December" Then
.Rows(OldRowCount).Copy _
Destination:= NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
Loop

End Sub


"Neon520" wrote:

Hi Everyone,

Here is what want to do:
I need to collection information with X amounts of sheet in one particular
folder that meet a particular criteria (let's say find all those line/records
that are December in a certain column range) and then store all of those
information in one Sheet name Summary).

Basically what I want is a summary sheet of all the workbooks in on folder.
I did a little research in the Discussion group, but mostly are just
collecting ALL data in workbooks and put them in one workbook in different
sheet. However, for my purpose, I also need the Macro/Program to search for
Certain Criteria before copying the Line over and Pending to ONE sheet only.

Is it possible to do this?

THANK YOU FOR ANY SUGGESTION/ADVISE.
Neon520

You lost the last slash on the folder name. My code has one and yours doesn't.

"Neon520" wrote:

Hi Joel,

Thank you for your reply, and I'm sorry to bother you again.
But I tried your code several times/ways, it didn't work out for me.

Here is the modified code I use for myself:

Sub Transfer()
'
' Transfer Macro
'
' Keyboard Shortcut: Option+Cmd+x
'

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "/Users/Neon/Desktop/TEST FOLDER"
FName = Dir(Folder & "Workbook1.xls")
NewRowCount = 1
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
With Sht
OldRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If .Range("B" & OldRowCount) = "December" Then
.Rows(OldRowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
Loop

End Sub

First of all, I'm a Mac user so the file/folder directory is a little
different from PC. Second of all I only change File name to Workbook1.xls (I
also tried it with the * on it, not work). Thirdly, I changed the column "A"
to "B".

Can you tell what I did wrong here? I place everything in a folder called
TEST FOLDER on the desktop.

Thank you,
Neon520



"Joel" wrote:

Try something like this

Sub GetData()

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
NewRowCount = 1
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
With Sht
OldRowCount = 1
Do While .Range("A" & OldRowCount) < ""
If .Range("A" & OldRowCount) = "December" Then
.Rows(OldRowCount).Copy _
Destination:= NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
Loop

End Sub


"Neon520" wrote:

Hi Everyone,

Here is what want to do:
I need to collection information with X amounts of sheet in one particular
folder that meet a particular criteria (let's say find all those line/records
that are December in a certain column range) and then store all of those
information in one Sheet name Summary).

Basically what I want is a summary sheet of all the workbooks in on folder.
I did a little research in the Discussion group, but mostly are just
collecting ALL data in workbooks and put them in one workbook in different
sheet. However, for my purpose, I also need the Macro/Program to search for
Certain Criteria before copying the Line over and Pending to ONE sheet only.

Is it possible to do this?

THANK YOU FOR ANY SUGGESTION/ADVISE.
Neon520

Hi Joel,

Sorry to bother you Again!

But it still doesn't work for whatever reason.

I tried the keyboard shortcut that I assign and then I tried to go to Macro
and run it from there, but nothing. When I did the keyboard shortcut, the
screen just had a slight flick, and then nothing happen - no data transfer,
no change in appearance, nothing.

Any idea that I can try?

Thanks for your help.
Neon520


"Joel" wrote:

You lost the last slash on the folder name. My code has one and yours doesn't.

I added some msgbox for debugging. Also change the check for December to
ignore case. One possibility in the Month is a serial date like 12/16/08
which is formated to display the Month only.

then the check would be
If Month(.Range("B" & OldRowCount)) = 12 Then



Sub Transfer()
'
' Transfer Macro
'
' Keyboard Shortcut: Option+Cmd+x
'

Set NewSht = ThisWorkbook.ActiveSheet

Folder = "/Users/Neon/Desktop/TEST FOLDER/"
FName = Dir(Folder & "*.xls")
MsgBox ("Found file : " & FName)
NewRowCount = 1
Do While FName < ""
Set OldBk = Workbooks.Open(Filename:=Folder & FName)
For Each Sht In OldBk.Sheets
MsgBox ("check Sheet : " & Sht.Name)
With Sht
OldRowCount = 1
Do While .Range("B" & OldRowCount) < ""
If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
..Rows(OldRowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If
OldRowCount = OldRowCount + 1
Loop
End With
Next Sht
OldBk.Close savechanges:=False
FName = Dir()
MsgBox ("Found file : " & FName)
Loop

End Sub


"Neon520" wrote:

Hi Joel,

Sorry to bother you Again!

But it still doesn't work for whatever reason.

I tried the keyboard shortcut that I assign and then I tried to go to Macro
and run it from there, but nothing. When I did the keyboard shortcut, the
screen just had a slight flick, and then nothing happen - no data transfer,
no change in appearance, nothing.

Any idea that I can try?

Thanks for your help.
Neon520


"Joel" wrote:

You lost the last slash on the folder name. My code has one and yours doesn't.