Copy column based on title in VBA

[AJ12345]

Hi.
I need to copy values in a column. Which column varies, but the title of the column (in row 1) is always the same. I had:

Dim Constant
Constant = Range("C:C")

The problem is that the values I want may or may not be in column C.

The column I want to copy has the text "Resource" in row 1. This text will never change.

Please help.

Regards

AJ

 

[lexxie2013]

Hi. The following sub will do as you ask. It assumes the column you're copying is in the activesheet and you'll have to change the destination. Good Luck.

Sub CopyResourceCol()
ColumnNumber = Application.Match("Resource", ActiveSheet.Rows(1), 0)
ActiveSheet.Columns(ColumnNumber).Copy Destination:=Sheets("Sheet1").Columns(10)
End Sub

 

[AJ12345]

Thank you.
If I would like to give the values in the column a name (previous I used dim constant constant = Range("C:C") - And then when I needed to paste the values from column C I would use Range"(Whatever") = Constant... how would I do that?

The truth is that I have several columns I want to copy (they got different text in row 1) and I want to past them several times in different places in the workbook. So the active columns must be copied and named "Constant1", "Constan2" etc.

Hi. The following sub will do as you ask. It assumes the column you're copying is in the activesheet and you'll have to change the destination. Good Luck.

Sub CopyResourceCol()
ColumnNumber = Application.Match("Resource", ActiveSheet.Rows(1), 0)
ActiveSheet.Columns(ColumnNumber).Copy Destination:=Sheets("Sheet1").Columns(10)
End Sub

 

[lexxie2013]

I'm not too clear. Can you give a couple of examples.

 

[AJ12345]

Sure.

If this is my spreadsheet: The data are downloaded from another source, so the columns might swich place. For instance "Art" (which is now in column B) might as well be in column A,C or D.
I need to give the different columns names so I later in my macro can paste them correctly. Previous I would just write:
dim Resource
dim Art
dim company
Resource = Range("A:A")
Art = Range("B:B")
Company = Range("D:D")

And when I wanted to paste the different values, I could just use Range(X:X) = Company



[TABLE="width: 281"]
<tbody>[TR]
[TD]Resource[/TD]
[TD]Art[/TD]
[TD]Value[/TD]
[TD]Company[/TD]
[/TR]
[TR]
[TD="align: right"]45[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD="align: right"]46[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]47[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="align: right"]48[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody><colgroup><col><col span="3"></colgroup>[/TABLE]

I'm not too clear. Can you give a couple of examples.

 

[lexxie2013]

Ok. You could use the following to allocate the correct column numbers, which you'd then use when you copy/paste (changing the YourSheet and YourColumnNumber to wherever you want them to go)

Sub CopyColumns()
ResourceColumn = Application.Match("Resource", ActiveSheet.Rows(1), 0)
ArtColumn = Application.Match("Art", ActiveSheet.Rows(1), 0)
CompanyColumn = Application.Match("Company", ActiveSheet.Rows(1), 0)
ValueColumn = Application.Match("Value", ActiveSheet.Rows(1), 0)


' Now if you want to copy, say, the Art column you'd put

ActiveSheet.Columns(ArtNumber).Copy Destination:=Sheets("YourSheet").Columns(YourColumnNumber)
End Sub

 

[AJ12345]

Hi. The point is that i need to copy those colums in to several different places. When I gave the colums a name (for instance: dim art) I could past the art values several times.

I need the different columns to be defined in that way. Do you know how?

Ok. You could use the following to allocate the correct column numbers, which you'd then use when you copy/paste (changing the YourSheet and YourColumnNumber to wherever you want them to go)

Sub CopyColumns()
ResourceColumn = Application.Match("Resource", ActiveSheet.Rows(1), 0)
ArtColumn = Application.Match("Art", ActiveSheet.Rows(1), 0)
CompanyColumn = Application.Match("Company", ActiveSheet.Rows(1), 0)
ValueColumn = Application.Match("Value", ActiveSheet.Rows(1), 0)


' Now if you want to copy, say, the Art column you'd put

ActiveSheet.Columns(ArtNumber).Copy Destination:=Sheets("YourSheet").Columns(YourColumnNumber)
End Sub