VBA code not working

[Exceliscool123456789]

Im trying to write a code that will copy all the information from columns A to J from one workbook (daily occurrence) and paste it into another workbook at the end of the last row of data pasted from column A (essentially a rolling data tally).

Also, if "Sheet 1" (worksheet name) always changes daily when I download the report, how can I label it in the code accordingly?


Sheet 1 = the downloaded sheet from 3rd site


Bank rec = is the rolling data tally workbook





Dim m As Long
Dim r As Long
m = Worksheets("Sheet1").Range("A:J" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
r = Worksheets("Bank rec").Range("A" & Worksheets("Bank rec").Rows.Count).End(xlUp).Row + 1


Worksheets("Sheet1").Range("A1:J" & m).Copy Destination:=Worksheets("Bank rec").Range("A" & r)
Application.CutCopyMode = False

 

[Exceliscool123456789]

What needs to be inputted in the "File Filter" section?

FileFilter:="CSV FIles (*.csv),*.CSV"

FileFilter:="Excel FIles (*.xls*),*.xls*"

 

[Joe4]

Thank you for your assistance and efforts, is there a way we can just have the "downloaded" report be open and start the code from there instead of retrieving that file?

This change will work if the downloaded CSV file is already open in the same Excel session:

Sub MyCopyRows()

    Dim file1 As String
    Dim file2 As String
    Dim wb As Workbook
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim lr1 As Long
    Dim lr2 As Long
   
'   Loop through all open workbooks to identify Bank CSV file
    For Each wb In Application.Workbooks
        If Left(wb.Name, 4) = "Bank" And Right(wb.Name, 3) = "csv" Then
            Set wb1 = wb
        End If
    Next wb
   
'   Find last row in column A with data
    lr1 = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Browse to open Bank Transactions File
    file2 = Application.GetOpenFilename(FileFilter:="Excel FIles (*.xls*),*.xls*", Title:="Select monthly Bank Transactions file to be opened")
    Set wb2 = Workbooks.Open(file2)
   
'   Find last row in column A with data
    lr2 = Cells(Rows.Count, "A").End(xlUp).Row
   
'   Copy data from file 1 to file 2
    wb1.Activate
    Range("A1:J" & lr1).Copy
    wb2.Activate
    Sheets("BMO All Transactions").Activate
    Range("A" & lr2 + 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
   
'   Close original CSV file
    wb1.Close
   
'   Save update Excel file
    wb2.Save
      
End Sub

What needs to be inputted in the "File Filter" section?

FileFilter:="CSV FIles (*.csv),*.CSV"

FileFilter:="Excel FIles (*.xls*),*.xls*"

I am not sure what you mean. You should not have to change anything there. Just use it "as-is".

 

[Exceliscool123456789]

This change will work if the downloaded CSV file is already open in the same Excel session:

Sub MyCopyRows()

    Dim file1 As String
    Dim file2 As String
    Dim wb As Workbook
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim lr1 As Long
    Dim lr2 As Long
  
'   Loop through all open workbooks to identify Bank CSV file
    For Each wb In Application.Workbooks
        If Left(wb.Name, 4) = "Bank" And Right(wb.Name, 3) = "csv" Then
            Set wb1 = wb
        End If
    Next wb
  
'   Find last row in column A with data
    lr1 = Cells(Rows.Count, "A").End(xlUp).Row
  
'   Browse to open Bank Transactions File
    file2 = Application.GetOpenFilename(FileFilter:="Excel FIles (*.xls*),*.xls*", Title:="Select monthly Bank Transactions file to be opened")
    Set wb2 = Workbooks.Open(file2)
  
'   Find last row in column A with data
    lr2 = Cells(Rows.Count, "A").End(xlUp).Row
  
'   Copy data from file 1 to file 2
    wb1.Activate
    Range("A1:J" & lr1).Copy
    wb2.Activate
    Sheets("BMO All Transactions").Activate
    Range("A" & lr2 + 1).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
  
'   Close original CSV file
    wb1.Close
  
'   Save update Excel file
    wb2.Save
     
End Sub

I am not sure what you mean. You should not have to change anything there. Just use it "as-is".

Never mind, it was highlighted yellow and thought something must have been inputted there.

 

[Joe4]

So, does the updated code in my previous post give you what you need then?

 

[Exceliscool123456789]

Omg sorry for the late response, it does work, thank you for your assistance.