Sleeper: How do I break a link after saving to new workbook

[GDB]

Hi

I was given this macro and ive amended but just trying to finish off.


It saves the active workbook as a copy called summary1.xls in a new subfolder created. What im trying to do is break the links in the new summary1.xls after it saved to new location.


Can this be done?

Sub PERIODEND()
Dim sName As Variant, bFolder As String, dest As String
sName = InputBox("Subfolder name")
If sName = "" Then Exit Sub
bFolder = "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\backup"
If Dir(bFolder & "" & sName, vbDirectory) = "" Then
    MkDir bFolder & "" & sName
End If
dest = bFolder & "" & sName & ""
ActiveWorkbook.SaveCopyAs dest & "NEW SUMMARY1.xls"

[大灰狼1976]

Hi GDB!
What does "break the links" mean?
The following code just corrects some errors.

Sub PERIODEND()
Dim sName As Variant, bFolder As String, dest As String
sName = InputBox("Subfolder name")
If sName = "" Then Exit Sub
bFolder = "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\backup"
If Dir(bFolder & "\" & sName, vbDirectory) = "" Then
    MkDir bFolder & "\" & sName
End If
dest = bFolder & "\" & sName & "\"
ActiveWorkbook.SaveCopyAs dest & "NEW SUMMARY1.xls"

[GDB]

Im a bit confused. You say it corrects errors.

The macro is in the workbook called summary.

When I run it asks me for a sub folder name and then creates one and copies the workbook called summary I have open into the new sub folder and saves as summary 1.

When I say break links what I meant was I have other workbooks linked to summary via copy and paste special. Thing is when it saves as summary 1 the links are still there to the other workbooks and that's what im trying to get rid of in summary 1.

The other issue I have also is that although the workbook I want to break links on will always be called summary 1 they will be in different folders as creating a folder every month.

[大灰狼1976]

Is links a Hyperlink or a Formula? This is what I want to ask.

[GDB]

Im not too sure but think its a formula between the workbook called summary and the files it connected to.

Ive done it via copy - paste special- paste link if that helps.

Sorry if it vague

[大灰狼1976]

Not sure.

Sub PERIODEND()
Dim sName As Variant, bFolder As String, dest As String, sh As Worksheet
sName = InputBox("Subfolder name")
If sName = "" Then Exit Sub
bFolder = "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\backup"
If Dir(bFolder & "\" & sName, vbDirectory) = "" Then
   MkDir bFolder & "\" & sName
End If
dest = bFolder & "\" & sName & "\"
ActiveWorkbook.SaveCopyAs dest & "NEW SUMMARY1.xls"
Workbooks.Open dest & "NEW SUMMARY1.xls"
For Each sh In ActiveWorkbook.Sheets
    sh.UsedRange = sh.UsedRange.Value
Next
ActiveWorkbook.Close True

[GDB]

99% there...

Macro mostly does what I want but just comes up with run time error 13 at the place where i have put the script in bold.

Sub PERIODEND()
Dim sName As Variant, bFolder As String, dest As String, sh As Worksheet
sName = InputBox("Subfolder name")
If sName = "" Then Exit Sub
bFolder = "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\backup"
If Dir(bFolder & "\" & sName, vbDirectory) = "" Then
    MkDir bFolder & "\" & sName
End If
dest = bFolder & "\" & sName & "\"
ActiveWorkbook.SaveCopyAs dest & "NEW SUMMARY1.xls"
Workbooks.Open dest & "NEW SUMMARY1.xls"
For Each sh In ActiveWorkbook.Sheets
    sh.UsedRange = sh.UsedRange.Value
Next
ActiveWorkbook.Close True

[/QUOTE]

[大灰狼1976]

Not sure, Better give me an attachment.

Sub PERIODEND()
Dim sName As Variant, bFolder As String, dest As String, sh As Worksheet
sName = InputBox("Subfolder name")
If sName = "" Then Exit Sub
bFolder = "T:\Passenger Accounts\SHARED\passacc\Excel\passacc\backup"
If Dir(bFolder & "\" & sName, vbDirectory) = "" Then
    MkDir bFolder & "\" & sName
End If
dest = bFolder & "\" & sName & "\"
ActiveWorkbook.SaveCopyAs dest & "NEW SUMMARY1.xls"
Workbooks.Open dest & "NEW SUMMARY1.xls"
For Each sh In ActiveWorkbook.Worksheets
sh.UsedRange = sh.UsedRange.Value
Next
ActiveWorkbook.Close True