Best Practice - Multiple Workbooks?

Jun 28, 2006

Task - Streamline & Automate daily Excel Tasks for 13 Staff & Manager, based in 2 different locations. Current Status. 25 workbooks, "Shared", + 1 Workbook {List.xls}, "Not Shared". All Validation lists are in this List book. 1 main data entry sheet in each workbook, feeding totals on Main Tab. Each data entry sheet is 10 columns, 250-700 rows. 5 Colums are free text entry, 5 are fed from data validation lists. The data validation lists are on another Tab, which is linked to a non shared workbook.

Each workbook has a Main Tab, which totals data input, and represents data in colours, in different cells..Picture a warehouse, with shelves & racks. I am in the middle of designing a simple form...but not so simple..thats another story. I am concerned that there is a lot of potential for disater, especially with shared books. I have thought of the access route, but the staff need to see colours on the sheets. Any opinions on what the Best Practice may be to handle this scenario. My main forte has been formula, pivot tables & external data..this is a new challenge

View 3 Replies


ADVERTISEMENT

Practice Data

Feb 24, 2010

Does anyone know where I can get a large amount of generic data that I could import? I don't want my skills to get rusty while I am out of work. I keep finding little tables here and there, but I am looking for a big file that I can really play with. Maybe something with 20 or 30 columns and several hundred rows?

View 9 Replies View Related

Application.EnableEvents Best Practice

Dec 2, 2008

Am using a number of checks on Worksheet_Change and Worksheet_Calculate events and, depending on the result of the change, resetting a cell value.

For each of these I am using

Application.EnableEvents = False
Range("A1").Value = 0
Application.EnableEvents = True

to avoid that the change of A1's value triggers a calculation event and gets me into a loop.

However, something in my project is setting EnableEvents to False and not resetting it. I've searched the entire project for all instances and each "=False" is swiftly followed by a "=True". The code is not causing an error

The only way to get around this is to have a user press a button to re-enable events, but I can't tell them to press the button because there are no events to trigger the message!

View 9 Replies View Related

Linking Data - Best Practice

May 13, 2006

I have yet another general design or best practice question regarding linking data between sheets in a workbook.

So the example scenario is we have 12 individual customer pages shown amounts invoiced and credited for the financial year.

I want to create an overall summary page.

Is it best to:
1. Use straight link eg. =Data!A1
2. Use Vlookup
3. Use Indirect with Address function
4. Use Database functions

I believe providing you have enough available memory that there isn't an issue with any of the above methods.

View 3 Replies View Related

Best Practice Spreadsheet Design

Aug 10, 2007

I have been tasked with performing a work measurement study to improve productivity in our organisation. I managed to adapt a paper based report to an excel file; with the view of creating a model. I also intend adding the cost of employing people in specific positions to determine the cost per hour for each specific activity.

View 2 Replies View Related

Best Practice Worksheet Struture

Aug 11, 2008

I am trying to reorganizing my database. Therefore data i like to restructure my current data (sheet 1) into a similar format of sheet 2. Is there a way to automate this process?

View 3 Replies View Related

Formatting Cells To Date - Best Practice

Aug 7, 2006

I thought I read somewhere that it wasn't good practice to format a whole column(s) b/c then you would have empty cells that were formatted. Is there a better way that you guys format your columns without selecting the whole column? I know you can do this in VBA, but I was just wondering if there was a way that I'm unaware of.

Second, is there a formula that can inserted into conditional formatting, that will check a named range against two other cell values. If that ranges date is > AB2 and <AC2 then it will bold that range? I'm trying to figure out how to do things without always relying on VBA if possible.

View 2 Replies View Related

Combining Multiple Cells In Multiple Worksheets In Multiple Workbooks Into One Table

Jan 6, 2009

I'm currently doing a survey using an excel workbook that contains multiple questions across multiple worksheets using radio buttons linked to certain cells.

I have around 400 workbooks coming back to me, so what i want to do is take specific values from across many worksheets within each workbook and combine them into a large master table in a seperate workbook.

I've tried using VBA, but not being very proficient at it i've hit a brick wall with that, so i'm hoping that there is an easier way to do it than what i'm currently pursuing.

View 9 Replies View Related

