best way to proceed

what would be the best way to proceed with this?

i want to set up a separate workbook that:

1. looks up values in 10+ workbooks, all in the same folder
2. each workbook has 12 sheets, named first 3 letter of each month.
3. i want to pull data by month for all 10+ sheets
4. the lookup range the data is in is fixed, c4:h56
5. i need columns b:d and g:h for each row if the value in column h >0,
putting these values on a sheet in the new workbook starting in b4:f4,
listing one after the other.

i'll probably have a data validation drop down that gives me the left 3
characters of the month.
i have a hidden sheet with all of the filenames to loop through, there are
other files in the folder and i only want to use these in the list.

do i loop with vlookup?

thanks for any insight.
--


Gary

You will need to open each workbook and extract the data. Also, I don't
really see a role for vlookup. You would just go through the data and find
what meets the critieria.

--
Regards,
Tom Ogilvy



"Gary Keramidas" <GKeramidas@msn.com> wrote in message
news:Oye3C9ayFHA.2644@TK2MSFTNGP09.phx.gbl...
> what would be the best way to proceed with this?
>
> i want to set up a separate workbook that:
>
> 1. looks up values in 10+ workbooks, all in the same folder
> 2. each workbook has 12 sheets, named first 3 letter of each month.
> 3. i want to pull data by month for all 10+ sheets
> 4. the lookup range the data is in is fixed, c4:h56
> 5. i need columns b:d and g:h for each row if the value in column h >0,
> putting these values on a sheet in the new workbook starting in b4:f4,
> listing one after the other.
>
> i'll probably have a data validation drop down that gives me the left 3
> characters of the month.
> i have a hidden sheet with all of the filenames to loop through, there are
> other files in the folder and i only want to use these in the list.
>
> do i loop with vlookup?
>
> thanks for any insight.
> --
>
>
> Gary
>
>
>

ok, thanks a lot. couple other questions then:
i just tried to hard code 1 item to test, is this how i should proceed?

With Workbooks("Nicole.xls").Worksheets("Sep")
..Activate
..Range("b4:D4").Copy
Destination:=Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
End With

i don't think i can copy b4:d4 and g4:h4 at the same time, can i?
--


Gary


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:uIHcPDbyFHA.2932@TK2MSFTNGP10.phx.gbl...
> You will need to open each workbook and extract the data. Also, I don't
> really see a role for vlookup. You would just go through the data and
> find
> what meets the critieria.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> news:Oye3C9ayFHA.2644@TK2MSFTNGP09.phx.gbl...
>> what would be the best way to proceed with this?
>>
>> i want to set up a separate workbook that:
>>
>> 1. looks up values in 10+ workbooks, all in the same folder
>> 2. each workbook has 12 sheets, named first 3 letter of each month.
>> 3. i want to pull data by month for all 10+ sheets
>> 4. the lookup range the data is in is fixed, c4:h56
>> 5. i need columns b:d and g:h for each row if the value in column h >0,
>> putting these values on a sheet in the new workbook starting in b4:f4,
>> listing one after the other.
>>
>> i'll probably have a data validation drop down that gives me the left 3
>> characters of the month.
>> i have a hidden sheet with all of the filenames to loop through, there
>> are
>> other files in the folder and i only want to use these in the list.
>>
>> do i loop with vlookup?
>>
>> thanks for any insight.
>> --
>>
>>
>> Gary
>>
>>
>>
>
>

With Workbooks("Nicole.xls").Worksheets("Sep")
..Range("b4:D4,g4:h4").Copy _
Destination:= _
Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
End With

If you want to paste the cells contiguously you should be able to do it.

--
Regards,
Tom Ogilvy


