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.
> >
>

THANK YOU SO MUCH, JOEL! I can never say thank you enough!

You know what I found out? Remember I told you that I'm on a Mac. I did a
little google search and found out that file directory is written in : not /
or \ on a Mac! I changed that and voila, it works!

Now, if you don't mind, I would like to ask you a few more questions to get
it to work the way I need.
1. Can I grab the data in other workbooks without open them? My concern is
if the user make changes to Workbook1 and didn't save and close it, there
will be debugging error.
2. Can you modify the code so that it will check ALL workbooks in a
Particular Folder (TEST FOLDER) regardless of names? I tried FName =
Dir(Folder & "*.xls"), but it didn't work. It only worked when I put in
Workbook1.xls.
3. Is there a way to select Particular Cell/row/column OR starting at
particular cell/row in Workbook1, instead of checking the whole column?
4. Is there a way to place the data that has been picked in a Particular
cell/row instead of starting in A1?

Lastly, Is this a "good" setup for my purpose of having a summary sheet to
work on? I don't want to place all Account in one giant workbook with 20+
sheets and one summary sheet. That's why I want to create one workbook for
each account and have this code that will collect a particular information to
a separate summary workbook. If in case of file lost of data corruption, not
all eggs are in one basket. Do you think that this is a good strategy? Or
is there a better way to do this?

THANK YOU SOOO MUCH FOR YOUR HELP, JOEL.
Neon520


"Joel" wrote:

> 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
>

See responses below

"Neon520" wrote:

> THANK YOU SO MUCH, JOEL! I can never say thank you enough!
>
> You know what I found out? Remember I told you that I'm on a Mac. I did a
> little google search and found out that file directory is written in : not /
> or \ on a Mac! I changed that and voila, it works!
>
> Now, if you don't mind, I would like to ask you a few more questions to get
> it to work the way I need.
> 1. Can I grab the data in other workbooks without open them? My concern is
> if the user make changes to Workbook1 and didn't save and close it, there
> will be debugging error.

There is a method reading workbooks without opening them using database
commands (excel and access files use similar methods of storing data) but I
would think using the not opening a file will give the same errors.



> 2. Can you modify the code so that it will check ALL workbooks in a
> Particular Folder (TEST FOLDER) regardless of names? I tried FName =
> Dir(Folder & "*.xls"), but it didn't work. It only worked when I put in
> Workbook1.xls.

Using my debug msgbox messages was anythiing returned when you used the
wildcard. Haven't used Macs very often and im not familar with the wildcard
in Mac. Thought it was a *. Look more into Macs using the DIR() command and
see if you can find out how to use a wildcard.

> 3. Is there a way to select Particular Cell/row/column OR starting at
> particular cell/row in Workbook1, instead of checking the whole column?

The start row is controlled by this statement

OldRowCount = 1

Depending on the number of columns you want copied there are different
methods of selecting columns. You can always delete columns after the code
is run

you can use this change

from
If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
.Rows(OldRowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)
NewRowCount = NewRowCount + 1
End If

to

If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
NewSht.Range("A" & Newrowcount) = .Range("C" & Oldrowcount)
NewSht.Range("B" & Newrowcount) = .Range("F" & Oldrowcount)
NewSht.Range("C" & Newrowcount) = .Range("K" & Oldrowcount)
NewSht.Range("D" & Newrowcount) = .Range("O" & Oldrowcount)

NewRowCount = NewRowCount + 1
End If



> 4. Is there a way to place the data that has been picked in a Particular
> cell/row instead of starting in A1?

Newrowcount sets where the the 1st row where the data is copied to.

NewRowCount = 1


>
> Lastly, Is this a "good" setup for my purpose of having a summary sheet to
> work on? I don't want to place all Account in one giant workbook with 20+
> sheets and one summary sheet. That's why I want to create one workbook for
> each account and have this code that will collect a particular information to
> a separate summary workbook. If in case of file lost of data corruption, not
> all eggs are in one basket. Do you think that this is a good strategy? Or
> is there a better way to do this?
>
> THANK YOU SOOO MUCH FOR YOUR HELP, JOEL.
> Neon520
>
>
> "Joel" wrote:
>
> > 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
> >
>

Hi Joel,

I have a few more questions for you if you don't mind my low level of
programming experience.

Is there a way to specify the column? It’s in the case that I need to
transfer from Old worksheet Column A to New worksheet Column B instead?
Basically not everything comes in a column-by-column order; Old Column B can
be transferred to Column D instead. So it’s varied. And it’s only for
particular column, not all of them.

What do I have to do if I need to copy the “Value” ONLY because there are
formulas in the New sheet already for other calculation? I don't want to
copy the Format of the cells along with them.

