Hi,

I am trying to get a workbook that is opened from this macro to automatically update its links when opened, so that the user does not have to worry about clicking on anything. I have used the example in the help file but no joy!

I have also noticed that the range is not pasting and causing an error when i try to paste it into the original workbook, it works when pasting onto the same workbook though.

Any help greatly appriciated.

Thanks,
David


Sub CreateStaffList()
' CreateStaffList
' Macro written by David Gard, 11/09/08
' Copies data from 'Staff List' into another file and adds headings.

FilePath = "P:\Employee Change Updates\"
FileName = "Website update details.xls"
Set wS1 = Worksheets("Staff List")
Set wB1 = Application.Workbooks(1)

    
' Open source workbook, copy data and close
    Sheets("Staff List").Select
    Workbooks.Open (FilePath & FileName)
    Select Case wB1.UpdateLinks
        Case xlUpdateLinksAlways
    End Select
    Sheets("Staff List").Select
    Range("A1:E100").Select
    Selection.Copy
    Workbooks("Staff List.xls").Activate
    Range("A3").Select
    Selection.Paste
    Workbooks(FileName).Close SaveChanges:=False
    Workbooks("Staff List.xls").Activate
' Create Sheet Title
    Range("AF1").Select
    TodaysDate = ActiveCell.Value
    Title = "List of Partners and Staff as at "
    Range("A1:E2").Select
    Selection.MergeCells = True
    ActiveCell.Value = Title & TodaysDate
' Creates all boarders
    CreateBoarders_Main
' Creates Heading Titles
    OfficeName = "Sherborne"
    OfficeLocation = "AA1"
    SearchOfficeName
    OfficeName = "Shepton Mallet"
    OfficeLocation = "AB1"
    SearchOfficeName
    OfficeName = "Castle Cary"
    OfficeLocation = "AC1"
    SearchOfficeName
    OfficeName = "Wincanton"
    OfficeLocation = "AD1"
    SearchOfficeName
    Range("A3").Select

End Sub