Vba To Create Separate Reports From Master
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 Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
Creating Separate Reports In Separate Spreadsheets
I have a giant data sheet. I have to cherry pick certain data out of the list and send it to certain associates, in a new spreadsheet. I want to do this automatically. I can create the macro to select the data I want, and copy it over (values only), but I cannot find out how to do it in a new sheet with a new name that automatically gets generated with a unique filename (probably using the date). Essentially, I want to copy my data spreadsheet (post-filter) into a new spreadsheet titled X mm-dd (where X is whatever I pick). I then want to delete several rows from it. Ideally, the macro would do this (I will put a * next to things I can handle the code for, to save work):Filter Data * Copy Data * Create a New Spreadsheet named (Person's Name) mm-dd.xls Paste Data (values, formats, column widths) to the new spreadsheet * Delete Several Columns from the new spreadsheet * Rename the sheet it's been pasted to Re-filter data (new filter) * Copy Data * Paste Data (values, formats, column widths) to the same spreadsheet, only Sheet 2 * Set the header of the spreadsheet with (Person's Name) and the date Save the new spreadsheet There will be as few as 1 sheet (in which case I would only want one worksheet in the new excel file), or as many as 4 sheets. I don't need a fancy way to do this, as it will only be done once a week, and I can just make the various cases required to do it even if it isn't pretty, but I can't figure out how to manipulate other files that haven't been created yet...
View Replies!
View Related
Create Reports From Workbooks
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 Replies!
View Related
Create Userform Which Can Add Or Delete Master Data
I am making planning & status board for each project(yard). But in my 1st Stage I am having a problem for creation of Userform & List Box for my Display Sheet. Problem-1) I want to create userform which can Add or Delete my MasterData. Problem-2) I want to display & change Block Status of Yard in List View. Yard No will be selected using combo box. List View Should allow addition & Deletion of record. Note: I am a bit new user for VBA but can copy codes from sample file to use in the files.
View Replies!
View Related
Create A Separate File After Macro Done
How can I code to create a copy of a worksheet in an excel file I am using a macro on, after the macro is done processing? In other words, when the macro completes processing, create a copy of the worksheet labelled "Output", and save it in a directory that the user chooses? Also, would it be possible to create a .pdf file?
View Replies!
View Related
Create Separate Worksheets Within Workbook
I have a table of data with three columns. In the first column I have different values, but they can also repeat. How can I have VB look at my table and for every distinct value in the first column create a sheet with the value name and paste all the data into that same sheet?
View Replies!
View Related
Create Separate Columns From One Column
I have a spreadsheet with these columns:Group_Name Contact Name Company Name Contact ID Situation: The Group Name Column has the name of the group that the Contact Name is a member of. The Contact Name could be a member of many Group Names and therefore there may be many rows of the same Contact Name one with each Group Name that they are a member of. I have manually created separate columns for each Group Name in the spreadsheet that is attached. Goal: I would like to have a macro that will look at the Group Name column and create a new column for each distinct group. I then would like the macro to go down the Contact Name column (I believe that I will have to have it sorted)and look at the Group Name that is listed in that row and put "True" in the Group Column that was created in the above step. Then, go to the next row, if the Contact Name is the same, look that the Group Name column and put "True" in the corresponding Group Column in the FIRST ROW OF THAT CONTACT NAME. Then Delete that row.
View Replies!
View Related
VBA Search And Paste (master)
I am trying to learn VBA and am struggling to figure out how to get started with this problem. On my "Master" sheet in cell B107 is a section where you can enter a search term. The term entered should be found in the headers on my "Site Resources" sheet in A2:H2. If it finds the term in the headers then I want it to copy and paste whatever is below that header into C108 and down in the "Master" sheet. How would I start this code?
View Replies!
View Related
VBA - Creating Separate Files And Tabs From A Table
I am sure the answer to this is out there somewhere but I haven't been able to find it. Thank you in advance for the help - I just can't figure it out. I have several identically formatted worksheets (for different departments) with macros that reorganize the data in each sheet. I also have a list of managers with their respective departments in this same workbook. I want a macro that will save these department sheets (values and formats only) in new workbooks - the manager's name - according to the list. I also need the macros that reorganize the data to work without referencing the original file.
View Replies!
View Related
VBA Data Entry Form - Populating Separate Book
I have built a data entry form (Input tab) and have code populating a table (Table tab). Currently both are in the same book, but I would like to house them in separately. What is the proper VBA syntax for this? Will both books need to be open in order to populate the table? The 'real' data is hosted on sharepoint and is getting darn big. The result is problems opening the doc and looong save times. My thought was to have the secretaries doing data entry open a single sheet (Input Form) which will then populate the data in another sheet. I'm dreaming, aren't I... I know Access would solve everything, but for cost reasons I am stuck with Excel.
View Replies!
View Related
Using VBA To Create VBA Code On The Fly
I have a vba module that reads a value from a txt file and saves that value in a variable called "iniInfo". I want to permanently assign that value programatically by using VBA code to create a Public variable e.g. "PUBLIC CONST conFIg = iniInfo" in my modGlobalVar Module. (Of course, this would only create the code if the "conFig" variable doesn't already exist.)
View Replies!
View Related
Create "Master" Listbox From Choices In Another Listbox
I have two listboxes on a userform. One is the "choice" listbox, the other is the "master" listbox. Each item selected is a billing object on a sales invoice. problem: The master list works fine when the item is selected in the choice list. But when it is deselected, how can you REMOVE it from the master list? question: How can I add a text box automatically to the userform to allow the user to enter quantity info? For I = 0 To CodeList.ListCount - 1 If CodeList.Selected(I) = True Then obj = CodeList.Column(0, I) p = 6 test = 0 Do While test = 0 And p <= 25 If sheetsales. Range("C" & p) = obj Then test = 1 Else test = 0 End If p = p + 1 Loop If test = 0 Then.........................
View Replies!
View Related
Creating Reports
I am trying to create a report that takes two user inputs and looks them up on another worksheet in the workbook and returns the right value. As an example I have the user input a date and a job location and on another sheet there is also a date and a job location that has a population associated with it. Is there a way to return this information so that it changes whenever the user changes these two criteria
View Replies!
View Related
Filtered Reports
I'd like to created a 'Filtered Report' in which data changes based on my selection. For example, in this scenario I have 2 columns, 'Part Type'(you may pick Part1, Part2, Part3) and the 'Results' columns. I have displayed 100 entries(which means at this point they're all displayed, no filters). When I apply the filter, and I choose Part Type to display Part1 and its "Results', I would like to dynamically be able to display somewhere in that Sheet something like this: Part Type: Part1 Total: This would be a =sum(of the 2 cells below, in this case it'd be 17) Pass: 14 Fail: 3 But I'm not sure how to dynamically count it based on what I've filtered, so when I change to Part2, it would give me: Part Type: Part2 Total: This would be another =sum of the 2 cells below Pass: 25 Fail: 4
View Replies!
View Related
Comparing 2 Reports
Files :- [url] i have upload 2 worksheets (in a zip file) i have attached my 2 files, 1 is report of Feb 2007 & other is March 2007 on 1 Branch, i got 200 Branches reports & i have to compare the people recored and have to identify following things 1) in which Days bukket is fall in Previous report ( compare with current) 2) if the Account # doesnot match then it should be HIGHLighted! what i have to do is to compare the Account # with Previous & add a New Column & write the previous days (in number) plus highlight or show some remards that doesnot exisits in last report,
View Replies!
View Related
Compare Two Reports
I have an Excel Spreadsheet WorkSheet Analysis Work there are two reports side by side. A) Source of Truth & B) Analysis Studio. What i'm trying to do is find out if Analysis Studio Matches off with the Source of Truth. The columns in Analysis Studio being Column M & Column N have to match the Source of Truth Columns E "Cil account" & Column I "Code Name". Now the problem is that the names in Analysis studio Column M Example Cash is Different to the Source of Truth Coloumn I Example Cash Assets. This is what I would match as Yes being the Same as it is. The other Problem is Analysis Studio Column N has the Account & Name in one cell whilst the Source of Truth has both these in separate Columns. I’m happy if it can just match off Column E being the account to the Analysis Studio Column N. Ideally would love the results to fall either go in Column O being YES it matches or Column P it does not match the Source of Truth.
View Replies!
View Related
Crystal Reports
I use excel pivot tables a lot for most of my information gathering for reports that I produce. Problem I keep running into is the 64k row limit. I work with up to 200K rows of excel data at a time and just need to pick out a summary of certain columns. I have read (mostly adverts) good things about crystal reports and have been considering giving it a try. It will be a steep learning curve for me, but I was wondering have this group any views on the program before I splash out on books, DVD courses etc.
View Replies!
View Related
Formatting: Save And Use For Other Reports
Every week I run the same report for different projects to show financials and such. Each project will have a different number of rows based on how large they are or the scope or a combination. The number of columns is always the same as well as the basic headings. The system that provides the output creates a bland (well, ugly) report. Not the type of thing you want to distribute. I'd like to create a report format that I can quickly and easily cut and paste (or use another functionality) to use on these reports. Yes, I can cut and paste the formatting from the "Master" report's format but it's not easy to do as I have to keep highlighting each report's data range before pasting. I'd like have my OWN template under autoformat.
View Replies!
View Related
Navigation Of 2 Reports On One Worksheet
I have 2 reports on one worksheet. I have put a button at the top of each report to navigate back and forth. One report is located at a1:S27 - it shows up fine. The issue is the other report located at BA1:BT52. No matter where I assign a name to it the report doesn't come up full page with cell BA1 at the top left hand corner.
View Replies!
View Related
Update Monthly Reports
My workbook contains 12 monthly spreadsheets (one for each month of the year). It will also contain an additional spreadsheet, I’ll call “Update”. At the beginning of each month I will copy and paste new account information into the monthly spreadsheet (for whatever particular month we are currently in). The columns (J-N) will be blank. I will then copy and paste account information into my “Update” spreadsheet (this is done twice a week per month, from 8 to 10 times per month). This spreadsheet will contain data that needs to update my monthly spreadsheet (for whatever particular month we are currently in). The “Update” spreadsheet column B needs to populate columns J-N on my monthly spreadsheet and this will depend on column C of the “Update” spreadsheet. Sorry to be confusing . . . . Update Spreadsheet Column B – Payment Amount (dollar amount) Column C – Commission Type (Low, Medium, High, Super, Duper) Monthly Spreadsheet these columns need to be populated with the dollar amount Column J – Low Column K – Medium Column L – High Column M – Super Column N – Duper I forgot to mention that all data is compared via the account number! However, to make it even more confusing if I am updating my monthly spreadsheet and during the 8 to 10 times I update it during the month I find that I already have a dollar amount posted for a particular account for a particular commission type (Low, Medium, High, etc.) I do not want it to keep adding up the dollar amounts. So, if I have an account that has $25 for “Low” and then next time I update my monthly spreadsheet I have an additional $75 for “Low”, for the same account, I do not want it to calculate it to $100. I need it to either color code that entry on the “Update” spreadsheet or toss it onto another spreadsheet . . . just so it stands out and I know that I have to handle it differently.
View Replies!
View Related
One Input, Two Lookups, Two Reports
Some notes on my example file: The data would get pasted into the first sheet, in the shaded area. It might be exactly those IDs, or others - possibly a longer or shorter list each week. Sheet 2 has the lookup master lists, or checklists. I can set these up ahead of time, and don't change week to week. The input IDs will be checked against these lists somehow on the Report pages. Sheet 3 and Sheet 4 are the Report pages. I want a formula in column A to match the input ID values from Sheet 1 to their appropriate checklist on Sheet 2, and return the ID value on the appropriate Report. If the value matches Checklist 1, it should repoirt on Report 1. If it matches Checklist 2, it should report on Report 2. If the value isn't on the checklist, it shouldn't report, don't report it. If there are ID values on the checklist that don't appear on the input, they also should not report. On the Report pages, once I have the correct IDs reporting, I can put in other formulas to pull Revenue, Cost, etc. info from elsewhere, VLOOKUP-ed from the IDs. (I know how to do that.) The key is that in terms of sorting out the correct ID values to the correct reports, I don't want to have to do anything to the Report pages each week (like sorting to get rid of blanks, etc.) - I just want to paste in the Input.
View Replies!
View Related
Generate Reports Based On Criteria
I have included a copy of the spreadsheet. What I need to do is get a cummulative duration report for each crew. This particular spreadsheet has less data than most. Many times there are 8+ crews. I need to make a column or graph or report or something that will continually update as changes are made and report back something like; Sewer 142 hours, Water 200 hours etc...
View Replies!
View Related
Compare Two Financial Reports From Two Different Sources
i have to compare two financial reports from two different sources each and it should derive the same result. I am still working on it and only focus on Column C & D. However, i have come across some problems and hope that these milestones can be cleared so that i can move on to another step to achieve my goal. So, my current problem is 1) #N/A (eg Cell D9 & D32) will be resulted when hlookup function is used. Since the workbook 'CB_Consol_2009' does not have the same name appear, how can i make the answer become '-' instead of #N/A when hlookup function is being used? 2) Double counting. The same name appears more than one time (eg CURRENT ACCOUNTS-RET_ (01#0505_01) and as such, its figures also calculating more than once. Is there a way to calculate the figure only once so that i come out with the correct grand total? As my objective is to compare two financial reports results (which should be the same), this is the table i can think of. If anyone here have any constructive comment on my current format.
View Replies!
View Related
Command Button Shows Two Different Reports
i have this userform which has textboxes. Every information in the textboxes are linked in the two kinds of reports namely: Report 1 and Report 2. After filling the textboxes, what are the codes that i can use so that the command button 'Generate Reports' will display the Report 1 in Sheet 1 and Report 2 in Sheet 2?
View Replies!
View Related
Single Input To Multiple Reports
I'd like to be able to drop one table of data into a workbook that already has sheets of two or more master tables, and two or more report pages. A formula on the report pages would compare aspects of the data to the master tables, and report the data on the correct sheet based on certain criterion. The reports would not have blank spaces, need to be sorted further, etc. My initial impulse was to think in terms of ol' verable VLOOKUP, but I can't figure out how to get the report formula to move on to the next line of the data table if it doesn't find a match for the first line - short of adding another VLOOKUP (and another, and...), which is totally impractical given that the data table will likely have 20,000+ lines.
View Replies!
View Related
Build Daily Reports With New Dates
I have been lucky building codes, but this one has me bad. I already have a code that pulls data and puts it on a spreadsheet. To activate this I put in a date that I want to pull data from the PI server. What I want to do is save that data into an excel spreadsheet for history. I want it to know the date and save all the information. Then at a later time I can pull the data back up again. I can currently get it to move the data, but it doesn't sort by date and when you do the macro over it writes over what i just had. How do i get it to sort by date and enter into new cells?
View Replies!
View Related
Generate Weekly Reports From Table
I would like to create one page (sort of like a form) to use to collect specific weekly payroll related data for multiple employees and be able to automatically transfer that data to its own weekly data page based on the week date, and then also automatically post specific data to each individual employees' total pages. While I am self taught and comfortable with formulas I do not yet have a lot of experience with VBA and am just now recognizing and exploring the true power of Excel so I don't always yet see the best way to accomplish my goal. My current way of doing this is to reuse the same form page. I plug in the data for each employee that week, it calculates, I print it out, and then I manually update each employee's total page for that date. As I do this I lose the previous week's details. I would like to retain a page for each week's details in addition to having each employee's total pages. Is this possible or is there a better way to accomplish my task?
View Replies!
View Related
Create Graph Using VBA
I want to create excel graph using VBA and found difficulties on chart name set to default. the script is like this : ActiveSheet.ChartObjects("Chart 31").Activate Can I set my own name of the chart instead of "Chart 31" (excel default) ?
View Replies!
View Related
VBA To Create New Sheet ...
I would like to be able to have a macro, linked to a button, so that when you press that button it creates a new named sheet based on data in a particular cell. I.e. There is a table with the following data: Apples (A1) Oranges (A2) Pears (A3) There are also the sheets Apples, Oranges & Pears. You enter 'Grape' in cell A4, then when you press the button it creates a new sheet named Grape. Also when a new sheet is created, the 'input cell', in this case A4, is moved down one so that when the next sheet is needed to be added, it doesn't just add the same sheet (i.e. in the example above, we don't end up with a new sheet named 'Grape' each time we click the button).
View Replies!
View Related
Create Tag In VBA?
I found this really cool code that creates a tag cloud in Excel using VBA. It's far too complex for my understanding but I was hoping I could get some of the experts opinions on here about modification possibilities. I have attached the macro-enabled 2007 workbook. What I am trying to do is search through a list of keywords and determine each keyword's density within a list. The list for example could look like this: slow windows xp windows xp running slow windows xp computer [slow windows xp] [windows xp running slow] [windows xp computer] [computer running slow windows xp] "computer running slow windows xp" "slow windows xp" "windows xp running slow" "windows xp computer" You'll notice the characters " [ ] of which I would like to ignore when the cloud builds. The cloud would list the most dense keywords first, and gradually decrease to the least dense keywords. The macro code is inside the workbook but I'll list it here to:
View Replies!
View Related
Create VBA InputBox
I am trying to create an inputbox using code from this site: Sub NumericDataType() Dim lNum As Long On Error Resume Next Application.DisplayAlerts = False lNum = Application.InputBox _ (Prompt:="Please enter you age.", _ Title:="HOW OLD ARE YOU", Type:=1) On Error Goto 0 Application.DisplayAlerts = True If lNum = 0 Then Exit Sub Else MsgBox "You are " & lNum & " years old." End If End Sub I get an error on the first line that says, "Compile error: argument not optional".
View Replies!
View Related
Automatic Sheet Name For Pivot Table Reports
When I creating a Pivot Table Report on a different sheet. I have attached the Stock Report, where I have created a Button at the top to create the report, but the worksheet name is the default Excel name rather than the one that I want (in my case the name of the new sheet should be "Admin".
View Replies!
View Related
Create Comparison Code In VBA
I have to do monthly comparisons of two excel worksheets, one being a vessel timesheet and the other being an excel download from manpower software that we use. The comparison that is being made are days of the month, whilst the unique identifier between the two sheets (To make it easier to understand I will call them sheet1 and sheet2) is an employee ID. Both sheets, alongside the name and employee ID, list all the days of the month that employees have worked. If they have worked, there is a "1" under that day, if they have not the cell is blank. What I currently do manually is: 1: Search for the employee ID (and/or name) in sheet1 and compare the days with the employee ID in sheet2. 2: Highlight the differences 3: Move onto the next employee ID All differences are highlighted in sheet2. Differences highlighted are in the days only, where if any days are missing/extra in either sheet, then the particular cell(s) are highlighted in yellow. If an employee is missing from sheet1 but is in sheet2, then all the days are highlighted (in sheet2). If an employee is missing from sheet2 but is in sheet1, then that particular employee is copied to the bottom of sheet2 (after last row). As this is horribly complicated to explain, I can attach an example if this is sounding rather inexplicable? Is it possible to attach a xls to a thread?
View Replies!
View Related
Create A New Workbook By Vba Of Excel
I want to the following steps to be done by vba of excel. 1. copy ranges of cells in the active worksheet (sheet3) 2. add a new workbook and paste the copied ranges to sheet1 3. save the new workbook with the new file name (e.g. date) 4. close the new workbook and clear the contents the copied ranges of cells in old workbook. following code and make it more simpler? Worksheets("Sheet3").Activate If Range("l1").Value <> "" Then Range("l1").Value = CDate(Date) End If asdate = Range("l1").Value 'date entered by user resp4 = MsgBox(" Case particulars of " & asdate & " will be backed up and deleted from this sheet! Proceed? (Y/N)", vbYesNo, "Deletion of Data") If resp4 = 7 Then Exit Sub
View Replies!
View Related
Vba To Create A New File From Spreadsheet
I have the following spreadsheet: A-------------B File No.-----PRODUCT TYPE 123456-----NDT 123456-----NDT 123456-----NDT 123456--------- 123456--------- 789110--------- 789110--------- 789110--------- What I need is if there is an NDT in Column B and 123456 in Column A then I need to eliminate all rows which contain 123456. End result A 789110 789110 789110
View Replies!
View Related
Create Outlook Appointment From VBA
I am probably overengineering my macro, but I would like to setup an outlook appointment in two peoples calendars from my userform. The macro has access to the email addresses of the selected persons, I just want to send date/time and subject.
View Replies!
View Related
VBA To Create A New Folder From A Textbox Value
I don't know if anyone has tried this but it possible using vba to create a new folder in a directory from the text of a textbox? I have made a userform that I use to collect a persons first name & surname. I then created a folder in a directory manually (e.g Z:spnzphotos) I then scan their photo and place it into the folder. Is it at all possible to have a piece of code that once I press a command button is creates folder ready for me to add the photo to?
View Replies!
View Related
VLookup In Reports Downloaded From SAP & AS400
Report 1 is downloaded from SAP into Excel and Report 2 is downloaded from AS400 into Excel. Report1 contain 3 columns as follows: Column A - Invoice # Column B - Invoice date Column C - Invoice Amt Report2 contain 5 columns as follows: Column A - Invoice # Column B - Ship Date Column C - Check # Column D - Amt Paid Column E - Payment date I tried to use the Vlookup function in Report 1 Column D to lookup the matching Invoice # in report 2 and bring in the corresponding Amt paid. It did not work and I assume that this is because of the formats in the downloaded files. I did try to change formats for the columns involved but this did not work. This function works fine if both files were setup in Excel, however in these downloaded files even though saved as excel workbooks, something prevents this function from working. I do know for a fact that the same invoice number does show up in both these files but the vlookup does not pull up the required Amt paid from Report 2.
View Replies!
View Related
Manipulate Date Ranges For Monthly And Quarterly Reports
I am currently trying to manipulate date ranges for monthly and quarterly reports, and am having trouble doing this. I have attached a file with an example of what I would essentially need. I would need the "Nbr of Projects", "Nbr of Days worked", "Nbr of International Projects" and "Nbr of Local Projects" cells filled in under each respective report, based on the data in the top left. If a project falls in two months, such as "18.12.2008 to 15.01.2009", this would be treated as 1 project for December and 1 project for January. Also, I would need only "NETWORKDAYS" included in the solution, so 11 days in this example.
View Replies!
View Related
|
TRACKER
