Copy Columns based on Header name

Hi, what I have is several columns on two different spreadsheets that
are not in the same order. Looking for a macro that will allow me to
copy one column on one sheet to another column on another worksheet..

Tried to make this simple by labeling about the column headers 1-65 so
that what I would like to do is have the macro look on sheet 2 find
the number that matches and bring back that columns data.. the only
kicker is that it needs to copy only down to the last data row..guess
what I am saying is this things need to copy all the data from one
column and past it in another worksheet in the column in which the
headers match

thanks in advance for any help on this
0
3/15/2012 4:38:21 PM
excel 39879 articles. 2 followers. Follow

1 Replies
818 Views

Similar Articles

[PageSpeed] 52

On Mar 15, 12:38=A0pm, Thomp <williamth...@gmail.com> wrote:
> Hi, what I have is several columns on two different spreadsheets that
> are not in the same order. Looking for a macro that will allow me to
> copy one column on one sheet to another column on another worksheet..
>
> Tried to make this simple by labeling about the column headers 1-65 so
> that what I would like to do is have the macro look on sheet 2 find
> the number that matches and bring back that columns data.. the only
> kicker is that it needs to copy only down to the last data row..guess
> what I am saying is this things need to copy all the data from one
> column and past it in another worksheet in the column in which the
> headers match
>
> thanks in advance for any help on this

It's not clear to me what part of your task you are having trouble
with.
- Finding the correct column
- Finding the length of the column
- Specifying the range for  the copy
- The copy
- The change to the other sheet
- The paste

Can you do this operation manually? Turn on the macro record and
see what the operations recorded are. Maybe you can mod that and
get what you need.

    Sheets("Sheet1").Select
    Range("D5:D7").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D5").Select
    ActiveSheet.Paste
    Application.CutCopyMode =3D False

Socks
0
puppet_sock (108)
3/16/2012 2:34:24 PM
Reply:

Similar Artilces:

tranposing every third cell in a row to a column
How do you transpose every third cell in a row of data to a column on a separate worksheet. There should be no empty cells in the column of the separate worksheet that this information is being copied to. Say the data was in Row 1. This will go from A to D to G ... etc. Enter this formula anywhere, and copy down: =INDEX(Sheet1!$1:$1,3*ROWS($1:1)-2) -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Wabash" <Wabash@discussions.micro...

Inbox
I am using Outlook 2007 sp2 A large number of email notifications have appeared in my inbox - they have no From or Subject and no content they only show a reveived date and a size nearly all at 326b -- I asssume that they are some form of spam - how can I get these items to go straight to my junk mail box other than identifying each one adding to my blocked sender list If these messages are not caught by the Junk E-mail filter already, try increasing the scanning level. If that doesn't work either, create a rule with the action to move all messages to the Junk Email folder...

Adding items in a column if value in a second column is X.
Hi, This one is getting the better of me, even though I know I should be able to figure it out myself!: I have two columns of data: B and C. I have one column of ID#: A I would like to subtract C from B in all rows where ID# is X and display the sum of these values in a single cell. I would then like to do the same for all rows where ID# is Y. Please help! Many thanks for your time, Paul Try these: =3DSUMPRODUCT((A1:A100=3D"X")*(B1:B100-C1:C100)) =3DSUMPRODUCT((A1:A100=3D"Y")*(B1:B100-C1:C100)) Adjust column ranges to suit, but you can't have a complete column ...

subtract amounts from different columns?
I have 3 columns with, income, expenditure & balance. How can I get the balance to auto fill using the other two columns? Also want the total at the bottom of the sheet for each column, after deductions have been made. Take a look at one of Microsoft's hundereds of templates offered on-line (freely downloadable): http://office.microsoft.com/en- us/templates/CT010317261033.aspx Look for "checkbook register". HTH Jason Atlanta, GA >-----Original Message----- >I have 3 columns with, income, expenditure & balance. How can I get the >balance to auto fill us...

Can I use variables for workheet name references in Excel functions?
I'm working with Excel 2003 and trying to look for a way to create some sort of dynamic references in functions between worksheets in the same workbook. Let me try to explain - Say I have a workbook with 12 different worksheets with the names - January, February, March, Etc... On a new Worksheet I want to write a function that will use a cell as a reference for the worksheet to use in the function. I'm going to have a function in that cell that will return a name (In this example - Cell A2) In Another cell, I want to write a function that will retrieve the highest value from a ce...

Copy An Apointment
Here is some script I worked hard on with Microsoft. Since it works real well, I thought I would share. It could easily be recycled for other windows. This is a method to copy an appointment: In the ISV.Config File Add: =================================================== <Entity name="appointment"> <ToolBar ValidForCreate="1" ValidForUpdate="1"> <Button Title="" ToolTip="Copy this appointment to a new apppointment" Icon="/_imgs/ico_16_merge.gif" JavaScript=" var date_now = new Date; //Convert...

