I have a workbook with many sheets, all of which derive information from the first sheet. On the first sheet, I enter values from say J1:J29. Each of these values is fed into one of the subsequent worksheets. I would like to have some code that would look at my range, determine which cells are used and print the corresponding worksheets. Write now I have a bunch of If statements that say
If Sheets("Worksheet").Range("j8") > 0 Then
Sheets("698-ALY W1222").PrintOut
End If
the problem is that it takes to long as each statement is evaluated and the print command sent.
The values in column J have names in column I and while the names do not exactly match the worksheets, a person could look at the name and match the correct sheet. For instance the name in column I that corresponds to the worksheet name 698-ALY W1222 is 698/ALY.
My workbook contains several worksheets each recording the results of equipment tests - one sheet for each piece of equipment. Rather than open each worksheet to check the date of the last test, other 'overview' worksheets pick up and display the last test date from each equipment record using the formula =MAX('sheetref'!A15:A500). Is there a better formula to do this?
Some items of equipment are tested at more than one frequency, ie Monthly, Yearly, etc. Therefore, the entry on row 15 of an equipment record may be column A 28/10/08 column D 'M' and row 16 column A 29/10/08 column D 'Y'. The above formula will only display the last test date, irrespective of frequency. There is a seperate overview sheet for each test frequency therefore, I need to modify the formula so that the date it copies from the equipment record worksheet to the overview worksheet is the date of the last test for the specified frequency.
Imagine I have 2 columns of information that look like this:
Column A Column B
AS Dog
AS Cat
AS Hamster
FT Fish
These are my key columns. The letters are initials of people and the animals are the pets they're responsible for.
Now, I have 3 more columns that look like this:
Column D Column E Column F
These columns can go on for hundreds of rows.
What I want to do is pull out the information from columns D, E and F where the initials and pet match those in the key list, then paste that elsewhere (say to columns J, K and L).
So, for instance, the first entry would be copied across because, according to the key list, AS is responsible for a Dog, but the bottom entry for AS wouldn't because he was looking after a fish, and that pet isn't listed as one of his animals in the key list (Fish is listed alongside FT). Likewise, the entry for AH wouldn't come across because AH doesn't appear on the key list at all.
I have a spreadsheet that I am trying to use as the 'master'. I want to break down this master by category (Column D) and label the worksheet by category (AA, Code, SQL...)
I need for the code to search the D1:D500 column and find "AA", "Code" or "SQL" and place the entire respective row on each respective worksheet. Then sort by colmn E. There are columns a:h that need moved to each sheet.
I have a monthly template that has 31 tabs labeled 1 through 31 for the days of the month. I would like to add a macro that would print only the sheets that have a value greater than 0 in either of two specific cells.
I could add a summary sheet that would pull over these values and/or add if statements that return results like "don't print" or "print" if it would be easier in the macro or I could just use the code in macro to determine to print or not.
I am a record and tweak macro user and this isn't something you can record. I found some simple code in another post that would print one page based upon single criteria but it isn't exactly what I was looking for.
Am trying to put comments on column G based on information on column A. Column A contains a grade eg CDEF picked up from another sheet using vlookup. This grade is suppossed to be the same, but I have cases where I add a different grade say GHIJ to fill up my 99 required grades.Column G is for comments such as "Transfered" (which at the moment am entering manually)How can I use a formular so that each time I transfer from another grade other that the one intended it returns a comment "tranfered" on column G? Is it also possible to indicate the cell from which vlookup picked that grade on the same comment column G.
I am new to using Macros and have not had great progress building a macros to make my life easier.
What I am trying to do -In my main database tab 'FW Telecom' Use column A ( a set of numbers) to create a new tab based on that number, with a 'FW' infront IE ( column A shows 11, i want the tab to read FW 11) -I want to create one for every number in the column -Then I would like to copy my 'Template' tab to each new tab. -From there I would like to fill in the information in the new tabs from the main database tab 'FW telecom' -Since they will all be copies of the original template, i figure i can just get it to pull from the first column A and then fill in the info.
Not too much in theory going on here, just take my database and move the pertinent information to a user friendly look which I set up in 'template'
I would like to automatically color fill a series of rows based on like information from a single column. When the information in the column changes then the rows would either stop filling (this would be best) or fill with a different color until the information changes again.
Summary: series of alternating rows would either be filled or unfilled based on changes from the column information.
Hope this isn't too confusing. Below is an example except I would want the cells filled, not the text or numbers to change. The highlight is changing based on the changing of the numbers.
Is it possible to take the information below and make one list out of columns F, H and J based on the common information in column C? For example, group all the 18s in a list, followed by the 44s.this is excel 2007
I have a macro to print selected worksheets. It works well with one small problem. Whether I select the last worksheet or not, it always prints. I have tried a bunch of different ways to get around this, and all I succeeded in doing was getting a separate page printed first, then it would print the rest of my selection.
Sub PrintReportSelection() Sheets("Actual").Select Application.Goto Reference:="Actualprint" ActiveSheet.PageSetup.PrintArea = "Actualprint" Sheets("Deposits").Select Application.Goto Reference:="Depositsprint" ActiveSheet.PageSetup.PrintArea = "Depositsprint" Sheets("Debt").Select Application.Goto Reference:="Debtprint" ActiveSheet.PageSetup.PrintArea = "Debtprint" If Range("SelectVariance") = True Then Sheets("Variance").Select.........................
I'm using VBA code to select several worksheets at once, and print them. The code runs fine, but the result is multiple print jobs, not one job. This is OK when printing to the printer...but undesireable if you want to print to a pdf file, for example.
I've searched the site and found some references to this possibly being an excel problem. Is that true?
I've also checked the page setup setting to make sure that the print resolutions are all the same...that doesn't appear to be the problem either.
I am wondering how to print only the rows that have information in them. I have a report that is super long. We only want the information that is updated to print. This information will change weekly.
Is selecting the area I want to print the only way to do this? Or is there a way I can tell excel to print only the rows that have been updated?
This report is one that we use every week, for ordering, there is a lot of information on the report that we will not need every week, so we are hoping to print only the rows that are updated for that week.
spliting of main data By column criterias which start from column E1:L1 something like Pivot Table in new worksheet, but based on formula functions.Each worksheets have to based in one of this column D1:K1 headings. That also have to rapidly change with main data table.
I am looking to split data in a worksheet into separate sheets based on Column C which contains Either Babycare or Homecare or Industrial or Professional. Eventually there maybe more names. If possible I would also like to name each sheet.
I have a workbook that lists system analysts and information on the systems they're responsible for. I would like to have an Excel macro that will:
1. Create and name a new worksheet for each UNIQUE value in the 'Name' column (new worksheet for each analyst) 2. Copy their system info to each respective worksheet
What I have is a sheet that is copied periodically from some source sheet, and on this sheet is a table. This sheet is called "Onsite Checklist Template" and it's table is titled "Checklist". I also have another sheet called "Loggers and Initial Notes" which has a tabled titled "Record", and then finally a title sheet call "Proj Details".
To clear this intro up - The sheets, in their order, is: "Proj Details", "Loggers and Initial Notes", "Onsite Checklist Template". The tables: "Record" on "Loggers..." and "Checklist" on "Onsite..."
When the user wants to make a new site visit, he/she fills in the requested date and then selects a button on "Proj...". When this button is selected, it copies the table data on "Record" and puts it on "Checklist", then inserts a new worksheet tab, always in the 3rd position (the title is based on the site visit date in which the user entered), which is a copy of "Onsite...". Now we have another sheet with a table called "Checklist1", and upon another new site visit, there will be another worksheet with "Checklist2", and so on.
On the "Onsite..." worksheet, there is a button on it which also gets copied with the worksheet so that every new worksheet has this copied "Checklist ???" and this button. I'm looking for a macro that, when the button is selected, will bounce the active sheet's table "Checklist ???" off of "Record" and make changes as needed.
"Checklist ???" data range is B11:M20 (the header is on row 10); "Record" data range is B29:Q78 (the header is on row 28); Column headers are titled the same, just that "Record" has 4 extra columns, 3 in the middle and 1 on the end. "Checklist ???" columns 1-12 to "Record" columns 1-7, 10-12, 14-15. The search criteria is the 4th column in both tables ("Trk #").
I need the macro to do the following:If it finds a match, then update "Record" as needed with data from "Checklist ???", changing whatever cell is different in the row that contains the matching "Trk #", so long as the cell on "Checklist ???" is populated (i.e, if a cell on the target row of "Record" has a value, but it's blank on "Checklist ???", then "Record" wins; if it's blank on "Record", but populated on "Checklist ???", the Checklist wins. If both populated but different, then Checklist wins.If a "Trk #" exists on "Checklist ???" but is not on "Record" then add the line to "Record" (the 1st empty row, table size remains)I see no need for any "delete" at this time.
My concerns: The last column on record (column #16) is the filtering column for the worksheet copy event and needs to be left alone (it's formulated to produce a "Yes" or "No")I would like the ability to adjust table sizes if needed without modifying the macroThe table rows on "Checklist ???" will not be changed, deleted, or altered in any way by the macro.
I have a worksheet that lists all employees (past and present) (Worksheet A). I have another worksheet that I am sent from our training department that lists monthly test scores (Worksheet B). The worksheet containing the test scores only contains current employees, and the employee list changes from month to month.
Is there any way to have Worksheet A match up employee John Q. Public with the row for John Q. Public's in Worksheet B? I would like to be able to import the test scores automatically regardless of which row the employee in on in Worksheet B.
I am trying to divide a table of data (7 Columns, 500-1000'ish rows) into individual worksheets based on any unique values in column. There may be anywhere from 10-30 instances of the same value in Column C, and I need to bring all rows of each unique instance into a newly created worksheet to be labelled the same as the value in Column C.
The RAW data will be updated monthly by keeping a template file copying and pasting data, therefore I am hoping to have a "SORT" button with this Macro assigned to it. I have quickly thrown together a sample workbook with how the RAW DATA will be pasted into the file, with additional sheets showing how I would like the data split and organised.
In Sheet2 I have a list of products with their description in adjacent columns such as prices and other information. Sheet1 is a MasterSheet of some of the products listed in Sheet2. So I would like to write a macro that links the information of the product from Sheet2 to Sheet1 if I type the products name in Sheet1 (MasterSheet). The list of products (Sheet2) is updated in a regular basis, so by linking the info I would just need to update Sheet2 and Sheet1 would update as well.
I essentially would need the Master Project Sheet, to have instructions to say. Research the folder of invoices, when you find project number 001 and plumbing (job performed), then grab C1 and D1 off of the contractor invoice and place it into the corresponding project name on the master, in the case above B2 and C2. Is that possible?
I have created a spreadsheet that I must fill out daily. this worksheet has averages that must be automtically shared with a "master" worksheet.
for example information from sheet2!b23 information from sheet3!b23 information from sheet4!b23 etc... must automatically be transfered to sheet1!c6 sheet1!c7 sheet1!c8 etc... in that order
at this time I am typing in =sheet2!b23 on sheet1 everytime, I want to avoid this.
If you are asking yourself why? I dont blame you but it needs be this way so I can average each days spreadsheet.
I have a Master list of kids names and age groups, that will be used by Coaches to update, from this i would like to have each of the different age groups details extracted into different worksheets within the same workbook.
I used an IF statement to but found it was leaving blank lines between finds.
The "On" worksheet represents ALL of the students that LIVE on campus (freshman, sop****re, juniors, seniors)
The "JS" worksheet represents all the juniors and seniors going to school here
I need to know which of the JS live OFF campus.
So.....I need a macro that will compare the JS worksheet to the On worksheet and create a new worksheet called Off and populate it with those JS that are not in the On worksheet.
The "On" worksheet contains:
A B Last First
The "JS" worksheet contains:
A B C D E F G Last First Street Street2 City State NY
The new "Off" worksheet should contain the same columns as "JS"
I'm trying to build is a master Excel sheet for a company-wide budget tracker, where our supply person enters in information on individual orders. Those orders would be broken down in separate worksheets based upon department.
So, let's say you have three departments. Each department has an identifier code (Human Resources would have HR, Operations would have OP, and Research & Development would have RD.) Each order number is prefixed with the department's code, then the other columns deal with dates ordered and received, cost of the order, and any notes on the order.
What I'd like to be able to do is to have each order be automatically filtered out to secondary worksheets, based upon which department the order belonged to.
I have seen a macro that allowed one to push a button and break data out like that, but the problem is that it created new worksheets each time, and I want the department worksheets to stay the same (since each of those can expect to have starting budget figures updated by the supply officer.)
I want to make a popup or some type of inquiry menu where it asks the user to specify which worksheets in the workbook to print. I have a data entry page, and four printable worksheets.
Sometimes I only want to print two of the worksheets, but not the data page or the other two. Any way to do this besides printing the entire workbook or one worksheet at a time? It would be ideal if a popup menu came up with checkboxes for all of the available worksheets and the user could check off which ones to print.
I have a template with 25 very hidden sheets in it, my userform, unhides and slightly modify's some of the sheets. When my userform is completed, there are between 2 and 11 sheets visible. I am trying to put code behind a button on a sheet that will save all sheets to one PDF file. The code below works but makes individual files.
select all active files as pdf
I know it's close but the code to select all active sheets is messed up.
I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.
I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.
I need to create a page (call it a dashboard) in excel whereby drop down fields (doesn't have to be drop downs, just how I imagine it to work at the moment), are used to refer to information in another worksheet, which updates several charts on the dashboard.
So, for example, I have several additonal tabs in the spreadsheet called 'Company 1', 'Company 2' etc, which in each tab contains information (monthly results) in exactly the same layout and format. However, on the front page, I would like to be able to select 'Company 1' (or 'Company 4') and then sub-select 'March 2014' results within that, referring to the data in the 'Company 1' tab. If possible, it would be handy to have this information displaying in a grid on the dashboard, which updates when you change between months and/or company. This could be the source data for the chart.
