Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-08-2017, 08:38 AM
alistair_ alistair_ is offline Summarise information from multiple worksheets in multiple files Windows XP Summarise information from multiple worksheets in multiple files Office 2016
Novice
Summarise information from multiple worksheets in multiple files
 
Join Date: Apr 2017
Posts: 2
alistair_ is on a distinguished road
Default Summarise information from multiple worksheets in multiple files

Hi All,

I'm a novice with Excel and new to the forum so I apologise if the post isn't clear.



I have a series of excel files in one folder. Each excel file contains information about a different material property i.e. unit weight, specific gravity etc. The material property is the file name.

Each file has a series of worksheets which contains information for different material types i.e. material 1, material 2 etc. The material name is the tab name. Within these worksheets I have calcuated the minimum, maximum, mean and standard deviation and these are always shown in the same cell.

I would like to use a VBA script in excel, so that I select the folder location and excel opens each individual file within the folder, record the file name, and for each tab, records the tab name summarise the information shown in these cells A3&4, B3&4, C3&4, & D3&4 (note cell 3 will be text and cell 4 an number). It would ideally loop through each file in folder and summarise the information into one excel table.

I was wondering whether anybody could help me out with a code to show a table like that below.

Any help would be greatly appreciated

Thanks


File Name i.e. Unit Weight
Tab 1 Name - Cell A3 - Cell A4
Tab 1 Name - Cell B3 - Cell B4
Tab 1 Name - Cell C3 - Cell C4
Tab 1 Name - Cell D3 - Cell D4

Tab 2 Name - Cell A3 - Cell A4
Tab 2 Name - Cell B3 - Cell B4
Tab 2 Name - Cell C3 - Cell C4
Tab 2 Name - Cell D3 - Cell D4

File Name i.e. Specific Gravity
Tab 1 Name - Cell A3 - Cell A4
Tab 1 Name - Cell B3 - Cell B4
Tab 1 Name - Cell C3 - Cell C4
Tab 1 Name - Cell D3 - Cell D4

Tab 2 Name - Cell A3 - Cell A4
Tab 2 Name - Cell B3 - Cell B4
Tab 2 Name - Cell C3 - Cell C4
Tab 2 Name - Cell D3 - Cell D4
Reply With Quote
  #2  
Old 04-08-2017, 11:26 PM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Summarise information from multiple worksheets in multiple files Windows 7 64bit Summarise information from multiple worksheets in multiple files Office 2010 64bit
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

Hi
this thread has been marked as " solved". Is this correct? There do not seem to be any answers?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #3  
Old 04-09-2017, 03:10 AM
alistair_ alistair_ is offline Summarise information from multiple worksheets in multiple files Windows XP Summarise information from multiple worksheets in multiple files Office 2016
Novice
Summarise information from multiple worksheets in multiple files
 
Join Date: Apr 2017
Posts: 2
alistair_ is on a distinguished road
Default

Hi Pecoflyer,

No it hasn't been solved yet. Not sure where the solved icon came from. Do you know if I can remove it?

Thanks
Reply With Quote
  #4  
Old 04-09-2017, 08:32 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Summarise information from multiple worksheets in multiple files Windows 7 64bit Summarise information from multiple worksheets in multiple files Office 2010 64bit
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

It's under "thread tools". I did it for you. Cheers
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #5  
Old 04-09-2017, 08:34 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Summarise information from multiple worksheets in multiple files Windows 7 64bit Summarise information from multiple worksheets in multiple files Office 2010 64bit
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

To solve your problem perhaps
http://www.rondebruin.nl/win/addins/rdbmerge.htm
or
http://www.rondebruin.nl/win/section3.htm
might help?
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post
Reply With Quote
  #6  
Old 04-10-2017, 08:27 AM
NoSparks NoSparks is offline Summarise information from multiple worksheets in multiple files Windows 7 64bit Summarise information from multiple worksheets in multiple files Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 831
NoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really niceNoSparks is just really nice
Default

Adapted from an example at The Spreadsheet Guru for looping through files in a chosen folder.
Maybe you can make use of something like this.
Attached Files
File Type: xlsm Info Summary.xlsm (22.9 KB, 11 views)
Reply With Quote
Reply

Tags
excel vba, vba script

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Summarise information from multiple worksheets in multiple files Macro to open Multiple files and copy information to a master file gbaker Excel Programming 2 04-08-2016 08:44 AM
Combine or merge multiple worksheets into one worksheet timomaha Excel 1 07-21-2014 01:02 PM
Summarise information from multiple worksheets in multiple files Multiple Dynamic Worksheets Consolidated into One Static Worksheet mars1886 Excel Programming 3 02-09-2014 12:50 AM
Summarise information from multiple worksheets in multiple files Sum multiple worksheets and summary tab that would sum and count by rep name angie.chang Excel 1 05-16-2013 12:36 AM
Summarise information from multiple worksheets in multiple files convert multiple csv files to multiple excel files mit Excel 1 06-14-2011 10:15 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:47 AM.


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