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
Bookmarks