"Gary Keramidas" <GKeramidas@msn.com> wrote in message
news:u$g4ZPbyFHA.2848@TK2MSFTNGP15.phx.gbl...
> ok, thanks a lot. couple other questions then:
> i just tried to hard code 1 item to test, is this how i should proceed?
>
> With Workbooks("Nicole.xls").Worksheets("Sep")
> .Activate
> .Range("b4:D4").Copy
> Destination:=Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
> End With
>
> i don't think i can copy b4:d4 and g4:h4 at the same time, can i?
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:uIHcPDbyFHA.2932@TK2MSFTNGP10.phx.gbl...
> > You will need to open each workbook and extract the data. Also, I don't
> > really see a role for vlookup. You would just go through the data and
> > find
> > what meets the critieria.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> > "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> > news:Oye3C9ayFHA.2644@TK2MSFTNGP09.phx.gbl...
> >> what would be the best way to proceed with this?
> >>
> >> i want to set up a separate workbook that:
> >>
> >> 1. looks up values in 10+ workbooks, all in the same folder
> >> 2. each workbook has 12 sheets, named first 3 letter of each month.
> >> 3. i want to pull data by month for all 10+ sheets
> >> 4. the lookup range the data is in is fixed, c4:h56
> >> 5. i need columns b:d and g:h for each row if the value in column h >0,
> >> putting these values on a sheet in the new workbook starting in b4:f4,
> >> listing one after the other.
> >>
> >> i'll probably have a data validation drop down that gives me the left 3
> >> characters of the month.
> >> i have a hidden sheet with all of the filenames to loop through, there
> >> are
> >> other files in the folder and i only want to use these in the list.
> >>
> >> do i loop with vlookup?
> >>
> >> thanks for any insight.
> >> --
> >>
> >>
> >> Gary
> >>
> >>
> >>
> >
> >
>
>

thanks, i tired that and didn't think it worked, but it was because g4:h4
were blank

anyway, here's what i have so far, hopefully one last question will get me
by,

how do i loop through all the cells in h4:h56? i tried:
For Each cell In .Range("h4:h56"), but it didn't seem to work

With Workbooks("Nicole.xls").Worksheets("Sep")
If .Range("H4") > 0 Then
..Range("B4:D4,G4:H4").Copy _
Destination:= _
Workbooks("loans.xls").Worksheets("Sheet1").Range("B" & FirstRow)
End If
End With
FirstRow = FirstRow + 1

--


Gary


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:ODhepabyFHA.156@tk2msftngp13.phx.gbl...
> With Workbooks("Nicole.xls").Worksheets("Sep")
> .Range("b4:D4,g4:h4").Copy _
> Destination:= _
> Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
> End With
>
> If you want to paste the cells contiguously you should be able to do it.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> news:u$g4ZPbyFHA.2848@TK2MSFTNGP15.phx.gbl...
>> ok, thanks a lot. couple other questions then:
>> i just tried to hard code 1 item to test, is this how i should proceed?
>>
>> With Workbooks("Nicole.xls").Worksheets("Sep")
>> .Activate
>> .Range("b4:D4").Copy
>> Destination:=Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
>> End With
>>
>> i don't think i can copy b4:d4 and g4:h4 at the same time, can i?
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:uIHcPDbyFHA.2932@TK2MSFTNGP10.phx.gbl...
>> > You will need to open each workbook and extract the data. Also, I
>> > don't
>> > really see a role for vlookup. You would just go through the data and
>> > find
>> > what meets the critieria.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "Gary Keramidas" <GKeramidas@msn.com> wrote in message
>> > news:Oye3C9ayFHA.2644@TK2MSFTNGP09.phx.gbl...
>> >> what would be the best way to proceed with this?
>> >>
>> >> i want to set up a separate workbook that:
>> >>
>> >> 1. looks up values in 10+ workbooks, all in the same folder
>> >> 2. each workbook has 12 sheets, named first 3 letter of each month.
>> >> 3. i want to pull data by month for all 10+ sheets
>> >> 4. the lookup range the data is in is fixed, c4:h56
>> >> 5. i need columns b:d and g:h for each row if the value in column h
>> >> >0,
>> >> putting these values on a sheet in the new workbook starting in b4:f4,
>> >> listing one after the other.
>> >>
>> >> i'll probably have a data validation drop down that gives me the left
>> >> 3
>> >> characters of the month.
>> >> i have a hidden sheet with all of the filenames to loop through, there
>> >> are
>> >> other files in the folder and i only want to use these in the list.
>> >>
>> >> do i loop with vlookup?
>> >>
>> >> thanks for any insight.
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

ok, i have thi worked out
Set rng = .Range("h4:h56")
For Each Cell In rng

now, should i use cell.offset to select my range to copy to replace the
absolute references here?
..Range("B4:D4,G4:H4").Copy
--


Gary


