PDA

View Full Version : Solved: VBA saving macro



chaosier
08-27-2008, 08:24 PM
Hi!! I've made a questionnaire and compiled the results on a separate sheet... However I would like to create a macro which will do this

1. Save the file name from a cell value.
2. Save it in the desktop as tab form.

Bob Phillips
08-28-2008, 01:14 AM
Dim oWSH As Object
Dim oShortcut As Object
Dim sPathDeskTop As String

Set oWSH = CreateObject("WScript.Shell")
sPathDeskTop = oWSH.SpecialFolders("Desktop")

ActiveWorkbook.SaveAs ActiveSheet.Range("H1").Value
Set oShortcut = oWSH.CreateShortCut(sPathDeskTop & "\" & _
ActiveWorkbook.Name & ".lnk")
With oShortcut
.TargetPath = ActiveWorkbook.FullName
.Save
End With
Set oWSH = Nothing