Excel VBA: replace data in range with data from another workbook

[2Took]

Two workbooks: WBDestination and WBSource

Data needed starts at A3 in WBSource and goes down and to the right, variable distance
Need to clear out old data in WBDestination and replace it with new data from WBSource

Data in WBDestination needs to go starting in A10, down and to the right

I plan to trigger the code by right click to A7 in WBDestination

WBSource name consistently starts with "AB - CDEF" and then name may vary

Already have the code below to identify WBSource among open, just need the code for the rest:

Sub ActivateWBbyName()
    Dim iWorkbook As Workbook
    For Each iWorkbook In Application.Workbooks
        If iWorkbook.Name Like "AB - CDEF*" Then iWorkbook.Activate   
‘code to do, call a macro
    Next iWorkbook
End Sub

 

[mmhill]

I made some assumptions

Sub ActivateWBbyName()

    Dim wbSource As Workbook
    
    'You don't need to activate the wbSource. If we Exit For, we can just use wbSource to refer to it.
    For Each wbSource In Application.Workbooks
        If wbSource.Name Like "AB - CDEF*" Then Exit For
    Next wbSource

    'Data needed starts at A3 in WBSource and goes down and to the right, variable distance
    '### YOU DIDNT SAY WHICH WORKSHEET THE SOURCE DATA IS IN.  I ASSUME A SHEET NAME ###
    '### CurrentRegion gets the area of contiguous cells
    
    Dim rngSource As Range
    With wbSource.Worksheets("XYZ") '<< PUT CORRECT SHEET NAME HERE
        
        Set rngSource = .Range("$A$3").CurrentRegion
    
        'if you need to make adjustments to the current region selected like for headers, edit the 0 numbers below; if no adjustment needed, delete this line
        Set rngSource = rngSource.CurrentRegion.Offset(0, 0).Resize(rngSource.CurrentRegion.Rows.Count + 0, rngSource.CurrentRegion.Columns.Count + 0)
    
    End With
    
    Dim wbDestination As Workbook, rngClear As Range
    
    'you do not say what the destination workbook is or what worksheet to use.
    Set wbDestination = ActiveWorkbook
    With wbDestination.Worksheets("XYZ") '<< PUT CORRECT SHEET NAME HERE
    
        'Data in WBDestination needs to go starting in A10, down and to the right
        Set rngClear = .Range("$A$10").CurrentRegion
    
        'if you need to make adjustments to the current region selected like to remove headers,edit the 0 numbers below; if no adjustment needed, delete this line
        Set rngClear = rngClear.CurrentRegion.Offset(0, 0)
        
        rngClear.ClearContents: rngClear.ClearFormats
        
    End With
    
    rngSource.Copy rngClear.Cells(1, 1)
    
    Set wbSource = Nothing: Set wbDestination = Nothing
    Set rngSource = Nothing: Set rngClear = Nothing
    
End Sub

 

[2Took]

I made some assumptions

Sub ActivateWBbyName()

    Dim wbSource As Workbook
   
    'You don't need to activate the wbSource. If we Exit For, we can just use wbSource to refer to it.
    For Each wbSource In Application.Workbooks
        If wbSource.Name Like "AB - CDEF*" Then Exit For
    Next wbSource

    'Data needed starts at A3 in WBSource and goes down and to the right, variable distance
    '### YOU DIDNT SAY WHICH WORKSHEET THE SOURCE DATA IS IN.  I ASSUME A SHEET NAME ###
    '### CurrentRegion gets the area of contiguous cells
   
    Dim rngSource As Range
    With wbSource.Worksheets("XYZ") '<< PUT CORRECT SHEET NAME HERE
       
        Set rngSource = .Range("$A$3").CurrentRegion
   
        'if you need to make adjustments to the current region selected like for headers, edit the 0 numbers below; if no adjustment needed, delete this line
        Set rngSource = rngSource.CurrentRegion.Offset(0, 0).Resize(rngSource.CurrentRegion.Rows.Count + 0, rngSource.CurrentRegion.Columns.Count + 0)
   
    End With
   
    Dim wbDestination As Workbook, rngClear As Range
   
    'you do not say what the destination workbook is or what worksheet to use.
    Set wbDestination = ActiveWorkbook
    With wbDestination.Worksheets("XYZ") '<< PUT CORRECT SHEET NAME HERE
   
        'Data in WBDestination needs to go starting in A10, down and to the right
        Set rngClear = .Range("$A$10").CurrentRegion
   
        'if you need to make adjustments to the current region selected like to remove headers,edit the 0 numbers below; if no adjustment needed, delete this line
        Set rngClear = rngClear.CurrentRegion.Offset(0, 0)
       
        rngClear.ClearContents: rngClear.ClearFormats
       
    End With
   
    rngSource.Copy rngClear.Cells(1, 1)
   
    Set wbSource = Nothing: Set wbDestination = Nothing
    Set rngSource = Nothing: Set rngClear = Nothing
   
End Sub

"CurrentRegion gets the area of contiguous cells" - meaning cells have to contain data without a break, I'm guessing...
What would be a simpler way than "CurrentRegion"?
I'd like to just be able to hardcode the starting point like above A3 & A10, but without then having to offset/resize the region...

 

[mmhill]

In my opinion there is no simpler ways than CurrentRegion to get a block of contiguous cells. CurrentRegion is a simple concept, shorter and more clear in the code and generally more reliable.
But, there are other ways. To each his own.

Consider these examples.

HAS HEADERS AND YOU WANT THEM OR HAS NO HEADERS

'If table has headers and you want headers in your selection
'OR if table has no headers
    Set x = Sheet1.Range("A1").End(xlToRight).End(xlDown)   ' << LEFT TABLE with a missing header and no value in A4, this selects A1:B3  Error!
    Set x = Sheet1.Range("A1").CurrentRegion ' << LEFT TABLE with a misisng header and no value in A4, this selects A1:E6

    Set x = Sheet1.Range("A1").End(xlToRight).End(xlDown)   ' << RIGHT TABLE with no value in C1 or A4, this selects A1:B2 Error!
    Set x = Sheet1.Range("A1").CurrentRegion ' << LEFT TABLE with no value in C1 or A4, this selects A1:E6

HAS HEADERS AND YOU DO NOT WANT THEM

'If table has headers and you do not want them in the selection
    Set x = Sheet1.Range("A2").End(xlToRight).End(xlDown) '<< LEFT TABLE, this selects A2:E3 Error!
    Set x = Sheet1.Range("A1").CurrentRegion.Offset(1, 0) ' << LEFT TABLE. this selects A2:E7 (same # of rows but shifted down 1. fine in many cases)

    'if you cannot have that extra row, ...  
    With Sheet1.Range("A1").CurrentRegion
        Set x = .Offset(1, 0).Resize(.Rows.Count - 1) '<<LEFT TABLE, this selects A2:E6
    End With

In the examples above, if there is no blank header or data in the column or row you are applying .End(xlToRight).End(xlDown) to, you will pickup the whole table. But, why risk it when CurrrentRegion doesn't have that issue?