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