I have a consolidation file which shows a summary of 70+ Project Files.
In this file we have a "Current" sheet which has Live Links to the source files and historical sheets for prior periods with hard coded values.
I am trying to automate the creation of these periodic sheets. The obvious way to do this would be to copy the sheet, select all and copy paste values. However this overwrites any summation within the sheet as well as the File Links which I don't want to do.
The way I thought of getting round this was to copy the sheet to a new workbook, break the links and then copy back to the original file. In addition I need to check that the sheet name that the user chooses doesn't already exist and need the option to let the user bail out by clicking Cancel.
I have come up with the following script to do this but it isn't working.
1) Entering a name that already exists gives the error message but entering a new name just goes into a loop and clicking Cancel doesn't do anything.
2) If I comment out all the sheet name checks and let it get through to the BreakLinks section I get an error message and the "arrLinks(I)" variable shows as blank. Is this because I haven't saved the new workbook?? I don't need to save it as it is only a temporary holding file. If it needs to be saved then its OK for it to go in the same directory as the source file but I will need an extra section for getting rid of the saved file so it doesn't cause issues at a later date. I adapted this section from some code that RGonzo supplied for changing multiple links so I might have done it incorrectly.
SourceFile = ActiveWorkbook.Name
ShtCount = ActiveWorkbook.Sheets.Count
NewPeriod = InputBox("Enter period number for copied sheet.", "Period Number", "AP#")
If NewPeriod = vbCancel Then Exit Sub
For Each Sht In ActiveWorkbook.Sheets
If Sht.Name = NewPeriod Then
NameExists = True
ErrMsg = MsgBox("That sheet exists! Choose a new name.")
If ErrMsg = vbCancel Then Exit Sub
Loop Until NameExists = False
Sheets("Current").Name = NewPeriod
NewFile = ActiveWorkbook.Name
For I = 1 To UBound(arrLinks)
MyLink = arrLinks(I)
Application.DisplayAlerts = False
ActiveWorkbook.BreakLink Name:=MyLink, Type:=xlExcelLinks
Application.DisplayAlerts = True
Any suggestions greatly appreciated on how to correct or an alternative method for achieving this.