Search code examples
excelvbafunctionrangecopy-paste

Using a variable named range in VBA code to copy the named range to another worksheet

I have 2 sheets. Named "M" and "C". I have a table (named Test) in sheet "M" with multiple columns (named col1, col2 etc. etc. Each column is a named range. In sheet "C" I have a formula (cell "I2") to name the column I want to copy from sheet "M" to sheet "C". i.e., "Test[col2]" or "Test[col3]". My problem is i cannot get the VBS code to reference cell "I2" and copy the range from sheet "M" to sheet "C". I have tried assigning "I2" as a variable. Dim col range. Set col = "I2". then in the copy section of the code I try to use "col" to no avail.

Table "Test"

1

=CONCATENATE(J2,I1,K2)

2

Solution

  • Copy an Excel Table Column

    enter image description here

    VBA

    Sub CopyColumn()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim dws As Worksheet: Set dws = wb.Sheets("C")
    Dim srgReference As String: srgReference = CStr(dws.Range("I2").Value)
    
    Dim sws As Worksheet: Set sws = wb.Sheets("M")

    Dim srg As Range:
    On Error Resume Next
        Set srg = sws.Range(srgReference)
    On Error GoTo 0
    
    If srg Is Nothing Then
        MsgBox "The reference """ & srgReference & """ is invalid.", _
            vbCritical
        Exit Sub
    End If
    
    Dim dCell As Range: Set dCell = dws.Range("I4") ' ???
    dCell.Resize(srg.Rows.Count).Value = srg.Value
    
End Sub

    Excel Formula

    • In Microsoft 365, a simple formula, e.g. in cell I4, will spill the result:
    =INDIRECT(I2)
    • The formula used in cell I2:
    =J2&"[Col"&K2&"]"