Link to home
Start Free TrialLog in
Avatar of steve smith
steve smith

asked on

Visual Basic Classic

Hi All,
I have received great help to get this far with saving a copy of an xlsm sheet to my desktop. I need for all users to save to their own desktop. The sheet is part of an ordering workbook. This is what I have so far, but it just saves to my desktop.
Sub SaveToDesktop()
Dim ThisFile As String
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    ActiveSheet.Copy
    ThisFile = Range("E12").Value & "(" & Range("L12").Value & ")"
    ActiveSheet.SaveAs Filename:="C:\Users\stevesmith\Desktop\" & ThisFile, FileFormat:=52
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    ActiveWorkbook.Close
End Sub

Open in new window

Module1.bas
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

I've amended the code for you and incorporated a UDF to find the Desktop address.

Option Explicit

Sub SaveToDesktop()
    Dim ThisFile As String
    With Application
        .ScreenUpdating = False
        .DisplayAlerts = False

        ActiveSheet.Copy
        ThisFile = Range("E12").Value & "(" & Range("L12").Value & ")"
        ActiveWorkbook.SaveAs Filename:=GetDesktop & ThisFile, FileFormat:=52
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With
    ActiveWorkbook.Close True
End Sub


Public Function GetDesktop() As String
    GetDesktop = CreateObject("WScript.Shell").SpecialFolders("Desktop") & _
                 Application.PathSeparator
End Function

Open in new window

Avatar of steve smith
steve smith

ASKER

Thank You Roy_Cox,
I really appreciate your help and taking time to do so:)
Steve
ASKER CERTIFIED SOLUTION
Avatar of Roy Cox
Roy Cox
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you Roy_Cox