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

the easiest is to anchor to and offset from Cell

for each cell in rng
cell.Offset(0,-6).Range("A1:C1,F1:G1").copy _
Destination:=

to illustrate from the immediate window:

? Range("h5").Offset(0,-6).Range("A1:C1,F1:G1").Address
$B$5:$D$5,$G$5:$H$5

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidas@msn.com> wrote in message
news:eu1mbqbyFHA.3892@TK2MSFTNGP12.phx.gbl...
> 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
> >>> >>
> >>> >>
> >>> >>
> >>> >
> >>> >
> >>>
> >>>
> >>
> >>
> >
> >
>
>

i see your code works just like mine and a lot more compact, but why does
the reference to (a1:c1,f1:g1) work?

i used this
..Range("b" & Cell.Row & ":d" & Cell.Row & ",g" & Cell.Row & ":h" &
Cell.Row).Copy _

--


Gary


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:u2e0eMcyFHA.2812@TK2MSFTNGP14.phx.gbl...
> the easiest is to anchor to and offset from Cell
>
> for each cell in rng
> cell.Offset(0,-6).Range("A1:C1,F1:G1").copy _
> Destination:=
>
> to illustrate from the immediate window:
>
> ? Range("h5").Offset(0,-6).Range("A1:C1,F1:G1").Address
> $B$5:$D$5,$G$5:$H$5
>
> --
> Regards,
> Tom Ogilvy
>
> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> news:eu1mbqbyFHA.3892@TK2MSFTNGP12.phx.gbl...
>> 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
>> >>> >>
>> >>> >>
>> >>> >>
>> >>> >
>> >>> >
>> >>>
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>
>

because it is relative to Cell

Range("B9").Range("A1") is B9
Range("B9").Range("B2") is C10

for example. See the pattern.

demo'd from the immediate window:
? Range("B9").Range("A1").Address
$B$9
? Range("B9").Range("B2").Address
$C$10


--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidas@msn.com> wrote in message
news:OhdcWfcyFHA.3864@TK2MSFTNGP12.phx.gbl...
> i see your code works just like mine and a lot more compact, but why does
> the reference to (a1:c1,f1:g1) work?
>
> i used this
> .Range("b" & Cell.Row & ":d" & Cell.Row & ",g" & Cell.Row & ":h" &
> Cell.Row).Copy _
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:u2e0eMcyFHA.2812@TK2MSFTNGP14.phx.gbl...
> > the easiest is to anchor to and offset from Cell
> >
> > for each cell in rng
> > cell.Offset(0,-6).Range("A1:C1,F1:G1").copy _
> > Destination:=
> >
> > to illustrate from the immediate window:
> >
> > ? Range("h5").Offset(0,-6).Range("A1:C1,F1:G1").Address
> > $B$5:$D$5,$G$5:$H$5
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> > news:eu1mbqbyFHA.3892@TK2MSFTNGP12.phx.gbl...
> >> 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
> >> >>> >>
> >> >>> >>
> >> >>> >>
> >> >>> >
> >> >>> >
> >> >>>
> >> >>>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>

i get it, thanks for the explanation

--


Gary


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OGxPwqcyFHA.2644@TK2MSFTNGP09.phx.gbl...
> because it is relative to Cell
>
> Range("B9").Range("A1") is B9
> Range("B9").Range("B2") is C10
>
> for example. See the pattern.
>
> demo'd from the immediate window:
> ? Range("B9").Range("A1").Address
> $B$9
> ? Range("B9").Range("B2").Address
> $C$10
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Gary Keramidas" <GKeramidas@msn.com> wrote in message
> news:OhdcWfcyFHA.3864@TK2MSFTNGP12.phx.gbl...
>> i see your code works just like mine and a lot more compact, but why does
>> the reference to (a1:c1,f1:g1) work?
>>
>> i used this
>> .Range("b" & Cell.Row & ":d" & Cell.Row & ",g" & Cell.Row & ":h" &
>> Cell.Row).Copy _
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
>> news:u2e0eMcyFHA.2812@TK2MSFTNGP14.phx.gbl...
>> > the easiest is to anchor to and offset from Cell
>> >
>> > for each cell in rng
>> > cell.Offset(0,-6).Range("A1:C1,F1:G1").copy _
>> > Destination:=
>> >
>> > to illustrate from the immediate window:
>> >
>> > ? Range("h5").Offset(0,-6).Range("A1:C1,F1:G1").Address
>> > $B$5:$D$5,$G$5:$H$5
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> > "Gary Keramidas" <GKeramidas@msn.com> wrote in message
>> > news:eu1mbqbyFHA.3892@TK2MSFTNGP12.phx.gbl...
>> >> 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
>> >> >>> >>
>> >> >>> >>
>> >> >>> >>
>> >> >>> >
>> >> >>> >
>> >> >>>
>> >> >>>
>> >> >>
>> >> >>
>> >> >
>> >> >
>> >>
>> >>
>> >
>> >
>>
>>
>
>