G
Guest
Good Morning,
I use the following code to export a specific sheet in my workbook, to a new
workbook. All works great, however, i do not want to export the buttons on
the orignal sheet. Can anyone suggest how i can modify this code to achieve
this?
Many thanks in advance,
Module Code:
Sub SendToDesktop(WB As Workbook)
Dim oWSH As Object
Dim oShortcut As Object
Dim myPath As String
Dim myShortcutPath As String
Dim sStr As String
With WB
myPath = .FullName
sStr = "\" & Left(.Name, Len(.Name) - 4)
End With
Set oWSH = CreateObject("WScript.Shell")
With oWSH
myShortcutPath = .SpecialFolders.Item("Desktop")
Set oShortcut = .CreateShortcut _
(myShortcutPath & sStr & ".lnk")
End With
With oShortcut
.TargetPath = myPath
.Save
End With
Set oWSH = Nothing
End Sub
*******************************************
On Click Event Code:
Private Sub cmdExport_Click()
Dim SH As Worksheet
Dim WB As Workbook
Dim WB2 As Workbook
Set WB = ThisWorkbook
WB.Sheets(Me.lstExportData.Value).Copy
Set WB2 = ActiveWorkbook
With WB2
'.SaveAs Filename:=WB2.Sheets(1).Name & ".xls"
Call SendToDesktop(WB2)
.Close
End With
End Sub
I use the following code to export a specific sheet in my workbook, to a new
workbook. All works great, however, i do not want to export the buttons on
the orignal sheet. Can anyone suggest how i can modify this code to achieve
this?
Many thanks in advance,
Module Code:
Sub SendToDesktop(WB As Workbook)
Dim oWSH As Object
Dim oShortcut As Object
Dim myPath As String
Dim myShortcutPath As String
Dim sStr As String
With WB
myPath = .FullName
sStr = "\" & Left(.Name, Len(.Name) - 4)
End With
Set oWSH = CreateObject("WScript.Shell")
With oWSH
myShortcutPath = .SpecialFolders.Item("Desktop")
Set oShortcut = .CreateShortcut _
(myShortcutPath & sStr & ".lnk")
End With
With oShortcut
.TargetPath = myPath
.Save
End With
Set oWSH = Nothing
End Sub
*******************************************
On Click Event Code:
Private Sub cmdExport_Click()
Dim SH As Worksheet
Dim WB As Workbook
Dim WB2 As Workbook
Set WB = ThisWorkbook
WB.Sheets(Me.lstExportData.Value).Copy
Set WB2 = ActiveWorkbook
With WB2
'.SaveAs Filename:=WB2.Sheets(1).Name & ".xls"
Call SendToDesktop(WB2)
.Close
End With
End Sub