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

best way to proceed
 

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" wrote in message
...
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

best way to proceed
 

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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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

best way to proceed
 

i get it, thanks for the explanation

--


Gary


"Tom Ogilvy" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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" wrote in message
...
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("sh eet1").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" wrote in message
...
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" wrote in message
...
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