Trying to select R1C1 range in VBA for copy/paste

[raynman1972]

Hello all,

Long time reader, first time poster. I am trying to create a dynamic range using R1C1 that will let me loop through (I have this piece solved for) a number of matrices and copy from one sheet to another- basically, I want to increment the R1C1 values in my loop and step through the ranges.

My challenge is trying to select the R1C1 range so that I can copy it. What is the proper grammar to use here?

Sub Make_VTO_CSV()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With
    
    Dim a_col, b_col, a_row, b_row As Integer
    Dim CopyRange As Range
    
    a_col = 59
    b_col = 68
    a_row = 5
    b_row = 52
    
    Sheets("VTO_UPLOAD").Select
    Cells.Select
    Selection.ClearContents
    
'Problem starts here  
 
    Sheets("Overstaffing_OUT").Select
    CopyRange = Range(Cells(a_col, a_row).Address, Cells(b_col, b_row).Address).Address

 'Problem ends here
       
    Sheets("VTO_UPLOAD").Select
    Range("A1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
        
With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With

End Sub

 

[Jonmo1]

Welcome to the baord..

Try

Set CopyRange = Range(Cells(a_row, a_col), Cells(b_row, b_col))
CopyRange.Copy

 

[raynman1972]

Outstanding, +1 for you. Thanks a bunch!