Merge and update ???

sdc,

If the reports are in a fairly strict template format this is quite easily done with VBA.

Try a sub something like the sub below as a starting point, put this in your MAIN workbook. It copys the data from Sheet1!E9 in an external workbook.

Teppic_

--------

Sub Import_Data()
    Dim NewFN       As String
    Dim ControlFile As String
    Dim Cell_Ref    As String
   
    ControlFile = ActiveWorkbook.Name

    NewFN = Application.GetOpenFilename(FileFilter:="Excel File (*.xls), *.xls", Title:="Please select a file")
    If NewFN = "False" Then
        MsgBox "No Input File Selected"                     ' They pressed Cancel
        Exit Sub
    Else
        Workbooks.Open Filename:=NewFN
    End If
   
    ' this is  a bit crude for getting the workbook name...
    NewFN = Left(NewFN, InStrRev(NewFN, ".") - 1)       ' this may not work if the filename has more than on '.' char in it
    NewFN = Right(NewFN, Len(NewFN) - InStrRev(NewFN, "\"))
   
    Range("'[" + ControlFile + "]Sheet1'!E9").Clear
   
    Cell_Ref = "'[" + NewFN + "]Sheet1'!E9" ' copy the data
    Range(Cell_Ref).Copy
    Range("'[" + ControlFile + "]Sheet1'!E9").PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
   
    Workbooks(NewFN & ".xls").Close   ' if file is not an xls this may want to be [xlsx, xlsm] etc
End Sub

2 ?'s... do I need to rename my reports and also what is ment by strict formatting

thx

Sdc,

No that macro would allow you to choose any input workbook - it will ask you which workbook when you run it.

By strict formatting I mean it is going to make your life much easier if you know where to look for the data.

It is possible to create a template so that the user will always put there content is specfic cell ranges, and on sheets whose name you know. If the people who send you reports are able to rename sheets and/or insert rows/columns then you might have a nightmare in getting a macro to find the data you want to copy across.

Teppic_

have another ?

Hello I have this code and now I want to work it for each of my employees 4-2000, can anyone help here is the code

Sub MoveDate()

Select Case Range("AI3").Value
Case Is = ""
Range("Z3:AA3").Select
Selection.Cut
Range("AI3").Select
ActiveSheet.Paste

Case Else

Range("AI3").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Range("AK3").Select
ActiveSheet.PasteSpecial Format:=12, Link:=1, DisplayAsIcon:=False, _
IconFileName:=False

Range("Z3:AA3").Select
Selection.Cut
Range("AI3").Select
ActiveSheet.Paste
End Select
End Sub

1: tryin to make this script autorun as soon as I open the worksheet?

(i have tried changing the code to Private Sub Workbook_Open() but still will not work )

2. have a list of employees 4-2000 I need this code to work for each employee

any help would be appreciated
thx

Not a direct answer to your question, but Access 2007 and 2010 have an email data collection feature that is designed to do just this sort of thing, so you might want to try that. You can link to Excel worksheets from Access, so that might be an option for getting the data into the Access environment.