I'm trying to set a macro on Sheet 1 to copy a column of data from Sheet 2 but the column number is based on a number value on Sheet 1.

Here's what I have so far:

bent = Cells(2, 2)                               'this variable is defined in Sheet1.
           
                        With Sheets("Sheet2")    'This information is on Sheet2.
                                 Range(.Cells(7, bent - 1), .Cells(7, bent - 1).end(xldown)).select
                                 Selection.Copy
                        End With

I get an "object doesn't support this property or method".

When I ran this same code for clearing content, it works perfectly:

bent = Cells(2, 2) ' this cell is on sheet 1, where the macro button is located.
           
                        With Sheets("MCT") 'this selection to be cleared is on another sheet
                                    Range(.Cells(2, bent - 1), .Cells(4999, bent - 1)).ClearContents
                        End With

So I'm a bit confused why my modification of it doesn't work to select a cell on Sheet2 based on a number on Sheet1 and use "xlDown" to pick all the information from that starting cell to the bottom of it and copy it to clipboard.

Any help is appreciated.

CodePudding user response：

The syntax is not valid. Also, if you say the variable bent is on Sheet1 then you should qualify that by including that information when referencing. You will need to find the last row for that column first, then specify the range. Additionally, you should avoid using Select or Selection.

bent = Sheets("Sheet1").Cells(2, 2) 'this variable is defined in Sheet1.
       
With Sheets("Sheet2")    'This information is on Sheet2.
    LR = .Cells(Rows.Count, bent - 1).End(xlUp).Row
    Range(.Cells(7, bent - 1), .Cells(LR, bent - 1)).Copy
End With

CodePudding user response：

Copy a Range

• Use Option Explicit which forces you to declare all variables.
• Qualify your objects: worksheets with the corresponding workbook (object), e.g. ThisWorkbook., and ranges, cells, rows, and columns with their corresponding worksheet (object), e.g. wb.Worksheets("Sheet1")..
• No need to select anything.

Option Explicit

Sub Test()
    
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    Dim bent As Variant: bent = wb.Worksheets("Sheet1").Range("B2").Value
    If VarType(bent) <> vbDouble Then Exit Sub ' not a number
           
    With wb.Worksheets("Sheet2")
        Dim LR As Long: LR = .Cells(.Rows.Count, bent - 1).End(xlUp).Row
        If LR < 7 Then Exit Sub ' no data in column range
        .Range(.Cells(7, bent - 1), .Cells(LR, bent - 1)).Copy
    End With

End Sub