I know that we "HARD CODE" the criteria of searching for a particular month
before copying the data, can we somehow "SOFT CODE" it instead?
Like I said earlier, I'm a newbie in this programming for excel, one dummy
trick that I always use is pointing it to a particular cell - the cell that
formatted to be a drop down list of all 12 months - so that user can select a
particular month that they need to do a summary sheet of. I'm hopping to do
that and implement a BUTTON that is link with the code, so that the user can
select the month and then press the button to execute. What do you think?

THANK YOU SO MUCH FOR ALL OF YOUR PREVIOUS ANSWERS!!!!

Neon520




"Joel" wrote:

> See responses below
>
> "Neon520" wrote:
>
> > THANK YOU SO MUCH, JOEL! I can never say thank you enough!
> >
> > You know what I found out? Remember I told you that I'm on a Mac. I did a
> > little google search and found out that file directory is written in : not /
> > or \ on a Mac! I changed that and voila, it works!
> >
> > Now, if you don't mind, I would like to ask you a few more questions to get
> > it to work the way I need.
> > 1. Can I grab the data in other workbooks without open them? My concern is
> > if the user make changes to Workbook1 and didn't save and close it, there
> > will be debugging error.
>
> There is a method reading workbooks without opening them using database
> commands (excel and access files use similar methods of storing data) but I
> would think using the not opening a file will give the same errors.
>
>
>
> > 2. Can you modify the code so that it will check ALL workbooks in a
> > Particular Folder (TEST FOLDER) regardless of names? I tried FName =
> > Dir(Folder & "*.xls"), but it didn't work. It only worked when I put in
> > Workbook1.xls.
>
> Using my debug msgbox messages was anythiing returned when you used the
> wildcard. Haven't used Macs very often and im not familar with the wildcard
> in Mac. Thought it was a *. Look more into Macs using the DIR() command and
> see if you can find out how to use a wildcard.
>
> > 3. Is there a way to select Particular Cell/row/column OR starting at
> > particular cell/row in Workbook1, instead of checking the whole column?
>
> The start row is controlled by this statement
>
> OldRowCount = 1
>
> Depending on the number of columns you want copied there are different
> methods of selecting columns. You can always delete columns after the code
> is run
>
> you can use this change
>
> from
> If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
> .Rows(OldRowCount).Copy _
> Destination:=NewSht.Rows(NewRowCount)
> NewRowCount = NewRowCount + 1
> End If
>
> to
>
> If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
> NewSht.Range("A" & Newrowcount) = .Range("C" & Oldrowcount)
> NewSht.Range("B" & Newrowcount) = .Range("F" & Oldrowcount)
> NewSht.Range("C" & Newrowcount) = .Range("K" & Oldrowcount)
> NewSht.Range("D" & Newrowcount) = .Range("O" & Oldrowcount)
>
> NewRowCount = NewRowCount + 1
> End If
>
>
>
> > 4. Is there a way to place the data that has been picked in a Particular
> > cell/row instead of starting in A1?
>
> Newrowcount sets where the the 1st row where the data is copied to.
>
> NewRowCount = 1
>
>
> >
> > Lastly, Is this a "good" setup for my purpose of having a summary sheet to
> > work on? I don't want to place all Account in one giant workbook with 20+
> > sheets and one summary sheet. That's why I want to create one workbook for
> > each account and have this code that will collect a particular information to
> > a separate summary workbook. If in case of file lost of data corruption, not
> > all eggs are in one basket. Do you think that this is a good strategy? Or
> > is there a better way to do this?
> >
> > THANK YOU SOOO MUCH FOR YOUR HELP, JOEL.
> > Neon520
> >
> >
> > "Joel" wrote:
> >
> > > 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
> > >
> >

The code below copies only the values and not the formulas or formating. the
code also copies from one column to any other column.

If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
NewSht.Range("A" & Newrowcount) = .Range("C" & Oldrowcount)
NewSht.Range("B" & Newrowcount) = .Range("F" & Oldrowcount)
NewSht.Range("C" & Newrowcount) = .Range("K" & Oldrowcount)
NewSht.Range("D" & Newrowcount) = .Range("O" & Oldrowcount)

NewRowCount = NewRowCount + 1
End If

You can also use PasteSpecial to copy just the values


Range("A1:A20").Copy
Range("D1").PasteSpecial Paste:=xlPasteValues


Using the Copy method copies the formating as well as the data

.Rows(OldRowCount).Copy _
Destination:=NewSht.Rows(NewRowCount)


The code below I changed December from hard coded to soft coded by assigning
the month to a variable. "December" is a string because it has double quotes
around the month name. You can assign the month to a variable as shown
below. You can also copy the month from an input box as shown below

method 1
MyMonth = Inputbox("enter name of month : ")

