Input range and use it in VBA

[zarkoff]

Hi,

(sorry for my english).

I need to consolidate data from several sheets for which the range I have to look at is different by sheets.

So I created a table in which I put the name of the sheet and the range I need to look at:

Sheets	Range
BOB	A4:E2000,AS4:BD2000
JON	A4:E2000,F4:Q2000
EDI	A4:E2000,BF4:BQ2000

I wanted to use a for... next, but I get an error.

Sub test()

Set xl = Excel.Application
Set macr = xl.ActiveWorkbook
Set def = macr.Sheets("Range")
    
For a = 2 To 3

    sh_name = def.Cells(a, 1)
    sh_range = def.Cells(a, 2)

    Sheets(sh_name).Range(sh_range).Select
    Selection.Copy

'(... other tasks)

Next

End Sub

I get an error message '1004': Select method of Range class failed, at step " Sheets(sh_name).Range(sh_range).Select"

Do you have an idea how to solve my problem (or an alternative way) ?

Thank you.

 

[trunten]

You can't use "Select" on a range in a none active sheet. the sheet you're referencing has to be the active one in order to do that. You can skip that step though:

Sub test()
    Dim a&, sh_name$, sh_range$, def As Worksheet
    
    Set def = Sheets("Range")
    For a = 2 To 3
        sh_name = def.Cells(a, 1)
        sh_range = def.Cells(a, 2)
        Sheets(sh_name).Range(sh_range).Copy
    '(... other tasks)
    Next
End Sub

 

[zarkoff]

Oh, I was so close

Thank you.