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

 

[Joe4]

Welcome to the Board!

I think there is an error in this line of code:

m = Worksheets("Sheet1").Range("A:J" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row

Try changing "A:J" to "A" like you have in the line of code after this.

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

How many sheets are in the report? If it is just one, you should be able to just use:

Sheet1.

instead of

Worksheets("Sheet1").

which just selects the first sheet in that file.

 

[Exceliscool123456789]

Im getting an error message

It is relating to the highlighted line

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+n

Sheets("Sheet1").Select
Sheets("Sheet1").Copy After:=Workbooks( _
"Bank Transactions - September 2023 - TEST.xlsm").Sheets(23)
Sheets("Sheet1").Select

Dim m As Long
Dim r As Long
m = Workbook("BankRecTEST_09052023-105245.csv").Worksheets("Sheet1").Range("A" & Worksheets("Sheet1").Rows.Count).End(xlUp).Row
r = Workbook("Bank Transactions - September 2023 - TEST").Worksheets("BMO All Transactions").Range("A" & Worksheets("BMO All Transactions").Rows.Count).End(xlUp).Row + 1


Worksheets("Sheet1").Range("A1:J" & m).Copy Destination:=Worksheets("BMO All Transactions").Range("A" & r)
Application.CutCopyMode = False
End Sub

There are currently 23 sheets and more may be added

 

[Exceliscool123456789]

FYI...I also added another code to automatically include the downloaded report into the main workbook (first step)

 

[Joe4]

I don't think you can do that. I think you need to be in that file already to find where the last row is.

Let's take a step back and start with some basic details.

Do you have any part of your code working at all? If so, can you post the working part?

How exactly are these files being opened?
Is your VBA code going to opening these files, or is it to be run on files that are already opened?
Does this VBA code reside in one of these two files, or is it outside of both?

 

[Exceliscool123456789]

The first part of the code is working where it copies all the data up to the last row on the "copysheet"

Worksheets("Sheet1").Range("A1:J" & m).Copy

1). The destination file is going to be open with code and the downloaded file is already open
2). The code is going to open up the destination file (Bank reconciliation file) and have the downloaded sheet is already opened
3). I would like the code to reside in the downloaded file if can be or if not, in the destination file.

 

[Joe4]

The first part of the code is working where it copies all the data up to the last row on the "copysheet"

Worksheets("Sheet1").Range("A1:J" & m).Copy

1). The destination file is going to be open with code and the downloaded file is already open
2). The code is going to open up the destination file (Bank reconciliation file) and have the downloaded sheet is already opened
3). I would like the code to reside in the downloaded file if can be or if not, in the destination file.

In your previous post, it would seem that your "downloaded" file is "BankRecTEST_09052023-105245.csv".
VBA code cannot be stored in a CSV file, therefore the VBA code cannot exist in this file.
I am thinking you need the Excel VBA code stored in its OWN file separate of the two files you want to interact.

So the VBA code could broken into three main pieces:

1. Open "downloaded" file
Questions:
- Is there any logic to tell Excel exactly where to look and what file to open up, or do you need a file browse option to appear so you can select the file?
- Does the file follow any sort of standard naming convention?
- Assuming this is a CSV file, there can only be one sheet of data in it. Is that correct?

2. Open the "destination" file
Questions:
- Is there any logic to tell Excel exactly where to look and what file to open up, or do you need a file browse option to appear so you can select the file?
- Does the file follow any sort of standard naming convention?
- If there is more than one sheet in the file, how do we know which sheet we want to paste the data to?

3. Copy the data over from the "downloaded" file to the "desintation" file

 

[Exceliscool123456789]

1).
- The downloaded file will be from a browser
- The file does not have a standard naming convention but the downloaded sequencing is
BankRecTEST_mmddyyyy-hhmmss
i.e. for today - BankRecTest_09052023-105245
- Correct

2).
- The destination file will be already saved in the shared drive and needs to be automatically opened
- Bank Transactions - MMM YYYY (This file will be saved in the same folder but a new folder will be renamed for the next month when that reporting period opens)
- The worksheet is titled accordingly in the workbook so we know where to paste the downloaded data too.

 

[Joe4]

See if this gives you a good start. I documented it to let you know what each step is doing:

Sub MyCopyRows()

    Dim file1 As String
    Dim file2 As String
    Dim wb1 As Workbook
    Dim wb2 As Workbook
    Dim lr1 As Long
    Dim lr2 As Long
    
'   Browse to open Bank CSV file
    file1 = Application.GetOpenFilename(FileFilter:="CSV FIles (*.csv),*.CSV", Title:="Select downloaded Bank CSV file to be opened")
    Set wb1 = Workbooks.Open(file1)
    
'   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

 

[Exceliscool123456789]

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?