Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 11-24-2021, 02:14 PM
XDAnalyst XDAnalyst is offline Excel: Merging specific worksheets from different workbooks into a master file Windows 10 Excel: Merging specific worksheets from different workbooks into a master file Office 2019
Novice
Excel: Merging specific worksheets from different workbooks into a master file
 
Join Date: Nov 2021
Posts: 1
XDAnalyst is on a distinguished road
Exclamation Excel: Merging specific worksheets from different workbooks into a master file

Hi all,



I'm just starting with VBA, so I would like some help with this task at hand.

I have four Excel files named "C Tier 4", "L Tier 4", "S Tier 4", and "O Tier 4". Within each of these workbooks, there's a tab called "Data" containing information that I need to combine and extract to a master file (another file). Unfortunately, one of the columns in "O Tier 4" is incorrectly placed, so I need to make sure the workbook columns match the name of the master file columns. Also, since I already have the headers on the master file, I would have to combine the data starting on cell A4.

How would I go about creating a button with VBA code to allow me to do that?

Thanks in advance,

XD
Reply With Quote
  #2  
Old 11-24-2021, 11:20 PM
ArviLaanemets ArviLaanemets is offline Excel: Merging specific worksheets from different workbooks into a master file Windows 8 Excel: Merging specific worksheets from different workbooks into a master file Office 2016
Expert
 
Join Date: May 2017
Posts: 869
ArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud ofArviLaanemets has much to be proud of
Default

Easiest way (and for main part no VBA is needed):

Into your Master workbook, add a hidden sheet. Now create 4 ODBC queries with dataranges in this hidden sheet - firstmost one placed at top and having headers returned. The rest of queries dataranges must be placed below it, without header row, and for every datarange you have to reserve enough of records (any number of empty records don't affect the end result in any way - so you can be generous with this). All gueries must return same fields in same order - so when source tables have different column orders, you have to adjust queries accordingly. All 4 queries must be set to be updated on open, and must overwrite existing rows and clear abundant ones - i.e. deleting is NO!

Then on hidden sheet you define a fixed named range covering all query dataranges + reserved empty rows on hidden sheet. And on sheet where you want to display consolidated data, you create an ODBC querie based on named range on hidden sheet, with WHERE condition excluding all empty rows. And set the query to be refreshed on open.

Optional part:
There may be a problem with queries - after some version of MS Access I enconutered a problem, where the query worked when created, but didn't work when workbook was opened next time. To get it to work again, I had to edit the datasource - then the query worked OK until next session (I suspect this may be because in our company we have 64-bit Windows in all computers, but 32-bit MS Office). As solution, I did write an Open event for such files, which edited all datasources of queries to Excel workbooks in this workbook. Additionally the event also checks the location of workbook, and in case the source of query is in same workbook and the location was changed, the event edits this too. So user can move the workbook to other location, and all queries will work.
Reply With Quote
  #3  
Old 11-25-2021, 12:39 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Excel: Merging specific worksheets from different workbooks into a master file Windows 7 64bit Excel: Merging specific worksheets from different workbooks into a master file Office 2010
Expert
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,766
Pecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant futurePecoflyer has a brilliant future
Default

Cross posted at Excel: Merging specific worksheets from different workbooks into a master file | MrExcel Message Board
and at
Excel: Merging specific worksheets from different workbooks into a master file


Please read Excelguru Help Site - A message to forum cross posters
Reply With Quote
  #4  
Old 11-25-2021, 03:26 PM
p45cal's Avatar
p45cal p45cal is offline Excel: Merging specific worksheets from different workbooks into a master file Windows 10 Excel: Merging specific worksheets from different workbooks into a master file Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

It looks very likely that Power Query (aka Get & Transform Data in Office 2019) could fetch this data for you very easily; especially that the tab name is constant. Are these data in the source files in tables (proper Excel table as in Insert|Table)? The fact that headers are in different orders in the source files is neither here nor there, and you'll be able to bring in the data according to the column headers in your receiving file.


If you could zip up some realistic mock-ups of those four files (each with only a little data in, and of course, de-sensitised), along with a 'Master' receiving workbook and attach the zip file here I'd expect to be able to provide a solution only requiring a refresh (like you do a pivot table) and no macro/vba code, to update with new data.


As an aside, how's that data going to be used once it arrives in the Master workbook?
Reply With Quote
Reply

Tags
macros, vba, worksheet

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel: Merging specific worksheets from different workbooks into a master file How to copy workbooks from different worksheets into one new worksheet abbani Excel 3 12-12-2016 04:09 AM
Excel: Merging specific worksheets from different workbooks into a master file Merging several worksheets into one exel file Alaska1 Excel 4 08-04-2015 08:20 AM
Linking Workbooks/Entire Worksheets to Identical in Master taxacct Excel 2 10-01-2014 11:22 AM
PLEASE HELP in merging workbooks 07 Micheleg Excel 3 05-19-2014 07:52 AM
VBA: Creating new Worksheets from two different Workbooks Johana Excel Programming 1 09-07-2012 09:30 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 05:00 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft