Unwanted link from 1 Excel file to another, how to remove. 1

[vamoose]

I am using an Excel 2000 program containing 12 sheets to create 1 Product Data Sheet. Upon closing Excel, it copies this 1 Product Data Sheet to a new workbook, saves the new workbook and closes both the new workbook and the Excel sheets it was created from. The problem is that there is now a "link" from the new workbook created to the one which created it. How do you stop the linking from occurring, can you turn it off or disable it in VBA ? These actions above are all done with VBA code.

 

[SkipVought]

Hi,

You must have formulas that you are copying/pasting.

Are you copying/pasting values or formulas?

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[vamoose]

I only want to copy the data seen in the cells, but I am getting both the formulas and data. Any ideas ?

Thanks.

 

[SkipVought]

Paste Special - VALUES

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[vamoose]

Here is the code I am using to perform the actions described above. The "links" and formulas are still getting copied and pasted into the new workbook. Any suggestions on how to prevent this from happening ? In the original sheet all cells are locked, hidden and password protected and xlNoSelection is enabled. Is there a way to copy or set the xlNoSelection property in the new sheet, I think that would work also to prevent the selection of a cell and opening up the original program. Thanks so much.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
X = Sheet1.Range("B7").Value
If X = "" Then ThisWorkbook.Saved = True: Exit Sub
ActiveWorkbook.Save
Application.DisplayAlerts = False
Sheets("Data Sheet").Select
Sheets("Data Sheet").Copy
ActiveWorkbook.SaveAs Filename:="C:\Winding\" & X & ".xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
ActiveWorkbook.Close
ThisWorkbook.Saved = True
End Sub

 

[Loomah]

Hi
Try putting this in immediately prior to your save code

With ActiveSheet.UsedRange
    .Copy
    .PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False

Happy Friday

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​