I'm trying to get the data from current excel file to another excel file using VBA but not working : I have a requirement where im doing copying and pasting data from current excel file to destination excel file.

  Below is the sample code;

Sub ImportCSV()
 
    Dim strSourcePath As String
    Dim strDestPath As String
    Dim strFile As String
    Dim strData As String
    Dim x As Variant
    Dim Cnt As Long
    Dim r As Long
    Dim c As Long
 
    Application.ScreenUpdating = False
 
    'Change the path to the source folder accordingly
    'strSourcePath = "C:\Path\"
    strSourcePath = Application.ActiveWorkbook.Path
    
    If Right(strSourcePath, 1) <> "\" Then strSourcePath = strSourcePath & "\"
 
    'Change the path to the destination folder accordingly
    strDestPath = Application.ActiveWorkbook.Path
 
    If Right(strDestPath, 1) <> "\" Then strDestPath = strDestPath & "\"
 
    strFile = Dir(strSourcePath & "*.csv")
    
    
    
    Set newbook = Workbooks.Add
    
    With newbook
    
        .SaveAs Filename:=strDestPath + "ERNTable.xlsx"
        
        .Close
    End With
 
    Do While Len(strFile) > 0
        Cnt = Cnt + 1
        If Cnt = 1 Then
           r = 1
       Else
           r = Cells(Rows.Count, "A").End(xlUp).Row + 1
       End If
        Open strSourcePath & strFile For Input As #1
            If Cnt > 1 Then
               Line Input #1, strData
           End If
            Do Until EOF(1)
                Line Input #1, strData
                x = Split(strData, ",")
                For c = 0 To UBound(x)
                    Cells(r, c + 1).Value = Trim(x(c))
                Next c
                r = r + 1
            Loop
        Close #1
        Name strSourcePath & strFile As strDestPath & strFile
        strFile = Dir
    Loop
 Set newBook = workbooks.add
with newBook
   .saveas filename:= "Finalfile.xlsx"
End with


ActiveSheet.Range("B$1:c$" &r).copy workbooks("Finalfile.xlsx").Worksheets("Sheet1").Range(("B$1:c$" &r)
Workbooks("Finalfile.xlsx").Save
    Application.ScreenUpdating = True
 
    If Cnt = 0 Then _
        MsgBox "No CSV files were found...", vbExclamation
'    End If
End Sub