Macro copy first value from multiple header rows to new sheet based on header

C

clazzic

New Member
Joined
May 6, 2013
Messages
26
Hi, ive got a complication with pulling certain data based on header columns, my Data sample set looks like below.

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]header1[/TD]
[TD="class: xl65, width: 64"]header2[/TD]
[TD="class: xl65, width: 64"]header3[/TD]
[TD="class: xl65, width: 64"]header4[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]54[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]23[/TD]
[TD="class: xl65, align: right"]56[/TD]
[TD="class: xl65, align: right"]54[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]54[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]header1[/TD]
[TD="class: xl65"]header4[/TD]
[TD="class: xl65"]header 3[/TD]
[TD="class: xl65"]header2[/TD]
[TD="class: xl65"]header5[/TD]
[TD="class: xl65"]header6[/TD]
[TD="class: xl65"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]54[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]12[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65"] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65"]header1[/TD]
[TD="class: xl65"]header2[/TD]
[TD="class: xl65"]header 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]3[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]4[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first Header row in the sourcesheet "Sheet1" will be in the 3rd row and there will be 100s of data sets that varies in length but will be seperated by a blank row.

The targetsheet "Sheet2" will have the the headers that i want data for, it will be matching with the headers in sourceshseet. e.g. header1,header2,header3

What i need is for it to loop through each header row for each data set in sourcesheet, and pull the first value of that data set to the targetsheet under the Target header row (row 1)

desired outcome:
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]header1[/TD]
[TD="class: xl65, width: 64"]header2[/TD]
[TD="class: xl65, width: 64"]header3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]1[/TD]
[TD="class: xl65, align: right"]6[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]2[/TD]
[TD="class: xl65, align: right"]5[/TD]
[TD="class: xl65, align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


Thanks guys,
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Sort by date Sort by votes
Try this for results on sheet2.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG01Aug27
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray()
Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants) '[COLOR="Green"][B][/B][/COLOR]
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    c = c + 1
    ReDim Preserve Ray(1 To 3, 1 To c)
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 3
        [COLOR="Navy"]If[/COLOR] c = 2 [COLOR="Navy"]Then[/COLOR] Ray(Ac, 1) = Dn(1).Offset(, Ac - 1).Value
        Ray(Ac, UBound(Ray, 2)) = Dn(2).Offset(, Ac - 1).Value
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 2), 3)
  .Value = Application.Transpose(Ray)
  .Columns.AutoFit
  .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
MickG said:
Try this for results on sheet2.
Code: 
[COLOR=Navy]Sub[/COLOR] MG01Aug27
[COLOR=Navy]Dim[/COLOR] Rng [COLOR=Navy]As[/COLOR] Range, Dn [COLOR=Navy]As[/COLOR] Range, Ac [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long,[/COLOR] Ray()
Set Rng = Range("A:A").SpecialCells(xlCellTypeConstants) '
c = 1
[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng.Areas
    c = c + 1
    ReDim Preserve Ray(1 To 3, 1 To c)
    [COLOR=Navy]For[/COLOR] Ac = 1 To 3
        [COLOR=Navy]If[/COLOR] c = 2 [COLOR=Navy]Then[/COLOR] Ray(Ac, 1) = Dn(1).Offset(, Ac - 1).Value
        Ray(Ac, UBound(Ray, 2)) = Dn(2).Offset(, Ac - 1).Value
    [COLOR=Navy]Next[/COLOR] Ac
[COLOR=Navy]Next[/COLOR] Dn
[COLOR=Navy]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 2), 3)
  .Value = Application.Transpose(Ray)
  .Columns.AutoFit
  .Borders.Weight = 2
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]
Regards Mick
Click to expand...

Thanks for the quick response Mick,

the code didnt return any of the values under the data set headers in sheet 1,
it only resizes and add borders. which part of the code reference the data set headers in the sourcesheet(sheet1)?
 
Upvote 0
thank Mick it work,

the function's there, but it keeps replacing the headers title that i put in Sheet 2, with the first few in sheet 1.

my actual data has approx 40 column header, and the one i want are only 3 (for now) position in different columns.

regards
Tony
 
Upvote 0
If you want to remove the headers in the results, Remove the line in Red.
The results will still show the first 3 columns in each Area.
If you want to replace the headers or number of columns in the results, you will need to specify some sort of criteria.
Code: 
 [COLOR=navy]For[/COLOR] Ac = 1 To 3
        [B][COLOR=#FF0000]If c = 2 Then Ray(Ac, 1) = Dn(1).Offset(, Ac - 1).Value[/COLOR][/B]
        Ray(Ac, UBound(Ray, 2)) = Dn(2).Offset(, Ac - 1).Value
    [COLOR=navy]Next[/COLOR] Ac
 
Upvote 0
yeah i realised that it only pulls the values in the first 3 columns,

if the my scenario is that the columns values i need are in columns e.g. col10, col 15, col30,

how would i get the values under all the source headers for each data set (sheet1) that matches the target headers (sheet2)?
 
Upvote 0
Perhaps this:-
Code: 
[COLOR="Navy"]Sub[/COLOR] MG02Aug51
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ray(), col [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Set Rng = Range("J:J").SpecialCells(xlCellTypeConstants) '[COLOR="Green"][B][/B][/COLOR]
c = 1
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    c = c + 1
    ReDim Preserve Ray(1 To 3, 1 To c)
    [COLOR="Navy"]For[/COLOR] Ac = 1 To 3
        [COLOR="Navy"]Select[/COLOR] [COLOR="Navy"]Case[/COLOR] Ac
            [COLOR="Navy"]Case[/COLOR] 1: col = 0
            [COLOR="Navy"]Case[/COLOR] 2: col = 5
            [COLOR="Navy"]Case[/COLOR] 3: col = 20
        [COLOR="Navy"]End[/COLOR] Select
        If c = 2 Then Ray(Ac, 1) = Dn(1).Offset(, col).Value '[COLOR="Green"][B]remove this line if Headers not required !!![/B][/COLOR]
        Ray(Ac, UBound(Ray, 2)) = Dn(2).Offset(, col).Value
       
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(UBound(Ray, 2), 3)
  .Value = Application.Transpose(Ray)
  .Columns.AutoFit
  .Borders.Weight = 2
[COLOR="Navy"]End[/COLOR] With
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Solved
Excel 2016 VBA - Pull data from multiple sheets to One with specificities
Replies
3
Views
426
mumps
mumps
N
  • Question Question
AutoFilter Field Macro Returns a Header Row only
2
Replies
11
Views
221
MARK858
MARK858
JenniferMurphy
  • Question Question
Is this a good solution to Excel's table header weaknesses?
Replies
3
Views
314
zero269
Z
H
  • Solved
Automatic copy new rows in sheet. Shooter contest.
Replies
8
Views
244
NdNoviceHlp
NdNoviceHlp
A
  • Solved
Count multiples rows and ans in a single row and column
2
Replies
13
Views
177
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,224,823
Messages
6,181,177
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top