CODEWITHSUNDEEP
excelvba
unknown
unknown

Reputation: 3

How do i select a range of columns based on cell values?

I'm trying to select and copy the range of columns in workbook 2 based on the values in cell C2 and cell C3 from workbook 1.

Sub copy_data1()

Dim wbmain_name, wb1_name As Variant
Dim wb1_ws1_name As Variant
Dim start_columnletter As String
Dim end_columnletter As String
Dim sheet_1 As Worksheet
Dim sheet_2 As Worksheet

wbmain_name = Range("C5")
wb1_name = Range("B9") & Range("B10")
wb1_ws1_name = Range("C10")

'Settings.
Set sheet_1 = Workbooks(wb1_name).Sheets(wb1_ws1_name) '‹ Specify here the source sheet.
Set sheet_2 = Workbooks(wbmain_name).Sheets(wb1_ws1_name) '‹ Specify here the destination sheet.
start_columnletter = Range("C2").Value
end_columnletter = Range("C3").Value


'Copying.
sheet_1.Range(start_columnletter & ":" & end_columnletter).Copy sheet_2.Range(start_columnletter & ":" & end_columnletter)

End Sub

Upvotes: 0

Views: 145

Answers (2)

Richard
Richard

Reputation: 26

If you want to select a whole column try: Range("AS:AS").

It's pretty simple to solve your problem. If you want the column number:

Sub a()  
    
    Dim r As Range  
    Dim colNo As Integer  
    Set r = Range("AS:AS")  
    colNo = r.Column  
    MsgBox colNo  

End Sub

Long time not working with VBA, hope it does not go wrong.

Upvotes: 0

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2629

Try this code:

Sub SubCopy()
    
    'Declarations.
    Dim start_columnletter As String
    Dim end_columnletter As String
    Dim sheet_1 As Worksheet
    Dim sheet_2 As Worksheet
    
    'Settings.
    Set sheet_1 = Workbooks("workbook 1.xlsm").Sheets(1) '‹ Specify here the source sheet.
    Set sheet_2 = Workbooks("workbook 2.xlsm").Sheets(1) '‹ Specify here the destination sheet.
    start_columnletter = sheet_1.Range("C2").Value
    end_columnletter = sheet_1.Range("C3").Value
    
    'Copying.
    sheet_1.Range(start_columnletter & ":" & end_columnletter).Copy sheet_2.Range(start_columnletter & ":" & end_columnletter)
    
End Sub

About your original code:

    'get column letter
    start_columnletter = Range("C2").Value '‹ This will refer to the range C2 of the active sheet. It might not be the one you want.
    end_columnletter = Range("C3").Value '‹ This will refer to the range C2 of the active sheet. It might not be the one you want.
    
    'get column number based on column letter
    start_columnnumber = Range(start_columnletter & 1).Column '‹ This is not really necessary since you have start_columnletter already. Also it will refer to the active sheet.
    end_columnnumber = Range(end_columnletter & 1).Column '‹ This is not really necessary since you have end_columnletter already. Also it will refer to the active sheet.
    
    Workbook("workbook2").Range(Columns(start_columnnumber), Columns(end_columnnumber)).Select
    'The line up here won't work because:
    '-Workbook("workbook2") does not exist; Workbooks("workbook2") might be the one you are looking for;
    '-Workbook("workbook2").Range does not exist; Workbooks("workbook2").Sheets(1).Range might be the one you are looking for;
    '-the Select method will select the range; it will not copy it; avoid selecting objects as much as possible;
    '-the Select method will not select the range this way unless the parent sheet (and of course the parent workbook) is already selected; such might not be the case already.

The "subscript out of range" error might be caused by a wrong reference in the name of the workbooks. Make sure they are correctly reported. Maybe a space shouldn't be there or the file extension in not correct (.xls .xlsx .xlsm). About the last code you posted:

Sub copy_data1()
    
    Dim wbmain_name, wb1_name As Variant '‹ I'd suggest to set them as String.
    Dim wb1_ws1_name As Variant  '‹ I'd suggest to set it as String.
    Dim start_columnletter As String
    Dim end_columnletter As String
    Dim sheet_1 As Worksheet
    Dim sheet_2 As Worksheet
    
    wbmain_name = Range("C5")  '‹ I'd suggest to set is as Range("C5").Value ; what's in range C5? It also refers to the active sheet of the active workbook.
    wb1_name = Range("B9") & Range("B10")  '‹ Same as wbmain_name comment.
    wb1_ws1_name = Range("C10") '‹ Same as wbmain_name comment.
    
    'Settings.
    Set sheet_1 = Workbooks(wb1_name).Sheets(wb1_ws1_name)
    Set sheet_2 = Workbooks(wbmain_name).Sheets(wb1_ws1_name)
    start_columnletter = Range("C2").Value '‹ You should refer to sheet_1.
    end_columnletter = Range("C3").Value '‹ You should refer to sheet_1.
    
    
    'Copying.
    sheet_1.Range(start_columnletter & ":" & end_columnletter).Copy sheet_2.Range(start_columnletter & ":" & end_columnletter)
    
    
End Sub

Upvotes: 0

Related Questions