"Gary Keramidas" <GKeramidas@msn.com> wrote in message
news:uI8YYibyFHA.3696@TK2MSFTNGP10.phx.gbl...
> thanks, i tired that and didn't think it worked, but it was because g4:h4
> were blank
>
> anyway, here's what i have so far, hopefully one last question will get me
> by,
>
> how do i loop through all the cells in h4:h56? i tried:
> For Each cell In .Range("h4:h56"), but it didn't seem to work
>
> With Workbooks("Nicole.xls").Worksheets("Sep")
> If .Range("H4") > 0 Then
> .Range("B4:D4,G4:H4").Copy _
> Destination:= _
> Workbooks("loans.xls").Worksheets("Sheet1").Range("B" & FirstRow)
> End If
> End With
> FirstRow = FirstRow + 1
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:ODhepabyFHA.156@tk2msftngp13.phx.gbl...
>> With Workbooks("Nicole.xls").Worksheets("Sep")
>> .Range("b4:D4,g4:h4").Copy _
>> Destination:= _
>> Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
>> End With
>>
>> If you want to paste the cells contiguously you should be able to do it.
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
>> news:u$g4ZPbyFHA.2848@TK2MSFTNGP15.phx.gbl...
>>> ok, thanks a lot. couple other questions then:
>>> i just tried to hard code 1 item to test, is this how i should proceed?
>>>
>>> With Workbooks("Nicole.xls").Worksheets("Sep")
>>> .Activate
>>> .Range("b4:D4").Copy
>>> Destination:=Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
>>> End With
>>>
>>> i don't think i can copy b4:d4 and g4:h4 at the same time, can i?
>>> --
>>>
>>>
>>> Gary
>>>
>>>
>>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>>> news:uIHcPDbyFHA.2932@TK2MSFTNGP10.phx.gbl...
>>> > You will need to open each workbook and extract the data. Also, I
>>> > don't
>>> > really see a role for vlookup. You would just go through the data and
>>> > find
>>> > what meets the critieria.
>>> >
>>> > --
>>> > Regards,
>>> > Tom Ogilvy
>>> >
>>> >
>>> >
>>> > "Gary Keramidas" <GKeramidas@msn.com> wrote in message
>>> > news:Oye3C9ayFHA.2644@TK2MSFTNGP09.phx.gbl...
>>> >> what would be the best way to proceed with this?
>>> >>
>>> >> i want to set up a separate workbook that:
>>> >>
>>> >> 1. looks up values in 10+ workbooks, all in the same folder
>>> >> 2. each workbook has 12 sheets, named first 3 letter of each month.
>>> >> 3. i want to pull data by month for all 10+ sheets
>>> >> 4. the lookup range the data is in is fixed, c4:h56
>>> >> 5. i need columns b:d and g:h for each row if the value in column h
>>> >> >0,
>>> >> putting these values on a sheet in the new workbook starting in
>>> >> b4:f4,
>>> >> listing one after the other.
>>> >>
>>> >> i'll probably have a data validation drop down that gives me the left
>>> >> 3
>>> >> characters of the month.
>>> >> i have a hidden sheet with all of the filenames to loop through,
>>> >> there
>>> >> are
>>> >> other files in the folder and i only want to use these in the list.
>>> >>
>>> >> do i loop with vlookup?
>>> >>
>>> >> thanks for any insight.
>>> >> --
>>> >>
>>> >>
>>> >> Gary
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>
>>
>
>

this seems to work:

With Workbooks("Nicole.xls").Worksheets("Sep")
Set rng = .Range("h4:h56")
For Each Cell In rng
If Cell.Value > 0 Then
..Range("b" & Cell.Row & ":d" & Cell.Row & ",g" & Cell.Row & ":h" &
Cell.Row).Copy _
Destination:= _
Workbooks("loans.xls").Worksheets("sheet1").Range("b" & FirstRow)
FirstRow = FirstRow + 1
End If
Debug.Print Cell.Row
'Debug.Print Rows(Cell.Address)
Next

End With

--


