Create Reports From Workbooks
Oct 21, 2009
I do testing of industrial equipment, collecting a lot of data along the way. Once I've calculated the performance metrics of existing systems, I do a cost analysis on proposed repairs or replacements.
I have a couple of goals in trying to streamline my spreadsheet process and make it more effective.
My worksheets usually end up with way more columns than I can view on the screen and that gets pretty cumbersome, so I want to develop input forms that will make data entry simpler. I've tried transposing the formats but they get even more difficult to handle that way. I just ran across the "Build a UserForm for Excel" pdf in this forum and I think that's going to help quite a bit.
Then for each of these types of projects I need to develop at least two report formats, one that will be comprehensive for each individual piece of equipment or subsystem that I'm analyzing, and another that's more of a summary of entire systems, such as a network of pumps all owned by the same customer. Ideally, this summary report would allow me to rank the subsystems by various criteria, such best ROI or most critical to operations. Format-wise what I'm looking for here is to strip away anything not relevant to decision makers, just reporting the important ID info and key results.
Finally, I'd like to be able to store all similar jobs in a single database so I can easily search through past results for comparative purposes when doing higher level analysis on new projects. I keep seeing the term "relational database" bandied about but don't really know if that's what I'm talking about or not.
View 10 Replies
ADVERTISEMENT
Jan 19, 2013
i would like to create reports and queries in excel just like in access is this possible or do you import a layout,i have a sheet that has data on i would like to be able to pick just one row or mutiple rows and print them off could i save them to a menu to use over and over again,would i use a userform to search for these or can it be done otherways ,have seen pivot tables but seem bit complicated
View 2 Replies
View Related
Jun 20, 2008
I have the following spreadsheet Columns on master sorted by Column J:
A--------------E-------------J
SUPPLIER----CARRIER------LOC CODE
BELGIUM-----HAM-----------BP
HYDRO-------MSC-----------BP
DAINIPPON---NNR-----------BP
BELGIUM------YM-----------BR
BELGIUM------YM-----------BR
HYDRO-------MSC----------CA
BELGIUM-----HAM-----------CA
HYDRO-------MSC-----------CA
HYDRO-------MSC-----------DE
DAINIPPON---NNR-----------DE
BELGIUM------YM-----------DE
BELGIUM------YM-----------DE
What I need too do based on the Codes in Column J is create a separate workbook for each different code.
View 9 Replies
View Related
Apr 24, 2014
I need to create 3 or 4 detailed reports from an excel 2010 worksheet.
My worksheet contains data including date, invoice number, company , consultant, days, dollars. There are approximately 100 records in the worksheet.
Requirement 1 :I want to use excel 2010 to automatically generate a series of reports broken down by consultant showing date, invoice number, company, and consultant with totals for days and dollars, when all the records for consultant 1 are listed, then I want to show consultant 2.
Same for any other consultants with a grand total at the end.
My understanding is that excel 2010 makes this process very simple .
View 3 Replies
View Related
Sep 16, 2012
See attached file - I would like to use the "Create Parts issue Report" Button to create worksheet titled "Parts Issue Report" based on the pulldown menu in cell B1 tab "Vehicle data" so each pulll down will generate a different "Parts Issue Report" when clicking the "Create Parts Issue Report" button. I think it needs to be a looping VBA to go thru the "Vehicle Data" rows and columns looking for the keywords "late" or past" as I only want a report created on that criteria. The output criteria I have label in the tab "Parts Issue Report" I can explain further if needed.
View 1 Replies
View Related
Dec 19, 2008
i want to create folder depending on cell value a1 select data from a1 to h20 save this as "b1 value.xls" my saving directory is c:
eports"a1value" ....
View 9 Replies
View Related
Feb 17, 2014
I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
View 4 Replies
View Related
May 15, 2014
Array set for workbooks? I have more than 10 workbooks to update
refer to attachment. the VBA code is in Consolidation SOP.
View 13 Replies
View Related
Nov 27, 2013
make a list of all Excel Workbooks in specific folder(My Documents). And once clicked, it will open the file immediately.
View 10 Replies
View Related
Jun 17, 2014
For example...
I have data for monday.. Tueday ..Wednesday as individual excel
All the excel have same column names and dynamic values.
Is there a way i can create one pivot with different excel as source
View 1 Replies
View Related
Dec 10, 2009
create a list of all open workbooks? I've found various examples of code that will do this in a Message Box, but what I am after is for them to be listed actually in the worksheet (let's say in column AA).
View 5 Replies
View Related
Jul 27, 2006
I have a workbook that query's our case tracking database and pulls data into a csv. I am trying to write something that would do the following:
1) Prompt the user to enter in a case number
2) Open up the following file:
-"C:Documents and SettingsDetails.csv"
3) Look through A:A for a match of the user entered number
4) If a match is found then pull the data from cell B in that Row
5) Output the data (from row b)into a text box on the user form along with the original entry made by the user.
View 2 Replies
View Related
Jul 3, 2013
I need to consolidate a lot of information from multiple workbooks all the workbooks are located in a folder, i am not bothered about running each one separately or a group at a time, each work book has ten sheets with each sheet in the workbook being different, it needs to add to the next blank row on each sheet.
View 1 Replies
View Related
May 13, 2014
I often have a table (sometimes formatted as a table, sometimes just data arranged like a table but not formatted as a table in Excel) where I'd like to create individual worksheets (eventually workbooks) based on the information in one of the columns. Take for instance the attached file. I'd like to create a macro to create worksheets (or preferably workbooks) based on data in the Region column. So workbook 1 would be something like North Region Sales 2014, and contain only the data for the North region. Workbook 2 would be something like South Region Sales 2014, and contain only the data for the South region, and so on. Sometimes I might need to create these based off the Region field, another time I might need to do it based off of the Salesperson.
Region Sales 2014.xlsx
To do this currently, I'd use the filter and unselect whichever data I want to keep and then delete all the remaining (visible) rows. When I unfilter, I'm only left with the data I want. This works, but it takes a long time when working with 50 or more "Regions" and large amounts of data.
View 14 Replies
View Related
Jun 3, 2009
On a weekly basis, I receive a single worksheet in a workbook that contains ~30,000 rows of product sales.
Row one contains column headings that is unique to all other rows. Column A contains the store number that sold the product. There are ~50 unique store numbers.
I am trying to create a macro that will break the report up into seperate workbooks.
For example, assume in column A there are 30 rows of data for 3 different store numbers (say store 112, 386, & 798, each with 10 rows of data). I want to create 3 new workbooks and include the same column heading for each. For example, name new workbooks as follows: "Store 112", "Store 386", "Store 798".
View 7 Replies
View Related
Feb 22, 2012
Every two weeks: During Payroll, we have a list of managers and specific employees under that manager.
The managers name is in Column A and they repeat.
Normally we filter that manager, copy and paste the data into a new Workbook, save it, and send the info out to that manager. We do not want other managers to see the information provided to the specific manager.
This is a long tedious process as there are close to 30 managers.
1. Is there a way to run a macro (presumably based on a validation list) to filter, copy the worksheet into a new workbook for each of the 30+ managers?
2. Is there a way to save each with a specific name like "Payroll Feb11 - Manager Name.xlsx"
View 9 Replies
View Related
May 4, 2014
I have 16 different workbooks for different regions of my company. Each workbook has one worksheet (sheet named each region name), and all workbooks are formatted same regarding column names. Ultimately, I would like to combine all workbooks into one summary workbook where I can build a pivot table and pull out various data. Do you think this method is the best way to do what I am trying to do? I am very novice when it comes to VBA. I have written the code below, however, it is not working.
Code:
Sub GetSheets()
Path = "C:UsersDanielleDesktopWorksheets"
Filename = Dir(Path & "*.xls")
Do While Filename ""
Workbooks.Open Filename:=Path & Filename, ReadOnly:=True
For Each Sheet In ActiveWorkbook.Sheets
Sheet.Copy After:=ThisWorkbook.Sheets(1)
Next Sheet
Workbooks(Filename).Close
Filename = Dir()
Loop
End Sub
View 8 Replies
View Related
Oct 10, 2006
Every month I work on an audit that has data from 35 different distributors. I have code below that puts each distributors audit/sales information on a new tab and each new tab is given the distributors name. This also creates a new workbook for each distributor.
When the new workbooks are created, how can I name each workbook with the distributor name it's being created for? Is it possible to predefine a file path to where these new workbooks will be saved?
The distributor names I'm using are in column AF.
Sub FormatList()
'The code below creates and names a new tab for each members info
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")
'extracts a list of member or distributor names
ws1.Columns("R:R"). AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("T1"), Unique:=True
r = Cells(Rows.Count, "T").End(xlUp).Row
'set up Criteria Area
Range("U1").Value = Range("R1").Value
For Each c In Range("T2:T" & r)
'adds the member name to the criteria area above
ws1.Range("U2").Value = c.Value
...............
View 9 Replies
View Related
Jan 17, 2008
I have a workbook that contains one worksheet with data. This data has 7 columns and is a database metadata report.
The columns are as follows:
Table
Joined Tables
Column Name
Column Alias
Column Description
Column Data Type
Column Length
From this data, I am looking to have a script that automates the creation of a new workbook for each unique value in the Table column (i.e. tablename.xls) and saves them to my local drive. Each workbook then would have x number of worksheets named joined table 1, joined table 2, etc that relate to the name of the table in the original Table column. Finally, each worksheet will contain the related Column data for each Joined Table as mentioned above.
View 9 Replies
View Related
Mar 8, 2014
I am trying to create a database that contains data taken from several work books that i have saved in one folder on my hard drive. These workbooks contain quite a lot ** data, but i only want to extract the data i want to instead ** having to extract it all? these works books are used by several other people as well, so i want it to be able to update as data changes.
I was thinking about using data links, but that only seems to extract all the data instead ** just what i want?
View 1 Replies
View Related
Apr 28, 2014
I expect to deploy over 200 iterations of a workbook (templates if you will) containing a mix of up to one hundred different attributes each with its own list of valid values. My problem is the attributes don't always default to the same column in every template workbook. Therefore, to make things easier for users I would like to create attribute valid value dropdown lists on the fly using the "Valid Values' table as the source data (see the attached). Since the attribute names will be the same from one template workbook to the next, I wanted to come up with a macro that would reference a source table (a separate workbook) containing all valid values for all attributes which users would be required to download to their local machine.
See the attached example workbooks and an example of a "Valid Values" list. Here's what needs to happen: When a user opens template workbook #1 I need drop down lists containing the valid values for the attributes contained in cell B3 (User Code),C3 (Department) and D3 (Category). Likewise, when another user opens template workbook #2, I need drop down lists containing the valid values in cell B2 (Department), C3 (Group), D3 (Category), E3 (Level), F3 (Material) and G3 (User Code). Notice that "Department", "Category" and "User Code" are data elements common between both workbooks but resident in different columns.
View 2 Replies
View Related
Jun 27, 2014
I have data in Column C that looks like this:
John
John
John
Ted
Ted
Ted
Gary
Gary
Is there a macro that can create workbooks based on this data. So, i'd have one workbook that would contain all the Johns and all the other data in Columns A -Z, then another workbook for Ted, and so on...
The number of workbooks I need is not fixed. It all depends on how many names are in column C.
View 14 Replies
View Related
Oct 9, 2012
I have a workbook with about 10000 rows of data for about 100 suppliers in Sheet1 and about 15000 rows of same Suppliers payment details in sheet2.
What I am doing now is:-Filter every supplier names in Column A of sheet1, copy all the rows and paste in sheet1 of a new workook & Again copy the Payment details of a same supplier from master file sheet2 and paste the same in sheet2 of this new workbook then save the files with the supplier name as file name in my documents folder code, that filters each suppliers data from sheet1 & 2 and paste in new workbook sheet1 & 2.
View 1 Replies
View Related
Jul 31, 2013
I have a worksheet with the following columns:
Brand
UserName
FirstName
LastName
Email
[Code]....
This is a relatively long list - 1000's. What I'd like to do is create a macro that sorts by brand, and at every change in Brand, copy the user details (Username, firstname, lastname, email) to another workbook with those labels at the top of the table. Upon completion, it saves the workbook with the name of the appropriate brand.
E.g. I'd have 3 workbooks:
- BrandA.xlsx - with the 2 user listed
- BrandB.xlsx - with the 1 user listed
- BrandC.xlsx - with the 1 user listed
View 3 Replies
View Related
Mar 18, 2014
I have a TEMPLATE workbook that has 106 cells (all in the same worksheet) that need to have data input in them.
I have a separate DATA workbook with 3,000 rows of data, each row has 106 columns that correspond to the cells in the TEMPLATE workbook.
I need to create 3,000 new workbooks that are populated with the data from the DATA workbook.
View 3 Replies
View Related
Sep 2, 2007
I have some very tedious work to do in Excel:
table looks like following:
DepID name function
S1 a YY
S1 b XX
S1 c ww
S2 d oo
S3 e ii
S3 f ll
S4 t mm
. . . . . .. . . .
. . . . . .. . . .
. . . . . .. . . .
S7999 u ee
S7999 w aa
My task is to create new folders for each department according to DepID, which means if there are 7999 departments, I have to create 7999 folders, any VBA code can do this?
View 9 Replies
View Related
Jan 16, 2008
I used the code below to successfully create workbooks from the filtered list using the below code provided on this forum (see link below). The only problem is that I want the resulting columns to fit to width.
Looping Through A Range - Use Result As Criteria In A Filter
View 9 Replies
View Related
Jun 13, 2013
I am trying to make my macro more dynamic for a spreadsheet I am working on. I have this code which splits out worksheets (using the specific names of the worksheets-not dynamic) from a main workbook into template workbooks (which are basically just an empty workbook because I didn't know how else to do it) and then saves them using the paths below. I would like to make this more dynamic by splitting the different worksheets into new workbooks based on a key column in the hierarchy worksheet.
Basically I have a list of accounts in column B with the file name they should be exported to in column A. There are about 30 accounts being split into 6 different region files. Also note that the same account might be listed multiple times in column A (needed to add multiple numbers for other lookup formulas in the worksheets) but that account worksheet will still only be going to one of the six region files and not to multiple regions. After these are copied to an individual file I would like to save it to a location on my computer. All files will go to this location.
VB:
Sub Create_Subfiles()
Dim FDMName As String
Dim FBName As String
Dim DIYName As String
Dim WMName As String
[Code] .....
View 3 Replies
View Related
Jul 6, 2008
I have an excel file which contains following data in it.
Col-A Col-B Col-C ......
Cust Cd Name Sales
=======================
101 AAA 1000
101 AAA 500
101 AAA 3000
102 BBB 800
102 BBB 200
103 CCC 200
103 CCC 200
103 CCC 200
I need to create following three workbooks with name based on Cust Cd from above excel file.
Workbook - 1 : 101.xls which contains records only pertaining to Cust Cd 101.
Workbook - 2 : 102.xls which contains records only pertaining to Cust Cd 102.
Workbook - 3 : 103.xls which contains records only pertaining to Cust Cd 103.
View 4 Replies
View Related
Jun 27, 2013
VBA Macro to work through a worksheet that consist of static data (tab 4) cost centres and to populate a new work book per cost centre consisting of three tabs for every cost centre found in the static data.
The master Workbook has the following tabs:
Tab 1 is called travel and consist of column a which is the cost centre number (plus 14 other columns)
Tab 2 is called Mobile and consist of column a which is the cost centre number (plus 14 other columns)
Tab 3 is called Expenses. and consist of column a which is the cost centre number (plus 14 other columns)
Tab 4 Static Date column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns).
If no information found on a specific cost centre, the tab will include the headers and return the words "No transactions for this period"
Whilst splitting data into Tabs the workbooks should check against the Static Data table and include cost center description in Column B of each tab in the new workbook.
If master workbook consist of the following....
Tab 1 is called travel and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,557,
Tab 2 is called Mobile column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns) cost centres, 555, 78689,
Tab 3 is called Expenses. column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns), cost centres, 555,
tab 4 Static Date - column 1 cost centre number and consist of column 1 which is the cost centre number (plus 14 other columns). cost centres, 555, 557,78689
It should output 3 workbooks by cost centre number.
One for 555, which consists of 3 tabs, travel, mobile and expenses.
A second for 557 which has 3 tabs travel, mobile and expenses, but only with data in the travel tab.
A third for 78689 which has 3 tabs travel, mobile and expenses, but only with data for mobile data.
The workbooks will be replicates of the contents within the tabs where column a wil be the cost centre plus 14 additional columns.
View 5 Replies
View Related