method 2
Mymonth = "December"

here is method 2 in the code below



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

Mymonth = "DECEMBER"

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)) = Mymonth 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,
>
> I have a few more questions for you if you don't mind my low level of
> programming experience.
>
> Is there a way to specify the column? It’s in the case that I need to
> transfer from Old worksheet Column A to New worksheet Column B instead?
> Basically not everything comes in a column-by-column order; Old Column B can
> be transferred to Column D instead. So it’s varied. And it’s only for
> particular column, not all of them.
>
> What do I have to do if I need to copy the “Value” ONLY because there are
> formulas in the New sheet already for other calculation? I don't want to
> copy the Format of the cells along with them.
>
> I know that we "HARD CODE" the criteria of searching for a particular month
> before copying the data, can we somehow "SOFT CODE" it instead?
> Like I said earlier, I'm a newbie in this programming for excel, one dummy
> trick that I always use is pointing it to a particular cell - the cell that
> formatted to be a drop down list of all 12 months - so that user can select a
> particular month that they need to do a summary sheet of. I'm hopping to do
> that and implement a BUTTON that is link with the code, so that the user can
> select the month and then press the button to execute. What do you think?
>
> THANK YOU SO MUCH FOR ALL OF YOUR PREVIOUS ANSWERS!!!!
>
> Neon520
>
>
>
>
> "Joel" wrote:
>
> > See responses below
> >
> > "Neon520" wrote:
> >
> > > THANK YOU SO MUCH, JOEL! I can never say thank you enough!
> > >
> > > You know what I found out? Remember I told you that I'm on a Mac. I did a
> > > little google search and found out that file directory is written in : not /
> > > or \ on a Mac! I changed that and voila, it works!
> > >
> > > Now, if you don't mind, I would like to ask you a few more questions to get
> > > it to work the way I need.
> > > 1. Can I grab the data in other workbooks without open them? My concern is
> > > if the user make changes to Workbook1 and didn't save and close it, there
> > > will be debugging error.
> >
> > There is a method reading workbooks without opening them using database
> > commands (excel and access files use similar methods of storing data) but I
> > would think using the not opening a file will give the same errors.
> >
> >
> >
> > > 2. Can you modify the code so that it will check ALL workbooks in a
> > > Particular Folder (TEST FOLDER) regardless of names? I tried FName =
> > > Dir(Folder & "*.xls"), but it didn't work. It only worked when I put in
> > > Workbook1.xls.
> >
> > Using my debug msgbox messages was anythiing returned when you used the
> > wildcard. Haven't used Macs very often and im not familar with the wildcard
> > in Mac. Thought it was a *. Look more into Macs using the DIR() command and
> > see if you can find out how to use a wildcard.
> >
> > > 3. Is there a way to select Particular Cell/row/column OR starting at
> > > particular cell/row in Workbook1, instead of checking the whole column?
> >
> > The start row is controlled by this statement
> >
> > OldRowCount = 1
> >
> > Depending on the number of columns you want copied there are different
> > methods of selecting columns. You can always delete columns after the code
> > is run
> >
> > you can use this change
> >
> > from
> > If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
> > .Rows(OldRowCount).Copy _
> > Destination:=NewSht.Rows(NewRowCount)
> > NewRowCount = NewRowCount + 1
> > End If
> >
> > to
> >
> > If UCase(.Range("B" & OldRowCount)) = "DECEMBER" Then
> > NewSht.Range("A" & Newrowcount) = .Range("C" & Oldrowcount)
> > NewSht.Range("B" & Newrowcount) = .Range("F" & Oldrowcount)
> > NewSht.Range("C" & Newrowcount) = .Range("K" & Oldrowcount)
> > NewSht.Range("D" & Newrowcount) = .Range("O" & Oldrowcount)
> >
> > NewRowCount = NewRowCount + 1
> > End If
> >
> >
> >
> > > 4. Is there a way to place the data that has been picked in a Particular
> > > cell/row instead of starting in A1?
> >
> > Newrowcount sets where the the 1st row where the data is copied to.
> >
> > NewRowCount = 1
> >
> >
> > >
> > > Lastly, Is this a "good" setup for my purpose of having a summary sheet to
> > > work on? I don't want to place all Account in one giant workbook with 20+
> > > sheets and one summary sheet. That's why I want to create one workbook for
> > > each account and have this code that will collect a particular information to
> > > a separate summary workbook. If in case of file lost of data corruption, not
> > > all eggs are in one basket. Do you think that this is a good strategy? Or
> > > is there a better way to do this?
> > >
> > > THANK YOU SOOO MUCH FOR YOUR HELP, JOEL.
> > > Neon520
> > >
> > >
> > > "Joel" wrote:
> > >
> > > > 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
> > > >
> > >