best way to proceed

[Gary Keramidas]

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.

 

[Tom Ogilvy]

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.

 

[Gary Keramidas]

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("b44").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?

 

[Tom Ogilvy]

With Workbooks("Nicole.xls").Worksheets("Sep")
..Range("b44,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.

 

[Gary Keramidas]

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("B44,G4:H4").Copy _
Destination:= _
Workbooks("loans.xls").Worksheets("Sheet1").Range("B" & FirstRow)
End If
End With
FirstRow = FirstRow + 1

 

[Gary Keramidas]

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("B44,G4:H4").Copy

 

[Gary Keramidas]

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

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

 

[Tom Ogilvy]

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

 

[Gary Keramidas]

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 _

 

[Tom Ogilvy]

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

 

[Gary Keramidas]

i get it, thanks for the explanation

--


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