Gary


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:ODhepabyFHA.156@tk2msftngp13.phx.gbl...
> With Workbooks("Nicole.xls").Worksheets("Sep")
> .Range("b4:D4,g4:h4").Copy _
> Destination:= _
> Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
> End With
>
> If you want to paste the cells contiguously you should be able to do it.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> news:u$g4ZPbyFHA.2848@TK2MSFTNGP15.phx.gbl...
>> ok, thanks a lot. couple other questions then:
>> i just tried to hard code 1 item to test, is this how i should proceed?
>>
>> With Workbooks("Nicole.xls").Worksheets("Sep")
>> .Activate
>> .Range("b4:D4").Copy
>> Destination:=Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
>> End With
>>
>> i don't think i can copy b4:d4 and g4:h4 at the same time, can i?
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:uIHcPDbyFHA.2932@TK2MSFTNGP10.phx.gbl...
>> > You will need to open each workbook and extract the data. Also, I
>> > don't
>> > really see a role for vlookup. You would just go through the data and
>> > find
>> > what meets the critieria.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "Gary Keramidas" <GKeramidas@msn.com> wrote in message
>> > news:Oye3C9ayFHA.2644@TK2MSFTNGP09.phx.gbl...
>> >> what would be the best way to proceed with this?
>> >>
>> >> i want to set up a separate workbook that:
>> >>
>> >> 1. looks up values in 10+ workbooks, all in the same folder
>> >> 2. each workbook has 12 sheets, named first 3 letter of each month.
>> >> 3. i want to pull data by month for all 10+ sheets
>> >> 4. the lookup range the data is in is fixed, c4:h56
>> >> 5. i need columns b:d and g:h for each row if the value in column h
>> >> >0,
>> >> putting these values on a sheet in the new workbook starting in b4:f4,
>> >> listing one after the other.
>> >>
>> >> i'll probably have a data validation drop down that gives me the left
>> >> 3
>> >> characters of the month.
>> >> i have a hidden sheet with all of the filenames to loop through, there
>> >> are
>> >> other files in the folder and i only want to use these in the list.
>> >>
>> >> do i loop with vlookup?
>> >>
>> >> thanks for any insight.
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

i think i pretty much jave this worked out, thanks tom

Sub CopyLoan()
FirstRow = 4
i = 1
For i = 1 To 14
fPath = "N:\My Documents\Excel\RECCU\FSA\"
Fname = Worksheets("fsa").Cells(i, "B").Value
Debug.Print fPath & Fname
Workbooks.Open Filename:=fPath & Fname, _
UpdateLinks:=3

With Workbooks(Fname).Worksheets("Sep")
Set rng = .Range("h4:h56")
For Each Cell In rng
If Cell.Value > 0 Then
..Range("b" & Cell.Row & ":d" & Cell.Row & ",g" & Cell.Row & ":h" &
Cell.Row).Copy _
Destination:= _
Workbooks("loans.xls").Worksheets("sheet1").Range("b" & FirstRow)
FirstRow = FirstRow + 1
End If
Debug.Print Cell.Row
Next

End With
ActiveWorkbook.Close SaveChanges:=False
i = i + 1
Next i
End Sub

--


Gary


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:ODhepabyFHA.156@tk2msftngp13.phx.gbl...
> With Workbooks("Nicole.xls").Worksheets("Sep")
> .Range("b4:D4,g4:h4").Copy _
> Destination:= _
> Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
> End With
>
> If you want to paste the cells contiguously you should be able to do it.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> news:u$g4ZPbyFHA.2848@TK2MSFTNGP15.phx.gbl...
>> ok, thanks a lot. couple other questions then:
>> i just tried to hard code 1 item to test, is this how i should proceed?
>>
>> With Workbooks("Nicole.xls").Worksheets("Sep")
>> .Activate
>> .Range("b4:D4").Copy
>> Destination:=Workbooks("loans.xls").Worksheets("sheet1").Range("b4")
>> End With
>>
>> i don't think i can copy b4:d4 and g4:h4 at the same time, can i?
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:uIHcPDbyFHA.2932@TK2MSFTNGP10.phx.gbl...
>> > You will need to open each workbook and extract the data. Also, I
>> > don't
>> > really see a role for vlookup. You would just go through the data and
>> > find
>> > what meets the critieria.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> >
>> > "Gary Keramidas" <GKeramidas@msn.com> wrote in message
>> > news:Oye3C9ayFHA.2644@TK2MSFTNGP09.phx.gbl...
>> >> what would be the best way to proceed with this?
>> >>
>> >> i want to set up a separate workbook that:
>> >>
>> >> 1. looks up values in 10+ workbooks, all in the same folder
>> >> 2. each workbook has 12 sheets, named first 3 letter of each month.
>> >> 3. i want to pull data by month for all 10+ sheets
>> >> 4. the lookup range the data is in is fixed, c4:h56
>> >> 5. i need columns b:d and g:h for each row if the value in column h
>> >> >0,
>> >> putting these values on a sheet in the new workbook starting in b4:f4,
>> >> listing one after the other.
>> >>
>> >> i'll probably have a data validation drop down that gives me the left
>> >> 3
>> >> characters of the month.
>> >> i have a hidden sheet with all of the filenames to loop through, there
>> >> are
>> >> other files in the folder and i only want to use these in the list.
>> >>
>> >> do i loop with vlookup?
>> >>
>> >> thanks for any insight.
>> >> --
>> >>
>> >>
>> >> Gary
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>