Set VBA Range Equal to Cell Value-CodePudding

I have written a formula in cell C2 of my worksheet where I define a range based on a variety of factors. The value in cell C2 is F13:G65.

I would now like to copy that range using VBA, where VB recognizes the range in cell C2 as the range that it should copy. Keep in mind that the range of F13:G65 will change, which is why I want VBA to copy whatever range I have in cell C2.

Can someone help me define the code to do this?

CodePudding user response：

Copy a Range: Avoiding 'Surprises'

Sub CopyRange()
    
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = wb.Worksheets("Sheet1") ' adjust!
    
    ' Write the range address to a string variable ('rgAddress').
    Dim rgAddress As String: rgAddress = CStr(ws.Range("C2").Value)
    ' Use 'CStr' to convert to string to avoid an error occurring
    ' if the cell contains an error value.
    
    ' Attempt to reference the range ('rg').
    Dim rg As Range
    On Error Resume Next ' defer error trapping (ignore errors)
        Set rg = ws.Range(rgAddress)
    On Error GoTo 0 ' disable error trapping
    
    ' Validate the range.
    If rg Is Nothing Then
        MsgBox "'" & rgAddress & "' is not a valid range address.", vbCritical
        Exit Sub
    End If
    
    ' Continue, e.g.:
    rg.Copy
    
End Sub

CodePudding user response：

Just point to the value in C2 as the range to copy:

Sub CopySomeRange()
Range(Range("C2").Value).Copy
End Sub

As Scott said in his comment, you can also specify the Sheet if you don't want to use the ActiveSheet.

For example, if C2 is on Sheet1, but you want to copy from the ActiveSheet, you could use:

Range(Sheets("Sheet1").Range("C2").Value).Copy

You can also shorten it a bit to Range([C2]).Copy if you're so inclined: