Selecting multiple cells from activecell reference (not sequenced)

[finaljustice]

Hello, I've been trying to select/ copy multiple cells that are not sequenced, such as for example

Range("B12,H12,K12,O12").Select

How do I do this from a activecell reference. Here is my code where I tried doing so with no success.

Do While ActiveCell <> Empty
    If ActiveCell.Offset(0, 166).Value = 2011 Then
        If ActiveCell.Offset(0, 155).Value > 0 Then 'vantajoso
        Range("cell(0,0),cells(0,20),cells(0,24),cells(0,27),cells(0,165),cells(0,168),cells(0,170),cells(0,171)").Copy ' this is where I can't make it
 
work. Basically what I tried to do was each "CELL()" being displaced in relation to the activecell.
        Sheets("2011").Select
        Range("B3").PasteSpecial (xlPasteValues)
        ActiveCell.Offset(1, 0).Select
 
        Else ' ñ vantajoso
        Range("cells(0,0),cells(0,20),cells(0,27),cells(0,165),cells(0,168)").Copy
        Sheets("2011").Select
        Range("B1048576").End(xlUp).Select
        ActiveCell.Offset(1, 0).PasteSpecial (xlPasteValues)
 
        End If
          'outros anos
    End If

Thank you before hand for your attention and help.
Your sinecerley,
Final.

 

[shg]

You don't need to select at all, but you can reference in these ways.

This selects the cells cols B, H, K and O in the row of the active cell:

Rows(ActiveCell.Row).Range("B1,H1,K1,O1").Select

This selects the same cells, offset by the column of the active cell:

ActiveCell.Range("B1,H1,K1,O1").Select

 

[finaljustice]

You don't need to select at all, but you can reference in these ways:

Rows(ActiveCell.Row).Range("B1,H1,K1,O1").Copy

or

ActiveCell.Range("B1,H1,K1,O1").Copy

I didn't quite get it, because, through my macro, i'm going down a list a in column B and as it goes depending i will have to copy cells to the according to my activecell. So basically what I am trying to do can be represented as this so you can understand.

Range("Activecell.offset(0,0),Activecell.offset(0,20),Activecell.offset(0,27),Activecell.offset(0,165),Activecell.offset(0,168)").Copy

Thanks for your attention and quick reply!

 

[shg]

activecell.range("A1, T1, AA1, FI1, FL1").copy

 

[finaljustice]

activecell.range("A1, T1, AA1, FI1, FL1").copy

Thats the thing the I can't use that since the ROW changes.... according to my activecell.

 

[shg]

Maybe you should just try it before assuming it won't work ...

 

[finaljustice]

Maybe you should just try it before assuming it won't work ...

I'm sorry for assuming that it would not work. I made that assumption based upon my limited knowledge of VBA which appeared to me that it would copy the referenced cells I.E "A1, T1, AA1, FI1, FL1" selecting those cells, which indeed is not the case. Thank you for your patience. Now I made up a little test to try and understand how this works.

activecell.range("A1, T1, AA1, FI1, FL1").copy

Now please bear with me. I set up a simple test, in a sheet I made a small list in column B (list goes from B2:B6) , columns C2:D6 I just worte "bla bla" (unwanted info), E2:E6 contains info that i want, F2:G6 "bla bla" (more unwanted info), H2:H6 containing the info I want.

Then from my understanding of your code, I coded

ActiveCell.Range("B1,E1, H1").Copy
Range("B15").PasteSpecial (xlPasteValues)

What I realised was that that code was selecting the column C, F, I. All offset 1 to the right of what I intended. To correct this I have to tell the formula to get the one to the column to the left of what I really intend. Could you please help me understand why??

So eventhough I want the columns B, E, H. I have to select A, D, G.
This works fine:

ActiveCell.Range("A1,B1,G1").Copy
Range("B15").PasteSpecial (xlPasteValues)

I really don't see why. Could you please help me understand?

Thank you for your attention and sorry if I disrespected you in any way that was not my intention.
My sincere appologies.

 

[shg]

You don't say what cell was slected when you did this.