Hi

I have a daily template that is completed by some team members, this has to
be in a format that is easy to read and use for people not used to
spreadsheets.

However, I need to transfer the data to the main spreadsheet, in a different
format so that it can be used to generate reports. I want to use excel as
those that will be using the reports don't need to be trained.

The input sheet will have a unique date on the form, this is also already
present in the main workbook, and will act as the key to link the data
extracted to the correct location.

The data is in the form of an easy questionnaire, but needs to be translated
into a single row of information for each date. The format of the source and
destination will not change, but the data will need to be placed against the
date row of the destination sheet.

So the question is, how do I automatically transfer the information from the
first (remembering that there will be around 20 of these each month) to the
second (only ever one)?

You could use a macro like this - just change the specifics, of course. The
code goes into the summary workbook, which has a sheet name "Summary Sheet"
with dates down column A. The data files have the key date in cell B1 of
the default worksheet. And then the values in cellsC1, F16, H3, I9, and B12
are transfered to columns B, C, D, E, and F of the summary sheet.

Sub PullData()
Dim myR As Long
Dim mySR As Range
Dim myD As Range

Workbooks.Open Application.GetOpenFilename(, , "Select the workbook")

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 Sub



HTH,
Bernie
MS Excel MVP


"danish404" <(E-Mail Removed)> wrote in message
news:38A4EBDB-8600-4962-B5B6-(E-Mail Removed)...
> Hi
>
> I have a daily template that is completed by some team members, this has
> to
> be in a format that is easy to read and use for people not used to
> spreadsheets.
>
> However, I need to transfer the data to the main spreadsheet, in a
> different
> format so that it can be used to generate reports. I want to use excel as
> those that will be using the reports don't need to be trained.
>
> The input sheet will have a unique date on the form, this is also already
> present in the main workbook, and will act as the key to link the data
> extracted to the correct location.
>
> The data is in the form of an easy questionnaire, but needs to be
> translated
> into a single row of information for each date. The format of the source
> and
> destination will not change, but the data will need to be placed against
> the
> date row of the destination sheet.
>
> So the question is, how do I automatically transfer the information from
> the
> first (remembering that there will be around 20 of these each month) to
> the
> second (only ever one)?

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!

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" <(E-Mail Removed)> wrote in message
news:E5DFE09F-B560-44D7-872A-(E-Mail Removed)...
> 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!