Copy column info based on column header

[slice_of_pineapple]

Basically what i want to do is copy the date below a column header to another sheet in the same workbook based on the header. This will be used by multiple people and each report will have different column arrangements. I will have three sheets within a workbook. Sheet 1 will be called ''paste report here''. Sheet 2 will be called ''run'' as i will have a macro running to collect data. Sheet 3 will be called ''totals''. I'm a novice and know a little about macro's but not enough to located data by the header and copy and paste using a formula. I don't know vba at all so code won't work for me. Is there a formula i can use? I've seen that index match can do what i want but i cant get my head around it. example below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Due Date[/TD]
[TD]Style[/TD]
[TD]Qty[/TD]
[TD]Routing[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]21/02/16
[/TD]
[TD]t1000[/TD]
[TD]1000[/TD]
[TD]print[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]25/02/16[/TD]
[TD]t1111[/TD]
[TD]5000
[/TD]
[TD]tag[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]30/03/16[/TD]
[TD]t1526[/TD]
[TD]500[/TD]
[TD]no prep[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]01/09/16[/TD]
[TD]t5252[/TD]
[TD]120[/TD]
[TD]print[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

What i want to do is for excel to realise that all the info in ''due date'' is needed and to copy and paste all data in that column, including the header, into another sheet. The reason being is that ''due date'' will not always be in column 1. sometimes it may be column 15. So i want excel to grab the column based on the title. Hope this makes sense. I'm really stuck.

 

[Fishboy]

Basically what i want to do is copy the date below a column header to another sheet in the same workbook based on the header. This will be used by multiple people and each report will have different column arrangements. I will have three sheets within a workbook. Sheet 1 will be called ''paste report here''. Sheet 2 will be called ''run'' as i will have a macro running to collect data. Sheet 3 will be called ''totals''. I'm a novice and know a little about macro's but not enough to located data by the header and copy and paste using a formula. I don't know vba at all so code won't work for me. Is there a formula i can use? I've seen that index match can do what i want but i cant get my head around it. example below.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Due Date[/TD]
[TD]Style[/TD]
[TD]Qty[/TD]
[TD]Routing[/TD]
[TD]Critical[/TD]
[/TR]
[TR]
[TD]21/02/16[/TD]
[TD]t1000[/TD]
[TD]1000[/TD]
[TD]print[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD]25/02/16[/TD]
[TD]t1111[/TD]
[TD]5000[/TD]
[TD]tag[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]30/03/16[/TD]
[TD]t1526[/TD]
[TD]500[/TD]
[TD]no prep[/TD]
[TD]no[/TD]
[/TR]
[TR]
[TD]01/09/16[/TD]
[TD]t5252[/TD]
[TD]120[/TD]
[TD]print[/TD]
[TD]no[/TD]
[/TR]
</tbody>[/TABLE]

What i want to do is for excel to realise that all the info in ''due date'' is needed and to copy and paste all data in that column, including the header, into another sheet. The reason being is that ''due date'' will not always be in column 1. sometimes it may be column 15. So i want excel to grab the column based on the title. Hope this makes sense. I'm really stuck.

Hi slice_of_pineapple,

I am not sure how to achieve this with only a formula, but hopefully this relatively simple macro (with added comments explaining what each line is supposed to be doing) should do the trick for you. If I have misunderstood what data is on which sheet, simply update the bold red sheet names in my code. Also as you did not specify a location I have selected to paste the data into cell A1 on the Paste Report Here sheet. If you want it elsewhere update the bold blue A1 reference to your desired cell reference.

To try out this code first make a COPY of your workbook to test in. Next press Alt+F11 to open the VBA developer window. Find the name of your workbook in the project panel on the left hand side, then right-click on ThisWorkbook and select Insert Module. In the new window that opens simply copy and paste in my code. The macro can be run by either applying it to a button or by pressing Alt+F8 from the main Excel window and selecting the CopyDueDates macro from the list:

Sub CopyDueDates()
' Defines variables
Dim Cell As Range, sRange As Range, Rng As Range
    ' Defines LastCol as the last column with a header on the Run sheet
    LastCol = Sheets("Run").Cells(1, Columns.Count).End(xlToLeft).Column
        ' Sets the search range as A1 to the last column with a header on the Run sheet
        Set sRange = Sheets("Run").Range("A1", Cells(1, LastCol))
            ' With the search range
            With sRange
                ' Set Rng as the cell where "Due Date" is found
                Set Rng = .Find(What:="Due Date", _
                                After:=.Cells(1), _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlPrevious, _
                                MatchCase:=False)
                    ' If Rng exists then
                    If Not Rng Is Nothing Then
                        ' Define LastRow as the last row of data under the Due Date header
                        LastRow = Sheets("Run").Cells(Rows.Count, Rng.Column).End(xlUp).Row
                            ' Copy from the Due Date header down to the last row of that column and paste to A1 of Paste Report Here sheet
                            Sheets("Run").Range(Rng, Cells(LastRow, Rng.Column)).Copy _
                                Destination:=Sheets("paste report here").Range("A1")
                    End If
            End With
End Sub

 

[jcfuchser]

This works great. However I'm curious if I can do this with a twist. I am trying to do this function but add the cells content if another cell has content.

So I'm looking for a column in Sheet 1 that says "Word" and I want to copy it from Sheet 1 to Sheet 2 but I only want its contents if the column that says "Adobe" has content, if does not it displays a message like "Needs complete".

I hope this is clear. Any advice would be greatly appreciated.

Thank you for you time