***If anyone has a more elegant solution, PLEASE post! I stitched this together from what I could learn googling the internet and in this site! ***
Well, I figured out how to do it, but I had to end up saving the workbook. In case anyone's interested, here's how I did it:
I found that if you export only the graphs and the source data and paste values on both sheets, then save, that this should break most of the links to the old workbook. In the case that there are still a *few* random links (test by going to Edit | Links and redirect your source to the newly saved workbook...if you only have a few errors to enter through, then this should work, otherwise it gets hung and crashes).
Then to redirect the final few links run through the changelinks command, but use variables for the naming convention.
Then you can move other sheets into the workbook as needed using the variable reference for the name. I found that moving all worksheets in together created too many links that needed to be resolved.
Here's the final code:
Sub aaa()
WName = ActiveWorkbook.Name
Application.ScreenUpdating = False
Sheets(Array("Trends", "Graph_Source")).Select
Sheets("Graph_Source").Activate
Sheets(Array("Trends", "Graph_Source")).Copy
Application.ScreenUpdating = False
Sheets("Graph_Source").Select
Range("HD1").Select
db_dt = ActiveCell.Value
TName = ActiveWorkbook.Name
Workbooks(TName).Activate
Sheets("Graph_Source").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D9").Select
Application.CutCopyMode = False
Sheets("Trends").Select
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("G1").Select
ActiveWorkbook.SaveAs Filename:= _
"C:\Program Files\Cognos\Impromptu 5.0\User Workspace\" & db_dt & ".xls", FileFormat _
:=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
False, CreateBackup:=False
NName = ActiveWorkbook.Name
ActiveWorkbook.ChangeLink Name:=WName, _
NewName:=NName, Type:=xlExcelLinks
ActiveWorkbook.Save
Workbooks(WName).Activate
Sheets("Main").Select
Sheets("Main").Copy Before:=Workbooks(NName).Sheets(1)
Workbooks(NName).Activate
Sheets("Main").Select
Range("A1:BZ300").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Graph_Source").Name = "GS " & db_dt
Range("A1").Select
Sheets("Trends").Name = "Trends " & db_dt
Range("A1").Select
Sheets("Main").Name = "Main " & db_dt
ActiveWorkbook.Save
Workbooks(WName).Activate
Sheets("Main").Select
Range("D13").Select
Application.ScreenUpdating = True
End Sub