Let's try a macro for this (put it in the report template):
Sub Gather_Data()
Dim strDataWorkbook, strReportWorkbook
Dim i As Long
' Set the row that data starts on (in both the data and report workbooks)
Const DATA_START_ROW = 2
Const REPORT_START_ROW = 2
i = DATA_START_ROW
' Set the current workbook to the Report Workbook
strReportWorkbook = ActiveWorkbook.Name
' Prompt for the name of the data workbook
strDataWorkbook = InputBox("Please enter worksheet name: ", "Data Sheet")
' Turn off interim screen updates for the sake of speed
Application.ScreenUpdating = False
' Switch over to the data worksheet
Worksheets(strDataWorkbook).Activate
' As long as there is not a row that has a blank in column A,
' go through the rows and copy the data over to the report workbook.
' This assumes that all the columns match between the two. If not,
' you can copy individual cells instead of the rows.
Do While Range("A" & i).Value <> ""
Rows(i).Copy
Worksheets(strReportWorkbook).Worksheets(strReportWorksheet).Rows(i + REPORT_START_ROW - DATA_START_ROW).PasteSpecial xlPasteValues
i = i + 1
Loop
' Turn interim screen updates back on
Application.ScreenUpdating = True
End Sub
This may require a little tweaking - feel free to send an e-mail if you'd like some help with that. Good luck!
