Data transfer, from template to main speadsheet
danish,
The double stops is an artifact introduced by some newsreaders... but you
did the right thing to take one out. Anyway, to process mutliple files, you
can do something like this, assuming that each of the data files is a single
sheet, or if the active sheet is the desired sheet. Uncomment out the line
'myB.Worksheets("Sheet Name with the data").Select
and change the sheet name if that is not the case:
Sub PullData2()
Dim myR As Long
Dim mySR As Range
Dim myD As Range
Dim myArr As Variant
Dim i As Integer
Dim myB As Workbook
myArr = Application.GetOpenFilename(, , "Select all the workbooks", , True)
For i = LBound(myArr) To UBound(myArr)
Set myB = Workbooks.Open(myArr(i))
'myB.Worksheets("Sheet Name with the data").Select
'Code from here is the same as what you had previously
Set myD = Range("B1")
Set mySR = ThisWorkbook.Worksheets("Summary Sheet").Range("A:A")
myR = WorksheetFunction.Match(CDbl(myD.Value), mySR, False)
With ThisWorkbook.Worksheets("Summary Sheet")
..Range("B" & myR).Formula = Range("C1").Value
..Range("C" & myR).Formula = Range("F16").Value
..Range("D" & myR).Formula = Range("H3").Value
..Range("E" & myR).Formula = Range("I9").Value
..Range("F" & myR).Formula = Range("B12").Value
End With
'End of code from before
myB.Close False
Next i
End Sub
HTH,
Bernie
MS Excel MVP
"danish404" wrote in message
...
Berine,
sorry for not replying quickly. Tried this out and it works a treat,
changed the variables, added a few more lines does the job fantastic!
(did
remove one of the stops at the beginning of this line "..Range("B" &
myR).Formula = Range("C1").Value" but that might be due to my version of
excel, but I'm not sure 2003)
one additional question if I may, and if you have time, how do i make it
open more that one input sheet at a time and work its magic? (they all
have
different names)
but thats for all your help is solving this, great job!
|