Hi Trish
See
http://www.rondebruin.nl/copy4.htm
You can change the red code block to code that make your changes
--
Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm
"Trish Smith" <(E-Mail Removed)> wrote in message news:06DA35ED-F219-4C73-A8CD-(E-Mail Removed)...
> Hi everyone,
>
> I'm hoping someone can help me out.
>
> I've got about 40 workbooks each with between 7 and 11 sheets.
>
> The workbooks originally just held data and new (analysis) sheets were added
> from a template.
>
> The analysis sheets have lookup formulas relating back to the original
> template.
>
> If it is possible I would be grateful for someone to help me with code that
> can go through each of the workbooks (all in the same folder) and change the
> link from the template link to the data in the same workbook.
>
> I've found this bit of code in this forum and it does what I want but it's
> just as easy to go into the menus and do it that way.
>
> Thanks
> Trish
>
> Sub LinksChangeSourcewitherrortrap()
>
> Dim stroldlink As String
> Dim strnewlink As String
> MsgBox "Select Original link Sourcexxx"
>
> stroldlink = Application.GetOpenFilename _
> ("Excel files,*.xls")
> MsgBox "Select workbook to change"
>
> strnewlink = Application.GetOpenFilename _
> ("Excel files,*.xls")
>
> MsgBox ActiveWorkbook.Name
>
> On Error GoTo errorLinks 'No colon after label name here
> ActiveWorkbook.ChangeLink Name:=stroldlink, _
> newname:=strnewlink, _
> Type:=xlLinkTypeExcelLinks ' This one works at work
>
> On Error GoTo 0 'Cancels the error call.
>
> Exit Sub 'Place this immediately before errorLinks
> 'label so does not process unless there is an error.
>
> errorLinks: 'Note the colon after the label name
>
> MsgBox "An error has occurred. " & _
> "This is possibly due to one of the following:-" _
> & Chr(13) & "Incorrect Workbook name selected and/or" _
> & Chr(13) & "Workbooks do not have same Worksheet names." _
> & Chr(13) & "Check both the above and then re-run the macro."
>
> End Sub
>
> --
> Trish
Similar Threads