Running Multiple Modules Across Multiple Workbooks With Multiple Sheets

Feb 19, 2012

I've got data being scraped from a site, putting 1 new workbook in a folder each day

each workbook has 40 sheets in it.

i need to run 5 modules in sequence on a sheet then loop to the next sheet and run the same 5 modules.

ive writen all the modules, and can loop them through the sheets in sequence but i cant work out how to loop them through the each workbook in the folder..

is there an easy way to do this or can it not be done because it would need access to the folder that holds all the wordbooks which lives outside of excel on the desktop ?

View 5 Replies View Related

Consolidate All Data In Multiple Worksheets Of Multiple Workbooks In One Master File?

Jul 12, 2014

I need a macro that would consolidate all data in multiple worksheets of multiple workbooks in one Master file.

All the workbooks will be in one particular folder. The macro should search for data in all the workbooks and consolidate it in one master excel workbook.

I am currently using both excel 2007 and excel 2010. This macro would really reduce manual work as currently consolidating data from 45 to 50 sheets takes an ample amount of time...

View 4 Replies View Related

Copy Multiple Columns In Multiple Workbooks Into Separate Worksheet?

Feb 27, 2014

The following code won't let me copy from the first workbook. I get a run time 1004 error stating "That command cannot be used on multiple selections".

I would rather not have to copy this by column for each of the 4 workbooks

[Code].....

View 6 Replies View Related

Summing Data From Multiple Workbooks Based On Multiple Criteria

May 14, 2014

I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.

Sample:

Location A Class:AA Segment: Sports Bar Rep: John Smith

Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0
Cocktail Menu: 0 1 1

I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.

I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.

View 2 Replies View Related

Copy Multiple Values From Multiple Workbooks To Master Workbook

Oct 7, 2009

I want create a macro which will extract 6 values (see below) from a workbook tab called summary to an master workbook for reporting purposes. Each workbook has a unique file name e,g ACI1150.

Values on sheet SUMMARY:
A1
A2
A3
G21
G24
G26

I tried to adapt the below to get one item copied/extracted. However it would no work.

I am new to using macros

Sub GetG26s()
Dim MyDir As String, FN As String, SN As String, NR As Long
Application.ScreenUpdating = False

View 9 Replies View Related

Combine Workbooks With Multiple Sheets Into 1 Multiple Sheet Workbook

Mar 16, 2008

I have about 20 workbooks with different file names for different projects all saved in the same folder. Each workbook has about 10 worksheets and each worksheet is named in a similar fashion in each of the 20 workbooks (eg. revenue, cost, variance etc.). I want to pull out a worksheet named ' forecast' from each workbook into a master workbook so that the master workbook would contain the 20 forecast worksheets.

View 9 Replies View Related

Append Multiple Workbooks With Multiple Worksheets

Sep 5, 2008

I would like to append multiple workbooks with multiple worksheets in a separate workbook. For eg. I have workbook "A" with sheets 1,2,3 and workbook "B" with sheets 4,5,6. Now I want to append "A" and "B" to create workbook "C" with sheets 1,2,3,4,5,6.

View 9 Replies View Related

Changing Multiple Workbooks And Multiple Worksheets Within

Jan 28, 2009

I have a folder - U:30000 - that contains a number of identically layed out workbooks. What I'm trying to do is, in each, change a number of cell values. Where dominicb's solution to the previous problem didn't work for me, however, is that I need to change cells in multiple worksheets. The cells are specifically:

