title: Macro to save active Sheet as new workbook, ask user for location and remove macros from the new workbook
body: <p>I have a Workbook with three WorkSheets: Product , Customer, Journal.
  What I need is a macro assigned to a button within each one of the above Sheets.
  If the button is clicked by the user, then the active sheet should be saved as a new workbook with the following naming convention:</p>
  
  <p><code>SheetName_ContentofCellB3_DD.MM.YYYY</code></p>
  
  <p>where </p>
  
  <ul>
  <li>SheetName should be the name of the
  current active sheet </li>
  <li>ContentofCellB3
  the content of cell B3 of the active
  sheet each time </li>
  <li>DD.MM.YYYY the
  current date</li>
  </ul>
  
  <p>The following macro I wrote makes the aforementioned:</p>
  
  <pre><code>Sub MyMacro()
  Dim WS As Worksheet
  Dim MyDay As String
  Dim MyMonth As String
  Dim MyYear As String
  Dim MyPath As String
  Dim MyFileName As String
  Dim MyCellContent As Range
  
  MyDay = Day(Date)
  MyMonth = Month(Date)
  MyYear = Year(Date)
  MyPath = "C:\MyDatabase"
  
  
  Set WS = ActiveSheet
  Set MyCellContent = WS.Range("B3")
  
  MyFileName = "MyData_" &amp; MyCellContent &amp; "_" &amp; MyDay &amp; "." &amp; MyMonth &amp; "." &amp; MyYear &amp; ".xls"
  WS.Copy
  Application.WindowState = xlMinimized
  ChDir MyPath
  
  If CInt(Application.Version) &lt;= 11 Then
      ActiveWorkbook.SaveAs Filename:= _
      MyFileName, _
      ReadOnlyRecommended:=True, _
      CreateBackup:=False
  Else
      ActiveWorkbook.SaveAs Filename:= _
      MyFileName, FileFormat:=xlExcel8, _
      ReadOnlyRecommended:=True, _
      CreateBackup:=False
  End If
  ActiveWorkbook.Close
  </code></pre>
  
  <p>End Sub</p>
  
  <p>However there are some issues I would like your help:</p>
  
  <ol>
  <li>How should I change the above macro so
  that the user can decide the path
  where the new workbook will be
  saved?</li>
  <li>How should I change the above macro so that the new Workbook wont include any macros that were part of the sheet of the initial workbook?</li>
  <li>Do u see anything in my macro
  that could be done another better
  way?</li>
  </ol>
  
  <p>Thanks everybody for your time in advance.</p>
  
  <p>P.S. For my case of use there must always be a backward compatibility from excel 2007 till excel 2002</p>