Hi RBS,

Thank you very much for your patience.  Yes, I do have the Sheets(1) opened.
In fact, it is part of Workbook A.  

Below is my code.  Please note the names in my code are different from the
names that I posted.  In my code, FName1 and FName2 are CRYear and FName,
respectively.  What I need help with is located right below "Dim" statements.

If you have any suggestions or recommendation on my code, please feel free
to share with me.  Thanks.

Sub CreateCSV()
Dim CSVDir As String                'Directory where the CSV files are saved
   Dim CSVFName As String              'Original Name of CSV file
   Dim CSVAFName As String             'Additional Name for CSV File, if
one exists
   Dim CRYear As Integer               'Year
   Dim FName As String        ‘Portion of CSV File Name
   Dim InitName As String              'Placeholder to create additional
CSV files
   Dim i As Integer                    'Use to create additional CSV files
   Dim j As Integer                    'Use to create CSV sheet
   Dim myFile As String                'Use to test for file existence to
create additional CSV files
   With Sheets(“Sheets3”)
       CRYear = Year(.Cells(2))
   End With
   With Sheets(“Sheets2”)
        FName=.cells(2)
    End With
   CSVFName = FName & CRYear & ".csv"
   CSVDir = "C:\"
   
   ActiveWorkbook.Save                 'Save the input file before creating
a CSV file.
   
   'CREATE A CSV SHEET
   Set NewSheet = Worksheets.Add       'Create a new worksheet for CSV
   NewSheet.Name = "CSV"
   j = 1
   'Create a CSV sheet
   For Each nName In ActiveWorkbook.Names
       NewSheet.Cells(j, 1).Value = "'" & Right(Left(nName.Name, 7), 6)
       'Data
       NewSheet.Cells(j, 3).Value = nName.RefersTo
       If Right(Left(nName.Name, 7), 2) <> 0 And Right(Left(nName.Name, 5),
2) <> 0 Then
           NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2)) & "-" & Val(Left(Right(nName.Name, 16),
2))
       ElseIf Right(Left(nName.Name, 7), 2) = 0 And Right(Left(nName.Name,
5), 2) <> 0 Then
           NewSheet.Cells(j, 6).Value = Left(nName.Name, 1) & "-" &
Val(Left(Right(nName.Name, 18), 2))
       ElseIf Right(Left(nName.Name, 7), 4) = 0 Then
           NewSheet.Cells(j, 6).Value = Left(nName.Name, 1)
       End If
       If Left(Right(nName.Name, 14), 2) <> 0 Then
           NewSheet.Cells(j, 7).Value = Left(Right(nName.Name, 14), 2)
       End If
       If Left(Right(nName.Name, 12), 2) <> 0 Then
           NewSheet.Cells(j, 8).Value = Left(Right(nName.Name, 12), 2)
       End If
       If Left(Right(nName.Name, 10), 4) <> 0 Then
           NewSheet.Cells(j, 9).Value =
Application.Substitute(Left(Right(nName.Name, 10), 4), "0", "")
       End If
       NewSheet.Cells(j, 10).Value = "'" & Left(Right(nName.Name, 6), 2) &
"." & Left(Right(nName.Name, 4), 2)
       NewSheet.Cells(j, 11).Value = Right(nName.Name, 2)
       j = j + 1
   Next
   NewSheet.Columns("A:K").AutoFit

   'CREATE A CSV FILE
   'Check to see the CSV file is already exist.
   'If exist, ask the user whether to overwrite the existing file.
   If Len(Dir(CSVDir & CSVFName)) > 0 Then
       PROMPT1 = MsgBox(Prompt:="There is a CSV file " & "'" & CSVFName &
"'" & _
                       " already created for this cost report." & _
                       "  Would you like to overwrite the existing CSV
file?", _
                       Buttons:=vbYesNo + vbQuestion, Title:="CSV Macro")
           'If 'Yes', overwrite it.
           If PROMPT1 = vbYes Then
               Sheets("CSV").Select
               Sheets("CSV").Move
               Application.DisplayAlerts = False
               ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , ,
True, _
                   False, xlNoChange
               Application.DisplayAlerts = True
               ActiveWindow.Close False
           'If 'No', ask the user whether to create a new file with a new
name.
           ElseIf PROMPT1 = vbNo Then
               PROMPT2 = MsgBox(Prompt:="Would you like to create an
addition CSV file?", _
                               Buttons:=vbYesNo + vbQuestion, Title:="CSV
Macro")
                   'If 'Yes', additional CSV files are created automatically.
                   If PROMPT2 = vbYes Then
                       InitName = CSVDir & CSVFName
                       CSVAFName = InitName
                       Do
                           myFile = Dir(CSVAFName)
                           If myFile <> "" Then
                               i = i + 1
                               CSVAFName = Left(InitName, Len(InitName) -
4) & i & ".csv"
                           End If
                           
                           If i > 2 Then
                               MsgBox "Sorry! There are already " & i & "
files created " & _
                                       "in the directory C:\'.  " & Chr(13)
& "No " & _
                                       "additional file is created.",
vbInformation, _
                                       "CSV Macro"
                               Application.DisplayAlerts = False
                               Sheets("CSV").Delete
                               Application.DisplayAlerts = True
                               Workbooks("MACRO to Create CSV.xls").Close
False
                                   End
                           End If
                       Loop While myFile <> ""
                       Sheets("CSV").Select
                       Sheets("CSV").Move
                       ActiveWorkbook.SaveAs CSVAFName, xlCSVWindows, , , _
                       True, False, xlNoChange
                       ActiveWindow.Close False
                       MsgBox "An additional CSV file '" & Right(CSVAFName,
Len(CSVAFName) - Len(CSVDir)) & _
                       "' has created in " & _
                       "the directory 'C:\'.", _
                       vbInformation, "CSV Macro"

                   'If 'No', delete the CSV sheet that created in the input
file.
                   Else
                       Application.DisplayAlerts = False
                       Sheets("CSV").Delete
                       Application.DisplayAlerts = True
                       MsgBox "No additional CSV file is created.",
vbInformation, _
                               "CSV Macro"
                   End If
           End If
   'If the CSV file does not exist, create one.
   Else
       Sheets("CSV").Select
       Sheets("CSV").Move
       ActiveWorkbook.SaveAs CSVDir & CSVFName, xlCSVWindows, , , , False,
xlNoChange
       ActiveWindow.Close False
       MsgBox "A CSV file '" & CSVFName & "' has created in the directory "
& _
               "'C:\'.", vbInformation, _
               "CSV Macro"
   End If
   
   'Close the Macro file after saving the CSV file
   Workbooks("MACRO to Create CSV.xls").Close False
End Sub