On worksheet 'Construction': C3 (which is a date), D3 (which is a text value); on worksheet 'FF&E': D3 (the same text value as D3 on 'Construction'. Unfortunately, whoever initially set up the workbook didn't have the foresight to link it!)

View 4 Replies View Related

Combine Multiple Worksheets From Multiple Workbooks

Dec 27, 2013

Code to merge worksheets from different workbooks stored in different location.

I have a sheet called "Master" in all the workbook I want to combine.

I have a unique password for all the workbook as well.

All the workbooks are stored in different folder location.

I would like to do a paste special values when the consolidation takes place.

View 1 Replies View Related

Combine Multiple Worksheets From Multiple Workbooks

Sep 7, 2006

I'm trying to combine data from several worksheets (one sheet per workbook) into a single, consolidated master worksheet for reporting purposes (filters and pivot tables). We do not need to keep formulas for the master worksheet, only values and formats. Individual worksheets are used by different users to capture case data in a Human Services field. Column headings are identical, but rows contain data on individual cases. I'm trying to find a relatively easy way to combine multiple worksheets into a single master. After I establish the worksheets and technique, it will be operated by extremely basic users so I've been reluctant to use extensive macros.

Because of complex reporting needs, the exact combination of worksheets being combined for reporting may vary. For example, one time I may combine Tom, Dick and Harry, another time Tom, Dick and Bob, and yet another time Tom, Dick, Bob and Harry. Obviously, one method is to cut and paste the rows into a single worksheet. Are there more elegant solutions that could easily be handled by very basic users? Worksheets are stored in a single folder along with a separate worksheet used for validation rules (as you can guess, this would ideally be a database application but for various economic and political reasons we are using Excel). One possibility, if straightforward, is to use Access to consolidate data then export it back to Excel for analysis. I've scoured the various threads but have not found a situation mirroring mine. The number of rows for each worksheet is generally less than one hundred, but there will be a few exceeding several hundred. Total numer of rows of the resultant master worksheet will not exceed 10,000.

View 4 Replies View Related

Find Multiple Instances In Multiple Workbooks

Feb 16, 2008

I'm trying to search through a group of Excel files for specific values in column B and then copy and paste the value from column C of the same row into the current worksheet if the search term is found. Thanks to some help from turtle44, I can do that if the search term only appears once, but if it appears more than once, I can only find the first occurance.

I have searched through te archives and found lots of people asking for the same thing. The common suggestion is to use .FindNext, but no matter how I try to use FindNext, it doesn't give me the results I want. It either seems to find the first occurance again or to find nothing at all.

Here is a simplified version of my code, if someone could just explain how to find the second occurance of "Use Code" and past the relevant value into column F of the destination workbook, I'd appreciate it and I should be able to work out the rest on my own.

Sub SearchAllXLSFiles()

Dim lCount As Long
Dim wbSource As Workbook
Dim wbDestiny As Workbook

On Error Resume Next

Set wbDestiny = ThisWorkbook

View 5 Replies View Related

Editing Multiple Workbooks

Apr 19, 2007

I have 150 or so workbooks. Each of them are in the same format. I need to edit this format. Bolding, Borders ect. I know you can do this with multiple worksheets but how can do do with with multiple workbooks?

View 12 Replies View Related

Merging Multiple Workbooks

Oct 25, 2009

I have a series of duplicate workbooks to allow for distribution to multiple users. The workbooks have a userform which allows the user to save records into a worksheet. I would like to have a standalone workbook which is a merge of all of the worksheets.

Since data will be contiuosly entered, I want the master workbook to have a "constant" link and alway but current without need to run anything.

View 2 Replies View Related

Multiple Excel Workbooks Into One

Jan 12, 2010

I have a folder where we get 10-15 workbooks and we need to convert these workbooks into one. Currently we manually copy from these individual workbooks and then paste it to the one which we want to have all the data from all the workbooks. This is really time consuming. Can anyone of you help me with creating a Macro which will do the job?

View 14 Replies View Related

Combine Multiple Workbooks Together?

Oct 20, 2011

I am trying to combine multiple workbooks together. I have some code that works great except I only want it to combine one sheet from each workbook. The workbooks are identical they just hold different data. Each book contains 8 worksheets and I want to copy only data from "Container Info"

Code:
Option Explicit
Sub CombineSheetsFromAllFilesInADirectory()
Dim Path As String

[Code].....

View 3 Replies View Related

Combining From Multiple Workbooks

Mar 9, 2009

I have multiple WB's all with the same format in a single folder. I need a button to copy all the text from each WB into a single Master WB that has the same format. There are 3 sheets in each client WB corresponding to three sheets in the Master WB. Each client WB has a number of rows (or none) on each sheet and when they are copied to the Master they need to paste consecutively and into the corresponding Master sheet.

Something like this:

Copy rows from [WB1].Sheets 1, 2 and 3 (starting at a:4, columns A-Q). Then paste to corresponding [MasterWB].Sheets 1, 2 and 3 (at a:4, columns A-Q) then repeat with Next Book.

I hope thats clear enough. The Client WB's are all named "stats [name].xls" with 1 hidden sheet (to populate lists) and 3 sheets named "POC", "ISS" and "ECS" repectively. The Master WB is named Stats.xls with the same sheet names as the Client WB's.

The following code was written for me by a helpful member of this forum but it only copies the first sheet of each Client WB. When i tried to duplicate and modify it to copy the second and third sheets I could not get it to copy from the second/third sheets and it meant 3 buttons/3 steps/3 times the confusion.

Sub Report()
a = 1:
st:
If Sheets(5).Cells(a, 1) = "" Then GoTo endd
Path = Sheets(5).Cells(a, 1).Text
If Dir(Path) = "" Then
w = MsgBox(Path + " Is Not A Valid Path / File", , "REPORT")
a = a + 1: GoTo st
End If

If there is anyone who could help me with this I would very much appreciate it. I am only a basic user of Excel and VBA is still new to me. Adding modules and understanding basic commands is as much as I know at the moment.

View 9 Replies View Related

Pulling From Multiple Workbooks Into One

Jul 20, 2009

I have 8 individual workbooks that reside on a networked drive that I need to compile into one worksheet in another workbook. The 8 source files are in a separate folder.

I got the following code from someone on this forum, but I can not seem to get it to work exactly.

Sub CopyFromWorkbooksTake3()
'Declare and define variables
Dim objWB As Workbook, SourcePath As String, wbName As String
Dim LastRow As Long, NextRow As Long, DestSheet As Worksheet, i%
NextRow = 2
SourcePath = "C:/Your/File/Path/"
Set DestSheet = ThisWorkbook.Worksheets("MasterList")

'Identify the directory of interest
On Error Resume Next
ChDir SourcePath
If Err.Number 0 Then
Err.Clear...............

View 9 Replies View Related

Using A Function In Multiple Workbooks

Nov 29, 2009

I have made a function that I seem to use in a lot of my workbooks. I just paste it into which ever workbook I use it. I was wondering if I could have the function in one location and use for all?

View 9 Replies View Related

Search Across Multiple Workbooks

Jan 7, 2010

I need to be able to search for an "Street Address" across mutiple workbooks all stored in same folder

My workbooks have one sheet with several columns one column is "Street Address"

What i need to do is search all the workbooks that are in that folder to see if a particular address is already in a previous workbook

Example:

Search
1313 Mockingbird lane

msgbox no match
or msgbox found in workbook blah blah.xls

I am using excel 2003, however the workbooks i open and the save get converted to 2007 if that makes a difference

View 9 Replies View Related

Copying From Multiple Workbooks

Feb 6, 2010

I have a series of workbooks residing in different directories which I am trying to copy into a summary workbook. At present what I did is to open the summary workbook and the individual workbook one by one and copy into the summary workbook by first right clicking of the individual workbook's name tab and select copy to copy onto the summary workbook as a new worksheet. I have about 20 individual workbook to copy and this always take a bit of time every week.

To be a bit clearer, the workbooks are in the following directories and the workbook name and sheet name are shown below. For simplicity sake I will only list 3 of them.

1. D:ContractINF00012301.xls the tab name is 2301
2. D:ContractINF0022403.xls the tab name is 2402
3. D:ContractINF00038400.xls the tab name is 8400

View 9 Replies View Related

Sum Up Numbers From Multiple Workbooks

Oct 9, 2008

I have had a look at FAQs and some of the threads but I can't seem to find one that actually sums up numbers from the various workbooks.

I have 15 workbooks all formatted the same, and a parent workbook, again with the exact same format. I would like to consolidate the numbers in one cell in the 15 workbooks to appear as one number in the parent (in the same cell ref).

E.G. wb1 has 150 in cell A1
wb2 has 75 in cell A1
wb3 has 25 in cell A1
wb4 has 100 in cell A1

The parent workbook should have 350 in cell A1 after the macro is run.

View 4 Replies View Related

Run Multiple Macros From Different Workbooks

May 3, 2006

I am trying to run multiple macros from multiple workbooks. I've figured out how to open each workbook, but I can't figure out how to run a certain macro in each workbook called "Main".

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved