Export/Save a worksheet in a workbook without changing the name of the workbook?

[111StepsAhead]

I've borrowed code from

http://www.excelforum.com/excel-prog...-location.html

and

http://www.ozgrid.com/forum/showthread.php?t=150943

to accomplish this..

Sub EXPORT_CSV()
    Dim fPath As String
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .AllowMultiSelect = False
        .Show
        If .SelectedItems.Count > 0 Then
            fPath = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
    
    fname = fPath & worksheets("Sheet1").Range("Z1").Value
    
    Worksheets(worksheets("Sheet1").Range("Z1").Value).SaveAs fname, xlCSV
End Sub

which this code allows the user to select a location and then save a worksheet off as a CSV. The names of all possible worksheets are stored on sheet 1 range column Z. My only issue is saving the file changes the physical name of the workbook I am exporting from. How would I prevent this name change from taking place but still be able to export worksheets as CSVs to user chosen locations?

Thanks in advance for any help with this issue.

[111StepsAhead]

bumping this thread

[JosephP]

copy the sheet to create a new workbook and then save the new active workbook as csv