Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Data from Closed Workbooks | Excel Worksheet Functions | |||
Referencing Data in Closed Workbooks | Excel Discussion (Misc queries) | |||
SAVING DATA TO CLOSED WORKBOOKS | Excel Worksheet Functions | |||
get data from closed workbooks! | Excel Programming | |||
Copying Data from closed workbooks | Excel Programming |