Selecting a column using a variable

[Linda21]

I am trying to set an entire column as a variable. I want to set the column variable as the 3rd column. When running the code, I want to select the entire column 3 by using the variable. Then I want to increase each column by 1 and run code again.
Is there something wrong with the variable below?
How do I select the entire column when using a variable?

PasteColumn = 3

Thank you.

 

[Scott Huish]

Use a range object variable for ranges:

Sub test()
Dim c As Range
Set c = Range("C:C")
c.Select
End Sub

 

[Linda21]

Thanks for the response...however I get Run-time error '91': Object variable or With block variable not set. Any ideas?

Dim c As Range
Set c = Range("C:C")

Sheets("Self").Select
Columns("J:J").Select
Selection.Copy
Sheets("Summary").Select
c.Select

 

[Norie]

Try this:

Dim MyVariable As Long
 
      MyVariable = 3
 
      Sheets("Self").Columns("J:J").Copy Sheets("Summary").Columns(MyVariable)

That's kind of a guess.

What I think you are trying to do is copy column J from the sheet Self to column C of sheet Summary.

 

[mirabeau]

Thanks for the response...however I get Run-time error '91': Object variable or With block variable not set. Any ideas?

Dim c As Range
Set c = Range("C:C")

Sheets("Self").Select
Columns("J:J").Select
Selection.Copy
Sheets("Summary").Select
c.Select

Which sheet are you wanting your c range to be defined on?

As it is you seem to be defining c as a range rather than as a variable, so if you're not clear what you want it's likely the code may become confused.

In your above snippet, move the line
Set c = Range("C:C")
to immediately below
Sheets("Summary").Select
and see if it helps.

Incidentally, too much Select commands in VBA coding is generally not a good idea.

 

[Linda21]

Thanks everyone. I used the 'MyVariable' example from Norie.