Macro to open workbook and copy and paste values in to orig workbo

[Dena X]

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 Cata\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

 

[Rowan Drummond]

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