Copy Contents

[thomas.szwed]

Hi,

I have the following code which creates a new workbook containing a copy of a paticular sheet from my other workbook. Only trouble is the copied sheet has lots of links in it and whenever opended asks whether to update. Is there anyway to modify the copy worksheet code below so it copies the contents (like a special 'Paste Values')????

Dim sSheetName As String

On Error Resume Next
sSheetName = Application.VLookup(Range("K10").Value, Range("F117:G125"), 2, False)
On Error Goto 0
Worksheets(sSheetName).Copy
ActiveWorkbook.SaveAs Filename

Many Thanks

[dominicb]

Good afternoon thomas.szwed

Is this code of any use to you? It will loop through each linked file (as seen in the Edit > Links dialog) in the workbook and turn the link to a fixed number. Just call it from your current routine :

Sub test()
Application.DisplayAlerts = False
Links = ActiveWorkbook.LinkSources(xlExcelLinks)
For f = 1 To UBound(Links)
ActiveWorkbook.BreakLink Name:=Links(f), Type:=xlExcelLinks
Next f
Application.DisplayAlerts = True
End Sub

HTH

DominicB

[thomas.szwed]

but i have a feeling if you call this sub from the module of the original workbook it will apply these settings to both workbooks. that wouldnt be want i wanted.....?

[thomas.szwed]

If i did use it where would i call it?

Sub EmailLineManager()
Dim sSheetName As String
Dim Filename As Variant


On Error Resume Next
sSheetName = Application.VLookup(Range("K10").Value, Range("F117:G125"), 2, False)
On Error GoTo 0

If sSheetName <> "" Then

Filename = Range("E6").Value
Filename = Mid(Filename, InStr(Filename, " ") + 1) & " " & _
Left(Filename, InStr(Filename, " ") - 1) & _
", CONTRACT, " & Format(Range("K8").Value, "dd.mm.yy") & ".xls"
Filename = Application.GetSaveAsFilename(Filename, "Microsoft Excel Files (*.xls), *.xls")
If Filename <> False Then

Worksheets(sSheetName).Copy
ActiveWorkbook.SaveAs Filename
End If
Else
MsgBox "You have not selected a Contract Type", vbOKOnly + vbInformation, "Information"
End If



End Sub

[dominicb]

Hi thomas.szwed

If you want to remove the links from the newly created file, then right at the end of the macro - as it stands at the moment, at the time this line runs :
ActiveWorkbook.SaveAs Filename
the new workbook then becomes the currently active workbook and the "old" workbook is removed from memory.

HTH

DominicB