title: Macro for exporting text files from Excel file with multiple worksheets
body: <p>I need a macro that will export a worksheet in an Excel file so that they are comma separated text files that look like this:</p>
  
  <p>field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field, field</p>
  
  <p>I have the following macro that runs on a file BUT need it to do the following:</p>
  
  <p>1) It should run on the active open worksheet in an Excel file with multiple worksheets.
  2) Should prompt the user to save the new text file with a unique name.
  3) Places the text file either on the desktop or in a designated folder.</p>
  
  <p>Here's the macro:</p>
  
  <pre><code>Sub WriteCSVFile()
  
  Dim ws As Worksheet
  Dim fName As String, Txt1 As String
  Dim fRow As Long, lRow As Long, Rw As Long
  Dim Col As Long
  
  Set ws = Sheets("Sheet1")
  fName = "C:\yourpath\yourfilename.csv"
  fRow = 2
  Col = 2
  Txt1 = ""
  
      With ws
          lRow = .Cells(Rows.Count, Col).End(xlUp).Row
  
          Open fName For Output As #1
  
              For Rw = fRow To lRow
                  Txt1 = .Range(.Cells(Rw, Col), .Cells(Rw, Col))
                      If Rw = lRow Then
                          Print #1, Txt1
                      Else
                          Print #1, Txt1 &amp; ", ";
                      End If
              Next Rw
  
          Close #1
  
          MsgBox ".csv file exported"
  
      End With
  End Sub
  </code></pre>
  
  <p>The problem with the above is that I have to modify the macro for each worksheet. I would like something that can run without modification on any open worksheet.</p>