Maybe this will get you started:
'-----------------------------------------------------------
Sub Import_Time_Report_Data()
Dim StrFileName As String
Dim TmFl As Workbook
Dim TSht As Worksheet

Set TSht = ActiveSheet
StrFileName = Cells(ActiveCell.Row, "E").Value

Set TmFl = _
Workbooks.Open(Filename:="P:\TIMESHEET2006\" & StrFileName)
TmFl.Sheets("Data_Export").Range("A2:L46").Copy
ThisWorkbook.TSht.Cells(ActiveCell.Row, "A").PasteSpecial _
Format:="Text", Link:=False, DisplayAsIcon:=False
Application.CutCopyMode = False
TmFl.Close False
ThisWorkbook.TSht.ActiveCell.Offset(45, 0).Select

End Sub
'-------------------------------------------------------------------

Hope this helps
Rowan

Dena X wrote:
> I am trying to do four things via a marco:
> 1) Open a time report based on a file path AND a file name contained in a
> cell in the row my cursor is in
> 2) Copy and "paste special-values" from the time report into the master
> workbook starting at column A of the current row the cursor is in
> 3) Close the time report without getting a "do you want to save this
> message" and a "do you want to have the data you copied available for pasting
> message"
> 4) move down to the next available row, input the time report employee name
> and pay period I wish to call up and repeat steps 1 through 3 for the next
> time report
>
> Here is an example of my data:
> Sample Time Report records (data I want to copy):
> (File Name of this example is Dena_1_31_2005.xls and stored under C:Data\Jan
> both file name and path are created from concatenations of data in the time
> report)
> Name PayPeriod Hours Client
> Dena 1/31/2005 30 ABC Customer
> Dena 1/31/2005 20 Togos
> Dena 1/31/2005 15 Kaplan
>
> Example 2 - time report feb file for another employee (more date I want to
> copy in per step 4 above) File Name = Joe_2_28_2005.xls and path =
> C:\Data\Feb
>
> col A col B col c col d
> Name PayPeriod Hours Client
> Joe 2/28/2005 55 marriot
> Joe 2/28/2005 25 fairfield
> Joe 2/28/2005 11 hamburger
>
> Naturally I have time reports for multiple people and for multiple pay
> periods. There are 45 line items of time to copy in from each time report.
> I have 20 employees and have designed a template where on every 45th row I
> have inputted the employee name in column A. Column B on every 45th line of
> my template I have set equal to B1, which I have set to the pay period I wish
> to copy in. Imagine the following:
>
> cell A1= Dena
> cell B1= 1/31/2005
> cell A46 = Joe
> cell B46 = 1/31/2005
> etc, etc. for 18 other employees.
>
> Column C is blank and a placeholder for the data to copy in (hours) and
> column D is similarly blank and a placeholder to copy in the client. I then
> created a formula in column E which mirrors the exact file name where you
> could pull the data to populate all the rows and column C &D from. The
> problem I am running into is how to avoid calling a particular row. The file
> name to open and copy from is always in column E and also how do I avoid
> hardcoding the path of where to find it since the path is dictated by the pay
> period date (column B). (I need it to go open the file in the relevant month
> folder)
>
> I have been able to write a macro that will open a file with the name of the
> active cell you are clicked on when you run the macro. The macro opens the
> time report with that file name, copies the 45 rows from the data_export
> worksheet, paste special values them in starting at a hard-coded cell
> referemce. A1. I can't figure out how to say, starting in the current row,
> go to column E, open a file with the name contained in column E of the
> current row, copy the data, go to column A of the current row and
> paste-special the data, now scroll down 45 lines and then I can run the macro
> again for the new name / date.
>
> This is the macro I have so far that is stuck on certain cell reference
> (current cell is file name to open) rather than go to column E generically,
> and paste to cell is A1, rather than generic column A. ANY HELP WOULD BE
> HUGELY APPRECIATED.
> I'm happy to email you the two files if it'll make more sense....
>
> Sub Import_Time_Report_Data()
> '
> ' Macro recorded 12/15/2005 by
> '
> StrFileName = ActiveCell
>
> Workbooks.Open Filename:="P:\TIMESHEET2006\" & StrFileName
> Sheets("Data_Export").Select
> Range("A2:L46").Select
> ActiveWindow.LargeScroll Down:=-1
> Range("A164").Select
> ActiveWindow.SmallScroll Down:=-192
> Range("A2:L46").Select
> Selection.Copy
> ActiveWindow.Close
> Range("A1").Select
> ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
> False
> ActiveWindow.ScrollColumn = 2
> ActiveWindow.ScrollColumn = 3
> ActiveWindow.ScrollColumn = 4
> ActiveWindow.ScrollColumn = 5
> ActiveWindow.ScrollColumn = 6
> ActiveWindow.ScrollColumn = 7
> ActiveWindow.ScrollColumn = 6
> ActiveWindow.ScrollColumn = 5
> ActiveWindow.ScrollColumn = 4
> ActiveWindow.ScrollColumn = 3
> ActiveWindow.ScrollColumn = 2
> ActiveWindow.ScrollColumn = 1
> ActiveWindow.SmallScroll Down:=27
> Range("A51").Select
> End Sub
>