How to save formulas while copying worksheet?
I'm trying to copy a worksheet i created as a template as a new worksheet in another workbook without having my formulas change in the process. I have tried all the pasting options and all the formulas get change from ex.: =Material!A4 to ='[Template.xls]Material'!A4 In other words, reference to the template file are added to the formulas but i don't want that... i just want the copy the original formulas as they appear in the template. Any way of doing this or will i have to copy and paste and then edit all the affected formulas to remove any reference to the template file?...

BLANK FORM BASED ON A QUERY
I Have Created a Dialog Form With a Button That Opens Another form, the Form That Gets Opened By The Dialog form is Based On a Query, and The Creteria Is Set To A value Inputed on the Dialog Form, Which Works Great When I Have Results Turned Up, If No Results The Form is Blank, Is Their A Code i can put in the CLick Event of the Dialog form that Opens the form, if there are no Results Wont Allow me to Open the form. and a Message.. Ive Tried The Following DoCmd.OpenForm "Project_Results_ByRecievedDate" If Me.RecordsetClone.RecordCount = 0 Then MsgBox "No records&qu...

Sum cells in columns based on condition
Dear all, I would like to multiply and add two columns together using an appropriate function - best if i could avoid anything too complicated like vba :-P The criteria for this is to multiply and add rows in the columns following the A's Col1 ... Col5 A 1 2000 H 2 1000 A 2 2000 So the sum should read 1x2000+2x2000 = 6000 (Col2 to Col4 also contain values as Col1.) I'm not sure how to do this... - I can't put the A's in any other place other than above the numbers, so the sum product doesn't work - I've put in a conditional format to colour the ce...

better way to copy data from an embedded html box
i have some fields from a browser window pasted into excel. the become shapes when pasted into excel. i give the shapes names starting with box 1 to box ?, depending on what's been pasted into excel. when one shape is selected, it shows the following the the formula bar: embed("forms.html:text.1",""). right now i have some code that will copy the data from the field: With ws.Shapes("Box " & i) Selection.Verb Verb:=xlPrimary SendKeys "^A", True SendKeys "^C", True end with this wor...

