Export the sheet into Excel 2007 Format save in Desktop [SOLVED]

[dev.sutradhar1987]

Hi,


I have looked and searched for previous posts not getting what i want. I have a Sheet in a Workbook name "Weekly Report". I want a macro which when clicked will export the sheet with name " Weekly Report Week" Sheet("Weekly Report").Range("Q1").Value to my desktop.


The path as far as i understood is "D:UsersdsutradhDesktopTrackway Report........"

The exported sheet should be exactly the same which i have. My initial Sheet contains Macro.


Please help

 

[shrivallabha]

Does this help:

[pre]

Public Sub Tryitlikethis()
Dim strFileName As String
Dim wbDesWkBook As Workbook

Application.DisplayAlerts = False
strFileName = CreateObject("WScript.shell").SpecialFolders("Desktop") & _
Application.PathSeparator & "Weekly Report Week " & _
Sheets("Weekly Report").Range("Q1").Value

Sheets("Weekly Report").Copy
Set wbDesWkBook = ActiveWorkbook
wbDesWkBook.Sheets(1).Name = "Weekly Report Week " & Sheets("Weekly Report").Range("Q1").Value
wbDesWkBook.SaveAs strFileName, 51
Application.DisplayAlerts = True

End Sub

[/pre]

 

[dev.sutradhar1987]

Hi Shrivallabha,


Sorry i couldnot reply earlier as i was on vacation. Teh code works great. I have modified it bit to suit my needs.


Thanks A ton for your Time and Help..Cheers