Help with retriving data from other closed Workbooks
 

I have 3 workbooks(Service Techs 1-3) I keep records in. I have a 4th
workbook (RECAP)that I want to retrieve from. The three workbooks each
have worksheets that are labeled January - December.

In the Recap Workbook I have a worksheet where I keep the information
for the data that I want to retrieve(Data!C3:C5). From this information
I want to open the corresponding workbook(s) and insert into a sheet
with all pertaining records:

Example:

If c3=Tony and C4=January retrieve all rows with data in workbook
(TONY) from worksheet (January)

If c3=Ron and C4=January retrieve all rows with data in workbook (Ron)
from worksheet (January)


If C3=All and C4= February get information from all 3 workbooks as
above.

Hope this makes sense - I am using a drop down combo box for C3:C5
these cells will change depending on the report I want generated

PLEASE HELP!

Thanks in advance


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=526278

Help with retriving data from other closed Workbooks
 

As written, this clears the used area in the Sheet Data in workbook
Recap.xls from Row 10 down to the last used row.

It then puts the new data starting in row 10 of that sheet.
Sub ABCD()
Dim v As Variant, bk As Workbook, sh As Worksheet
Dim bk1 As Workbook, sh1 As Worksheet
Dim sn As String, sm As String, i As Long
Dim rng1 As Range, rng As Range
Dim rng2 as Range
Set bk = Workbooks("Recap.xls")
Set sh = bk.Worksheets("Data")
sn = LCase(sh.Range("C3").Value)
sm = sh.Range("C4").Value
If sn = "all" Then
v = Array("Tony.xls", "Jan.xls", "Humphry.xls")
Else
v = Array(sn)
End If
For i = LBound(v) To UBound(v)
Set bk1 = Workbooks.Open("C:\MyFolder\" & v(i))
Set sh1 = bk1.Worksheets(sm)
If i = LBound(v) Then
Set rng1 = sh.Range(sh.Range("A10"), sh.Cells(Rows.Count, 1).End(xlUp))
rng1.EntireRow.Delete
Set rng = sh.Range("A10")
Else
Set rng = sh.Cells(Rows.Count, 1).End(xlUp)(2)
End If
set rng2 = sh1.Range(sh1.Range("A3"), _
sh1.Cells(rows.count,1).End(xlup)).EntireRow
rng2.copy Destination:=rng
bk1.Close SaveChanges:=False
Next
End Sub



--
Regards,
Tom Ogilvy






"parteegolfer"
wrote in message
news:parteegolfer.258ipm_1143305701.2181@excelforu m-nospam.com...

I have 3 workbooks(Service Techs 1-3) I keep records in. I have a 4th
workbook (RECAP)that I want to retrieve from. The three workbooks each
have worksheets that are labeled January - December.

In the Recap Workbook I have a worksheet where I keep the information
for the data that I want to retrieve(Data!C3:C5). From this information
I want to open the corresponding workbook(s) and insert into a sheet
with all pertaining records:

Example:

If c3=Tony and C4=January retrieve all rows with data in workbook
(TONY) from worksheet (January)

If c3=Ron and C4=January retrieve all rows with data in workbook (Ron)
from worksheet (January)


If C3=All and C4= February get information from all 3 workbooks as
above.

Hope this makes sense - I am using a drop down combo box for C3:C5
these cells will change depending on the report I want generated

PLEASE HELP!

Thanks in advance


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile:
http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=526278

Help with retriving data from other closed Workbooks
 

Thanks tom

this is almost what I am looking for hoevever it is putting it in the
wrong sheet. I Need it in worksheet (Recap Report) not Data.


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile: http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=526278

Help with retriving data from other closed Workbooks
 

So replace "Data" with "Recap Report"

This is a peer to peer support group.

If you are looking for a free coding service, please so state so people can
decide if they want to provide that service or not.

--
Regards,
Tom Ogilvy




"parteegolfer"
wrote in message
news:parteegolfer.258qta_1143316202.5273@excelforu m-nospam.com...

Thanks tom

this is almost what I am looking for hoevever it is putting it in the
wrong sheet. I Need it in worksheet (Recap Report) not Data.


--
parteegolfer
------------------------------------------------------------------------
parteegolfer's Profile:
http://www.excelforum.com/member.php...o&userid=31951
View this thread: http://www.excelforum.com/showthread...hreadid=526278