how to CHANGE THE name "rajeev Chhajer" to "Chhajer rajeev"
I want to change the name and title placements in a cell/ worksheet/excel file. For ex. the content of the cell is "Rajeev Chhajer" and i wanna change to "Chhajer Rajeev" Pl suggest for the same, if u know. with your name in A1, use =MID(A1,FIND(" ",A1)+1,99)&" "&LEFT(A1,FIND(" ",A1)-1) cheers teylyn 'The Code Cage - Microsoft Office Help - Microsoft Office Discussion' (http://www.thecodecage.com) Rajeev Chhajer;671235 Wrote: > I want to change the name and title placements in a cell/ works...

Getting & Using Sheet Names or Index in VBA code
How do you get each Sheet Name in a Workbook so you can use it in code? I know that each Sheet, regardless of Name has an Index of 1 through n beginning with the leftmost Sheet but I don't know how to use that information. I have one reference book, Excel 2003 VBA, that says there is a Worksheet Property 'Name' that returns a String and in the Description of that Property it says 'Set/Get the name of the worksheet'. Unfortunately, it doesn't tell me how to use that in code. I don't really care whether I use the name of each Sheet or the Index but if you c...

Column Headings
Firstly, Happy New Year to you all. In my Outlook 2003 IN BOX I have lost the FROM column. Can anybody tell me how to get it back? Cheers Greg Hi, Greg; Right-click any of the remaining column headers, select "field chooser," find the "from" field, and drag it to the row of column headers. And a Happy New Year to you, IanRoy "Greg" wrote: > Firstly, Happy New Year to you all. > > In my Outlook 2003 IN BOX I have lost the FROM column. > > Can anybody tell me how to get it back? > > Cheers > Greg > > > ...

copying formulas from worksheet to worksheet
I need to copy a row of cells from one worksheet to a column of another worksheet. I want it to reference the cells listed in the row of one worksheet to the column of the other worksheet. I found out how to do the reverse - =offset('worksheet name'!$a$1,0,row(1:1)-1) - but it doesn't work for copying rows to columns? Can you help? Think the way that your Q was phrased, an adaptation of the formula you posted should work Assume you have in Sheet1,in B2:E2, the numbers: 1,2,3,4 In Sheet2 ------------ Put in the start-cell, say B2? : =OFFSET(Sheet1!$B$2,,ROWS($A$1:A1)-1)...

To set the whole row in a particular colo. (based on some conditio
Hi, I was to set a particular color to a row based on some condition. ForEx: if the value of a cell E5 is 'closed', then set that entire row to 'grey' color. How do I do that? Use conditional formatting. Select all the rows (let's assume it is E-M in this case). Go to CF. Change Condition 1 to Formula Is Add a formula of =$E5="closed" Click Format button and format as desired OK out -- HTH RP (remove nothere from the email address if mailing direct) "Venkatesh V" <VenkateshV@discussions.microsoft.com> wrote in messag...

count how many different numbers in the column and return to the n
I have a sheet Amount Item $12 1 $2 1 $8 3 $98 2 $23 2 the thing I would like to do is item amount 1 total(amount part is easy, I can use sumif()) 2 *** 3 *** the item part is hard, I tried if(countif()>0,item #), but I have 7 items in total, if I have only 3 items in the colume, I will have 4 empty rows. A PivotTable would work very nicely here because it would automatically condense/expand to include the number of different items you need, as well as summ...

Create a column upon insertion or overwriting of data.
Hello, What should I read up on, where to look, what is the function or feature called, to learn how Excel spreadsheet be configured so that upon insertion or overwriting data in a cell, a new cell will be populated with the results that display relative difference and a percentage of change ? Peter Hi Peter, You should look at the Worksheet_Change event. If you are not familiar with macros, you may wish to visit David McRitchie's 'Getting Started With Macros And User Defined Functions' at: http://www.mvps.org/dmcritchie/excel/getstarted.htm For information on event ...

How to get notified when user inserts cells, rows, or columns in W
I'm making a client to Excel that keeps references to cells on worksheets. These references must be updated if the user inserts cells to the left or above the referenced cells. I have not been able to find this information, neither in the Range that accompanies the Change notification nor in any of the Worksheet, Workbook, or Application properties. Can anybody help me? Using the Worksheet_Change event, you could probably evaluate the row and column values of the target cell against the criteria cell: If Target <> (Reference Cell) Then If Target.Row <=( Refere...

automatically copying data from one sheet to another, depending on which drop-down code is allocated
I have a sheet where I list each item of my business outgoings. Column C lists the amount spent. In Column D I allocate a code from a drop-down list, to allocate that item of expenditure against a particular cost centre. I have thirty cost centres. So once the code is allocated, I can filter the list to see all items for any particular cost centre. But what I really want is this: I have created a separate sheet in the workbook, with all the cost centres as column headings. I'd like each amount to be automatically copied to the cost centre sheet, and placed in the correct column,...

number of columns in a customized Form
Hi, We need some times more than 3 columns in a row on a customized form, is this possible? has any one tried this out? any hint is highly appreciated. Regards not possible in the standard forms and not heard of any solution to increase the capability. ======================= John O'Donnell Microsoft CRM MVP http://codegallery.gotdotnet.com/crm "Max.f FirstCity" <max.f@first-city.com> wrote in message news:e%23oXeStbGHA.1856@TK2MSFTNGP03.phx.gbl... > Hi, > We need some times more than 3 columns in a row on a customized form, is > this possible? has any ...

Hide pg1 row1 or suppress pg1 header?
I am running Excel 2002 and need to either hide the first row of m spreadsheet on the print out (it contains column headers) or I need t suppress the header on only the first page of my print or perhaps ther is a way to show the headers in the "normal view" of the spreadshee while in edit mode? Can someone please assist me -- Message posted from http://www.ExcelForum.com ...

dates change when I copy and paste
I have a report that has columns for Day of week and a column for th date(10/15/04) but when I copy and paste to another excel worksheet th date changes from ex: 10/15/04 to 10/13/08. Any ideas why this woul happen. It's a pretty lengthy report and I hate to always have t change each date. Help. : -- Nesi ----------------------------------------------------------------------- Nesia's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1534 View this thread: http://www.excelforum.com/showthread.php?threadid=26965 Hi both workbooks have different date setting (...

Internet header question
Does anyone know of any tool that could be ran against an outlook folder full of internet mail that would extract the SMTP address sent from for all of them and export it to any format? I'm going through spam from various users, and between the firewall and the antivirus software on my exchange server I want to add the IP's to a block list. But it's a pain opening them one at a time, getting to options, copying the IP, and pasting it into an excel spreadsheet. Thanks for any suggestions. John JDTHREE [MVP] <john@removeforspam.engagenet.com> wrote: > I'm going th...

NAME BOX
How do I delete a cell reference that is in my mame box?? thanks, larry Hi Larry one way: goto 'Insert - Name - Define' and delete the name in the appearing dialog -- Regards Frank Kabel Frankfurt, Germany FLKulchar wrote: > How do I delete a cell reference that is in my mame box?? > > thanks, > > larry Larry, Go to the Insert menu, choose Name, the Define. In that dialog, select the name and click Delete. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "FLKulchar" <flk2575@comcast.net> wrote...

sum using or (if Blank cell in Column)
My Basic problem seems to be that i cannot run a variable against a blank cell. My set up as below: Item 50cl 70cl 1.5cl per 25ml (< answer i'm looking for) Rum £17.50 Cheese £80.35 Milk £10.50 Now individually i can work out these items. Rum would be 0.62 in the 25ml column... but I cannot find how to create each sum in the total box, so when i leave two of them blank it understand to only work out the one answer. It must look something like this? Right? Item 50cl 70cl 1.5cl per 25ml Rum £17.50 =s...