Extract workbook select in Col A after last item

[howard]

I have written the following code to select files to be copied


once I select a file it appears in the message box


I would also like the file name selected to be copied into Col A from row 2 onwards on sheet "Macro"

or e.g. if BR1 sales May 2019.csv is selected first, BR2 sales May 2019.csv is then selected, BR1 sales May 2019.csv must be copied to A2, BR2 sales May 2019.csv must be copied to A3 etc

 Sub Open_Workbook()


A:
Dim A     As Variant
    
    ChDir "C:\download"
    
    If msg <> "" Then MsgBox msg

    A = Application.GetOpenFilename
    If A = False Or IsEmpty(A) Then Exit Sub
    
    Application.ScreenUpdating = False
      With Workbooks.Open(A)
         msg = msg & vbCr & A
        .Sheets(1).UsedRange.Copy Destination:=ThisWorkbook.Sheets("Imported Data").Range("A" & ThisWorkbook.Sheets(1).Rows.Count).End(xlUp).Offset(1)
        .Close False
    End With
   

   answer = MsgBox("Does another file needs to be selected?", vbYesNo + vbQuestion, "Hello")
   
If answer = vbYes Then
GoTo A:

End If
 Application.ScreenUpdating = True
End Sub

I would be appreciated if someone could kindly amend my code accordingly

 

[mumps]

Try:

Sub Open_Workbook()
A:
    Dim A As Variant, desWS1 As Worksheet, desWS2 As Worksheet
    Set desWS1 = ThisWorkbook.Sheets("Imported Data")
    Set desWS2 = Sheets("Macro")
    ChDir "C:\download"
    If msg <> "" Then MsgBox msg
    A = Application.GetOpenFilename
    If A = False Or IsEmpty(A) Then Exit Sub
    Application.ScreenUpdating = False
    With Workbooks.Open(A)
         msg = msg & vbCr & Trim(Right(WorksheetFunction.Substitute(A, "\", WorksheetFunction.Rept(" ", Len(A))), Len(A)))
        .Sheets(1).UsedRange.Copy desWS1.Range("A" & desWS1.Rows.Count).End(xlUp).Offset(1, 0)
        desWS2.Cells(desWS2.Rows.Count, "A").End(xlUp).Offset(1, 0) = Trim(Right(WorksheetFunction.Substitute(A, "\", WorksheetFunction.Rept(" ", Len(A))), Len(A)))
        .Close False
    End With
    answer = MsgBox("Does another file needs to be selected?", vbYesNo + vbQuestion, "Hello")
    If answer = vbYes Then
        GoTo A:
    End If
    Application.ScreenUpdating = True
End Sub

 

[howard]

Thanks for the help, code works perfectly

 

[mumps]

You are very welcome.