Need help - how to copy between workbook until the last row, with the name of the source is starting with "AXREP*" in Excel VBA

[Cakz Primz]

Dear all,

I have to open a latest update file in share folder, the name of the workbook is always changing, but it always begin with this word "AXREP_20221103.xlsb", or AXREP_20221104.xlsb", etc.
And it only has one sheet "AXREP", this will become my source.

I have the code below, after the workbook is being opened:

Sub CopyAXREP()
    Dim wb As Workbook

    For Each wb In Application.Workbooks
    If wb.Name Like "AXREP*" Then wb.Activate: Exit Sub '===Sub is exited at this point, so if the workbook is open, no further code will be executed. So the result would simply be the Actuals Repository workbook would be activated
    Next wb
    Sheets("AXREP").Activate
    
    
    Dim ws As Worksheet
    Set ws = ActiveSheet
    
    Dim lRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    With ws
        .Range("B2:B" & lRow).Copy Workbooks("Target").Sheet("Dataset").Range("B2")
    End With
    Workbooks("AXREP*.xlsb").Close SaveChanges:=True
End Sub

But it stop in this code:

    Sheets("AXREP").Activate

How I can active "AXREP" sheet in source workbook ("AXREP*.xlsb) so I can copy from column B until the last row?

Thanks in advance.
Prima Indonesia

 

[Cakz Primz]

I

Dear all,

I have to open a latest update file in share folder, the name of the workbook is always changing, but it always begin with this word "AXREP_20221103.xlsb", or AXREP_20221104.xlsb", etc.
And it only has one sheet "AXREP", this will become my source.

I have the code below, after the workbook is being opened:

Sub CopyAXREP()
    Dim wb As Workbook

    For Each wb In Application.Workbooks
    If wb.Name Like "AXREP*" Then wb.Activate: Exit Sub '===Sub is exited at this point, so if the workbook is open, no further code will be executed. So the result would simply be the Actuals Repository workbook would be activated
    Next wb
    Sheets("AXREP").Activate
   
   
    Dim ws As Worksheet
    Set ws = ActiveSheet
   
    Dim lRow As Long
    lRow = Cells(Rows.Count, 1).End(xlUp).Row

    With ws
        .Range("B2:B" & lRow).Copy Workbooks("Target").Sheet("Dataset").Range("B2")
    End With
    Workbooks("AXREP*.xlsb").Close SaveChanges:=True
End Sub

But it stop in this code:

    Sheets("AXREP").Activate

How I can active "AXREP" sheet in source workbook ("AXREP*.xlsb) so I can copy from column B until the last row?

Thanks in advance.
Prima Indonesia

For your additional information, how to activate source workbook, with the name starting with "AXREP_":

Windows("AXREP_*.xlsb").Activate

Thank you very much
Prima Indonesia