populate data from multiple tabs with diff columns

[myexcellent123]

hello! sorry- I am in a rush at work! if anyone could PLEASE help me!!

so, I have a list of Unique IDs in column A in my MASTER tab. Then have columns from B- DY. I essentially need to populate this MASTER tab by extracting data from the 6 tabs, in the same workbook, after this MASTER tab.

The 6 tabs have columns in different order and are (currently) also labelled differently from the header in the MASTER tab. I have a row 1 going across my master tab that lists the tab on which that data can be found.

so essentialy, i am looking for a macro that will look at the Unique ID in column A, then up to Row 1, and the tab name that is listed there- the macro will refer to that specific tab, find that unique ID in column A- go across to the column (that CAN/WILL BE be named the same but in different order) and populate that cells data.

I have data going down to 1300 rows- for upload puposes..to make the file smaller- i only listed till row 10

 

[myexcellent123]

so essentialy, i am looking for a macro that will:

1. MASTER TAB: look at the Unique ID in column A, then up to Row 1, and the tab name that is listed there
2. The macro will refer to that specific tab, (in the listed tab) find that unique ID in column A- go across to the column (that CAN/WILL BE be named the same but in different order)
3. and populate that cells data into the MASTER tab.

 

[iferror]

I'm not 100% sure i got it but this is how i would do:

For each sheet create 3 named ranges
(i.e. for sheet lte1 create
lte1_h for the headers
lte1_i for uniqueids
lte1_d for the data
)

and then use the magic combination of index/match (or vlookup/match) with a hint of indirect to get the correct named range, something in the line of

=INDEX(INDIRECT(B$1 & "_d");MATCH($A5;INDIRECT(B$1 & "_i");0);MATCH(B$2;INDIRECT(B$1 & "_h");0))

NON EU VERSION
=INDEX(INDIRECT(B$1 & "_d"),MATCH($A5,INDIRECT(B$1 & "_i"),0),MATCH(B$2,INDIRECT(B$1 & "_h"),0))

 

[myexcellent123]

Thank you so much, iferror!!! I will try that but I would really love to get a macro to run this and avoid having 1200x129 formulas in this file! i just feel like it will break my computer!

 

[iferror]

Not something you can solve in spare time while working and weekend approaching

But this is a nice starting point (not tested, not commented, not checked for unused declared variabled)

Sub Draft()


Dim mstSht As Worksheet
Dim actSht As Worksheet
Dim mstidRng As Range
Dim mstHRng As Range

Set mstSht = Worksheets("Master")
Set mstidRng = mstSht.Range("A4", Range("A" & Rows.Count).End(xlUp))
Set mstHRng = mstSht.Range("AB3", Range("AB3").End(xlToRight))


For Each cl In mstidRng
  For Each cll In mstHRng
  actshname = cll.Offset(-2, 0).Value
  Set actSht = Worksheets(actshname)
  
  Set tmpRngC = actSht.Cells.Find(What:=cll.Value, After:= _
  ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  
  Set tmpRngR = actSht.Cells.Find(What:=cl.Value, After:= _
  ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
  SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
  If Not (tmpRngC Is Nothing) And Not (tmpRngC Is Nothing) Then
  vv = actSht.Cells(tmpRngR.Row, tmpRngC.Column).Value
  Else
  vv = "Not Found"
  End If
  
  mstSht.Cells(cl.Row, cll.Column).Value = vv
  
  Next
Next

End Sub

 

[iferror]

I just noticed that you have
ANTSS Mapping Forecast
in the master, but in lte3 you have sligthly different column headers
PS01 Antenna Mapping Forecast
and
Revised PS01 Antenna Mapping Forecast
is not going to work

 

[SirJB7]

Hi, myexcellent123!

Are you very hurried? If so please take a break and dedicate 5 minutes to what follows. Posting without adding any useful information just for bumping up a topic doesn't guarantee neither a faster assistance nor getting the interest of people who might be reading that post, but rather the opposite effect of discouraging them.

If you'd have read the 1st forum at the main page...
http://chandoo.org/forum/forums/new-users-please-start-here.14/
...you should have noticed this points (and if you did it seems as if you should do it again):

"Consider that the world is operating 24hrs a day. A late post today may well be answered by someone else overnight."

"If you and a reader have been involved in an ongoing conversation and the conversation suddenly stops, recognize that the person may have gone to bed, even though you have just arrived at work. In the worst case a reader may go on holidays and not get back to the question for a few days."

"Never title your posts as "Urgent", "Priority" "Immediate". It may be Important to you, but not for rest of the members here. These words will be moderated out."

"Say "Thanks", whenever you can. Recognize when someone has bothered to go to the trouble and time to assist you with your question for free. Often readers will spend several hours working on a solution to a problem, a line of recognition will go a long way."

Regards!

PS: If it's a high priority issue and you can't even wait... how much?... oh, a few hours or a couple of days... absolutely unbearable... but you always have the alternative of hiring a local consultant who may provide you the exact tailored service. Should I remember you that these forums are supported by contributors who dedicate part of their time in an absolutely free way to help other people and answer questions?

 

[myexcellent123]

Hi Iferror- thank you so much for your response!

thanks SirJB7. Duly noted!