0

I'm saving a workbook as a copy with date. I did it with the code suggested on this site (save as copy).

Now I'm getting an error concerning the links in my new workbook. Is there a way to set the correct links (on the new workbook) to the new workbook together with the save as copy method?

Sub Button15_Click()  
ActiveWorkbook.Save

'https://stackoverflow.com/questions/18899824/how-to-use-vba-saveas-without-closing-calling-workbook
'SaveAsCopy

Dim thisWb As Workbook, wbTemp As Workbook
Dim ws As Worksheet

On Error GoTo Whoa

Application.DisplayAlerts = False

Set thisWb = ThisWorkbook
Set wbTemp = Workbooks.Add

On Error Resume Next
For Each ws In wbTemp.Worksheets
    ws.Delete
Next
On Error GoTo 0

For Each ws In thisWb.Sheets
    ws.Copy After:=wbTemp.Sheets(1)
Next

wbTemp.Sheets(1).Delete
'wbTemp.SaveAs "C:\Users\Me\Desktop\FileName & Format(CStr(Now), dd-mm-yy-hhumm).xlsm", 52  'Save as timestamp
 wbTemp.SaveAs ("C:\Users\Me\Desktop\") & "FileName" & Format(Now, "dd-mm-yy-hhumm") & ".xlsm", _
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

LetsContinue:
Application.DisplayAlerts = True
Exit Sub
Whoa:
MsgBox Err.Description
Resume LetsContinue

End Sub
Harassed Dad
  • 4,669
  • 1
  • 10
  • 12
Adri
  • 85
  • 6
  • 1
    Please [edit] your question add the code you use and give an example which links you get an error and what your error massage is. Reading [mcve] might help to improve your question. – Pᴇʜ Apr 02 '19 at 09:08
  • 1
    The answer to these *"Is there a way to...?"* questions is usually just **"Yes"**. But, if you wanted to know what that way might be, then I would suggest starting with a `For Each` loop through your `Worksheets`, and a nested `For Each` loop through the `Hyperlinks` in each `Worksheet` – Chronocidal Apr 02 '19 at 11:48
  • You still haven't explained what you mean by *"Is there a way to set the correct links (on the new workbook)"* which links are wrong? You need to be much more specific here and give an example. – Pᴇʜ Apr 02 '19 at 14:47
  • I have an access file with data and an excel file with the data (sumif, countif) linked with the accessdata. That works fine. But now I try to create a backup of my excel file (with date) to send to my partners. I do that with the save copy as method (see above). The links (sumif, countif) in the new file should refer to the sheets in the new file because my partners do not have the original file or the access file. So I thought it should be like this: when save copy then set a new link or when save copy paste only the data, not the fomulas. – Adri Apr 03 '19 at 07:01
  • In the mean while we solved it with this code. Sub FormulesChange() With Workbooks("FileName" & Format(Now, "dd-mm-yy-hhumm") & ".xlsm") .Activate .Connections("AccessFile").Delete .Sheets("FileName").Activate End With Range("B5:D19").Select Selection.Replace What:="[AccessExcel.xlsm]DataAccess!", Replacement:= _ "DataAccess!", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _ SearchFormat:=False, ReplaceFormat:=False – Adri Apr 03 '19 at 09:26

0 Answers0