Copy column based on title in VBA

AJ12345

New Member
Joined
Feb 8, 2014
Messages
22
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

Board Regular
Joined
Nov 28, 2013
Messages
225
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.

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

AJ12345

New Member
Joined
Feb 8, 2014
Messages
22
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.

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

AJ12345

New Member
Joined
Feb 8, 2014
Messages
22
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



ResourceArtValueCompany
451291
4613102
4714113
4815124

<tbody>
</tbody><colgroup><col><col span="3"></colgroup>



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

lexxie2013

Board Regular
Joined
Nov 28, 2013
Messages
225
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)

Code:
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

New Member
Joined
Feb 8, 2014
Messages
22
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)

Code:
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
 

Forum statistics

Threads
1,082,380
Messages
5,365,124
Members
400,824
Latest member
Themilkybarkid

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top