VBA Loop to bring over data from multiple sheets using header and column data

[MHamid]

Hello,

I have been trying to figure out the best way to accomplish this task.
I need to bring over data from multiple sheets (4 sheets to be exact), into the main sheet using headers as a point of reference for the data that I need brought over but it also has to match with the account number. I can't upload a mini sheet or use XL2BB add-in since it's company data. I could try to send dummy data as an example later today, but want to know if it's possible and how would you approach this scenario.

There are 5 Sheets total:

1. Main
2. Source1
3. Source2
4. Source3
5. Source4
6. Source5

When bringing over the data from the Source sheets. Source1 takes priority, then Source2, then Source3, then Source4, and then Source5. VBA should look for account number from Main sheet in the Source1 sheet first and if account number is not in Source1, then it would look in Source2, and so forth bringing over data based on headers. If header from main sheet does not exists in Source1 or Source2 or so forth, then leave blank

Main Sheet:

Account Number	Audit Name	Region	Cluster	Country	Business L1 (Home)	Business L2 (Home)
A123456
A456789
A789123

Source1:

Account Number	Account Name	Region	Cluster	Country	Business L1 (Home)	Business L2 (Home)
A123456	Acct Name1	EMEA	EMEA EM;Turkey, Russia, Ukraine, and Kazakhstan (TRUK)	Turkey(TR)	Business 1	Business2
A741852	Acct name 2	North America	North America	US(US)	Business 3	Business 4
A852963	acct name 3	North America	North America	US(US)	Business 5	Business

Source2:

Audit_ID	Account Name	Country	Region
A456789	name1	Japan(JP)	Japan
A852741	name2	US(US)	NAM
A753412	Singapore Interbank Offered Rate (SIBOR)	Singapore(SG)	Asia Pacific

Source3:

Audit_ID	Audit Name	Country	Region	Cluster
A789123	name6	Country list	ashfgajkfg	Cluster list
A777777	name7	US(US)	NAM	North America
A444444	name8	ahgsfjagh	afgajgf	akjshfkajh

Source4:

Account Number	Account_Name
A888888	adfgasdfaljhklasfjh
A555555	ajfhgajkgfkasfh

Source5:

Audit Shell ID	Audit Name	Country (Home)	Region (Home)	Cluster
A333333	name1	LATAM Hub(X7)	LATAM
A666666	name2	US(US)	NAM	North America
A858585	name3	Vietnam(VN)	Asia Pacific	Asean

Please let me know if I need to clarify anything.


Thank you,

 

[MHamid]

anyone has any advice or need any further information?

 

[Tupe77]

5. Where and how often you get the data to/for sheet1-5
5.1 Can't influence the structure of the data available? (column headings and order?)
5.2 How many rows can there be on each sheet?


If you can't influence the structure ...

1. Do you want to fill the Main sheet according to the Account number already in it?

2. Information to be filled can be found in Source 1,2,3,4,5 sheets?

3. Search in order of sheet number?

4.Is the data in a table object or just a sheet?

6. Can the same Account Number be found on more than one sheet?

7. Can the same Account Number get data from more than one sheet to same column of the Main Sheet?

8. Can different sheets give different data for same cell in Main-sheet? -> Sheet3 data is overwritten by data received from Sheet1?

 

[MHamid]

5. Where and how often you get the data to/for sheet1-5
5.1 Can't influence the structure of the data available? (column headings and order?)
5.2 How many rows can there be on each sheet?


If you can't influence the structure ...

1. Do you want to fill the Main sheet according to the Account number already in it?

2. Information to be filled can be found in Source 1,2,3,4,5 sheets?

3. Search in order of sheet number?

4.Is the data in a table object or just a sheet?

6. Can the same Account Number be found on more than one sheet?

7. Can the same Account Number get data from more than one sheet to same column of the Main Sheet?

8. Can different sheets give different data for same cell in Main-sheet? -> Sheet3 data is overwritten by data received from Sheet1?

Hello,

Source1 - we can call this 'MonthlyRefresh'
Source2 - 'WeeklyRefresh1'
Source3 - 'WeeklyRefresh2'
Source4 - 'ManuallyAddedItems'
Source5 -'PreliminaryData'

5. An outside source provides data for sheets 1-3. Sheet1 is received monthly while sheets 2 and 3 are received weekly. Sheet4 is for data that we receive sporadically and have to be manually added into sheet4 (this will be weekly, but only if we receive any data, if not it's fine). Sheet 5 is preliminary data from the beginning of the year (this sheet data never changes throughout the year).
5.1 Not sure what you mean by influence the structure, but I think the columns header names can be used to bring over data since they will most likely be the same (or I can make the names match).
5.2 Row count varies, but it is definitely over 850 rows of data ... sometimes it can go over 900 or 1000 rows of data. But the account number column can be used from the main sheet to determine the row count.


if can't influence the structure ...
1. Yes, the account number from the main sheet should be used to find the corresponding data for that account number
2. Yes, the information is found in sheets 1-5
3. Yes, the Source1 will always be first and if account number is not in Source1, then search for account number in Source2. If account number is not found in Source2, then search in Source3. If not found, then search in Source4. If not found then search in Source5.
4. No table, just a sheet
6. Yes, the same number can be found on more than one sheet, but please use the order I mentioned in #3
7. Even if account number exists in other sheets, the data brought over should be entered based on the priority level in #3. So if an account number is found in both Source1 and Source2, I just want the data from Source1 brought over.
8. Different sheet may have different data, but Source1 data will always be the most up-to-date data, which is why this data should be brought over first and never be overwritten.


Please let me know if you have further questions and what your thoughts are to make this happen.


Thank you,

 

[MHamid]

Hello,

Anyone has any advise with what I have provided so far? Does anyone need more information to assist?

Thank you,