View original (French)

VBA: save a sheet and remove links

Solved
Maud1681 Posted messages 14 Registration date   Status Member Last intervention   -  
Maud1681 Posted messages 14 Registration date   Status Member Last intervention   -
Hello,
I am using the code below to copy a sheet from an Excel document and save it:
1) you click the button
2) the sheet is copied/saved under a name and the macro button disappears
3) the source document closes without saving
The source document has cells whose content is linked to another sheet in the workbook.

Problem: the copied sheet keeps the links that I would like to remove... do you know how to modify my code?

Private Sub CommandButton4_Click()
'Macro: when the file is completed, we click on button 4, the sheet is copied and the original file is deleted and closed.
Dim nomfichier As String
Dim FileExtStr As String
Dim FileFormatNum As Long
ThisWorkbook.ActiveSheet.Copy
ActiveSheet.Shapes("CommandButton4").Visible = False
FileExtStr = ".xlsm": FileFormatNum = 52
ActiveSheet.SaveAs Filename:= _
"\\xxxxx\xxxx\xxx\xx\test\" & "test" & ".xls"
ThisWorkbook.Close SaveChanges:=False
End Sub

Thank you in advance

1 answer

Answer 1 / 1
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
 
Hello,

By going through an intermediate sheet, not a workbook (
ThisWorkbook.ActiveSheet.Copy
), it won't generate anything since you close without saving. 

Private Sub CommandButton4_Click() 'Macro: when the file is completed, we click on button 4, the sheet is copied and the original file is deleted and closed. Dim nomfichier As String Dim FileExtStr As String Dim FileFormatNum As Long With ActiveSheet .Shapes("CommandButton4").Visible = False .Cells.Copy End With Sheets.Add After:=ActiveSheet Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False ActiveSheet.SaveAs Filename:="\\xxxxx\xxxx\xxx\xx\test\" & "test" & ".xls", FileFormat:=56 ThisWorkbook.Close SaveChanges:=False End Sub
0
Maud1681 Posted messages 14 Registration date   Status Member Last intervention  
 
Hello,
I applied your method but it copies the sheet while removing the links, and I would like to keep the formatting and macro buttons that should be active for the next user...
0
f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717 > Maud1681 Posted messages 14 Registration date   Status Member Last intervention  
 
Re,

Okay, I'll take a look

continuation:

Maybe the name of the copied sheet to check 

Private Sub CommandButton4_Click() 'Macro: when the file is completed, we click on button 4, the sheet is copied and the original file is erased and closed. Dim nomfichier As String Dim FileExtStr As String Dim FileFormatNum As Long With ActiveSheet .Shapes("CommandButton4").Visible = False .Copy After:=ActiveSheet End With With ActiveSheet nom = .Name .Select .UsedRange.Copy .Range("A1").Select End With Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False 'ActiveWorkbook.SaveAs Filename:="d:\_acsv\" & "test_trial_link" & ".xls", FileFormat:=56 ActiveSheet.SaveAs Filename:="\\xxxxx\xxxx\xxx\xx\test\" & "test" & ".xls", FileFormat:=56 Sheets(nom).Copy ThisWorkbook.Close SaveChanges:=False End Sub
0
Maud1681 Posted messages 14 Registration date   Status Member Last intervention  
 
I'll check and let you know... thanks.
0
Maud1681 Posted messages 14 Registration date   Status Member Last intervention  
 
I finally went for it like a barbarian by doing a special copy/paste of the cells that had links:

Range("A35:C35").Select
Selection.Copy
Range("A35").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Thank you very much for your help.
0