Print A Report Based On Cell Value
May 25, 2009
I'm looking for the same thing, this great macro by Leith Ross looks for the word "buy" in kolom A starting at A2. Now this all works fine until i change the value of the colum into a if statement : =IF(C2>0;"buy";"NR"). See the attachment on what i mean. If i run the macro now i get error 91 : objectvariable or blockvariable With is not set.
I can work around it, if i hardcode the A2 with "buy" and then A3 etc with the if statement i works for some strange reason. But there must be a smarter way of doing it, that's why i came here .
View 2 Replies
ADVERTISEMENT
Aug 30, 2013
I have two worksheets in my report cards:
1) Data - the students are listed in Column A, math scores in Column B, reading scores in Column C and science scores in Column D. The grades of 300 students are entered in this sheet.
Student
Math
Reading
Science
Jimmy
75
84
100
[code].....
2) Report Card template - This is the report card that needs to be generated for each student. It's pulling the student name and grades from the Data worksheet.
Student Name
=Data!$A2
Math
=Data!$B2
Reading
=Data!$C2
Science
=Data!$D2
How do I create worksheets (report cards) for additional students? I have 300 students in the school. I need the next worksheet to reference Data!$A3. I know how to cut and paste the report card template and then edit =Data!A2 to be =Data!A3 to create a report card for Sally. How do I create the 300 report cards I need?
I've been teaching for 13 years. I can create a report card for each student in my class and edit each worksheet individually for each student. Now I've been asked to do this for the whole school and I don't know how to create the other 299 sheets I need.
View 1 Replies
View Related
Jan 16, 2010
how to print one report page for every row on a sheet. So if I have 300 rows containing personal info like name phone email I would like to print the data out in a separate formated report for each person.
View 3 Replies
View Related
Mar 14, 2014
I have n rows on a tab and I have counted them using Offset and Counta and put that count on B1.
I now need to make a little macro that print from row n to row number in B1. I've seen macros using things like "For i" "Next i", etc... but I do not know how to set the macro to start at a cell with a number n and repeat till the number in B1.
View 9 Replies
View Related
Aug 28, 2006
It does everything I need it to do but I would like it to sort 3 columns and then merge the data. I have included a sample with a "WishReport" tab the shows what I am looking for. If possible (not imperative) I would like it to add a "unit cost" column and provide a grand total.
Option Explicit
Public Const SZ_INPUT As String = "SheetData"
Public Const SZ_OUTPUT As String = "Report"
Public Const I_FIRST As Integer = 0
Public Const I_LAST As Integer = 1
Public Const I_DATA As Integer = 2
Public Const ROW_HEADER As Long = 1
Public Const ROW_DATA As Long = 2
Public Const COL_FIRST As Integer = 1
Public Const COL_PROJECT As Integer = 2
Public Const COL_LAST As Integer = 5
Sub CombineProjectData()
Dim lInputRows(I_FIRST To I_DATA) As Long
Dim lFirstInstance As Long, lOutputRow As Long
Dim szData As String
Dim iCurrColumn As Integer
Call FindRowArray(lInputRows(), , COL_PROJECT)...............
View 5 Replies
View Related
Jul 8, 2013
I am trying to COUNTIF some data from a spreadsheet I have, where they have been entered after a certain date (which is part of the information). The problem I am having is that I am trying to enter the date I want as a variable in another field so that it can update the rest of the table.
Basically I have a field where it says "Calculate new business since" and then a date should be entered - in turn the formulas should make the calculations based on that date. Is this not possible?
View 2 Replies
View Related
Feb 8, 2008
I am working on building a better report manager using named ranges
1.) Loop through the workbook and find all named ranges
Output the named ranges to a worksheet
2.) Read the values from the worksheet and print each one
Seem simple enough and is really just first step
Would also like to store all print settings next to each named range to be modified by user as needed and used to format report before printing each range as needed
But I'm getting ahead of myself.
First is to print a named range
My code is below
Error msg:
Run Time Error 91
Object variable or With Block Variable Not Set
Debug points here:
Set rngPrint.Name = wsPrnt.Range("B" & lngRows).Value
1. Loop through all named ranges and output to worksheet. This part works fine:
Option Explicit
Sub GetNamedRange()
Dim nName As Name
Dim wb As Workbook
Dim wsPrnt As Worksheet
Dim Row As Long
With Application
. ScreenUpdating = False
.DisplayAlerts = False
.Calculation = xlCalculationManual
End With
View 6 Replies
View Related
Jul 18, 2014
In Column C (C11:C28) I have Sheet Names - Descriptors and then in Column D (D11:D28) I have the "YES" or "NO" for whether the sheet should print There could be blanks rows or rows with just heading text on them.
This project uses Table of Contents and what is on the specific sheet I use a Formula [=CONCATENATE("Agenda1 - ",Agenda1!H1)] on some of the Rows. These all then Hyperlink to the appropriate worksheet. Not all Rows will have the Formula if it is easily identifiable.
The name of the sheet is Either the Name As Is Unless there is a hyphen and if there is a hyphen then the sheet name is the first word up to the space.
I'm sure this is doable with VBA I just don't know how to manipulate the - out of the names and do the appropriate sheet selections so that I can print all of the selected sheets.
My current data looks something like this:
Info Pages Print (Merged Cell Header Row)
Title YES
Implementation NO
<blank row>
Agendas Print (Another Merged Cell Header Row)
Agenda1 - FORMULA NAME1 YES
Agenda2 - FORMULA NAME2 NO
Agenda3 - FORMULA NAME3 NO
... thru Agenda7
Other Print (Merged Cell Header Row)
Other1 - Formula Name NO
Other2 - NO
Other3 - NO
My workbook has worksheets with names: Title, Implementation, Agenda1, Agenda2, Agenda3,...Other1, Other2, Other3
I want to select all of them that have the YES to Print and then send them to my printer.
View 3 Replies
View Related
Aug 28, 2009
I have put together a spreadsheet to help with the creation of work orders. I have a summary page that the user can define which category the work order falls under along with this I have a couple of macros set up that will print a specific work order pertaining to the category they selected. I have run in to a few problems (mostly training users) that I would like to eliminate up front.
I am looking for a macro that I can use to look at the contents of a cell (contains a word that corresponds directly to the name of one of the sheets in the work book) and print the worksheet that has the same name as the contents in the referenced cell.
View 3 Replies
View Related
Apr 3, 2012
I have a code Ive been trying to get to work. It works fine with selecting the print area based off a cell value, it just wont update when ever the value changes.
This is on the Sheet's code
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target Range("A1") Then Exit Sub
Call PrintbyName
End Sub
And this is in a module
Sub PrintbyName()
Dim rng1 As Range, rng2 As Range, rng3 As Range, rngPrint As Range
'Refer to the named ranges
With Worksheets("Permit Page")
Set rng1 = Range("$A$1:$bx$603")
Set rng2 = Range("$A$1:$bx$493")
Set rng3 = Range("$A$1:$bx$493,$a$549:$bx$603")
[code]......
I wasnt sure if I was suppose to fill out where it says "nothing".
View 9 Replies
View Related
Mar 12, 2014
I am trying to set the print area based on cell values, but I keep getting errors when running the macro. It fails where I have highlighted, but rc.Value shows as the proper number.
[Code] ....
View 4 Replies
View Related
Mar 12, 2009
I'm trying to get this macro to work; so that when someone hits the ok to print button on a worksheet titled "TOTALS", if cells C18 and C3 don't equal each other it would prompt the user and cancel the print.
View 2 Replies
View Related
Dec 8, 2011
I'm looking for a way to set the print area based on the value of a cell. I have a worksheet that prints a 12-page document, with data pulled from separate sheets. At the bottom of these twelve pages is an additional 2-page section that I only need in certain instances.
For example, if a cell (let's call it A1) on Sheet1 says "brown," I need the print area set to include these additional pages. If that same cell ('Sheet1'!A1) reads "yellow," however, I don't need to include the 2 pages in the print area.
View 1 Replies
View Related
Jul 3, 2014
I have data (part numbers) in column B. They are alphanumeric, eg 29 EE.
When I receive parts I enter the quantity of pallets received in column C next to the part number in column B.
Sometimes certain parts are not received therefore the relevant cell in column C would be left blank. There are a total of 30 part numbers in column B.
I also have an A4 landscape sheet (when printed) which is formatted into two rectangular blocks (merged cells) with fonts sized 200. The lower section contains the NOW() function and the upper section contains a part number which is entered manually. If I receive 10 pallets of 29 EE I will then print out 10 copies of the sheet with the part number and current date.
The same applies with the next part number 29 HE, if I receive 3 pallets of this part I then edit the part number for the A4 sheet and then print 3 copies.
I would like to be able to just enter the quantities received into column C and then select a macro button to print out all the sheets automatically for each part.
The reason for this is to enable older stock to be used first which can be easily identified with an A4 sheet attached when it is put away in the warehouse racking.
View 4 Replies
View Related
Nov 21, 2006
I need to have a macro or VB that prints a number of pages based on the value of a cell in the workbook.
Example:
A1 = 155
B1 would divide by 50, print 3 pages where B1 = 50 and then print the 4th page where B1 = 5.
View 2 Replies
View Related
Dec 15, 2008
got a great bit of code the other day from this forum to automatically print 'x' amount of copies based on the value in cell (see below)
View 2 Replies
View Related
Nov 7, 2009
On the attached sheet I would like to set the print range and then print based on 1 cell's valve. F9 is the value I would like to use:
If F9=1 I need it to print A1:M43
If F9=2 I need it to print A1:M87
If F9=3 I need it to print A1:M131 and so on.
View 3 Replies
View Related
Jun 10, 2009
There are 20 worksheets in my workbook. For the first ten, I would like to print out the only sheets in which cell E28 = 0. For the last ten, I would like to print out the sheets in which cell T1 = 0. Each sheet has a unique name.
View 9 Replies
View Related
Sep 24, 2013
I've found some code which works to print certain pages with value in cell A1 but I need to print dynamic ranges on some of the sheets as they will have filters on so the rows ranges will be different each time.
So far this is what I have but the dynamic range part is not working:
VB:
Sub Print_All_Worksheets_With_Value_In_A1()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
[Code] ....
View 3 Replies
View Related
Jan 11, 2008
I have two worksheets. Worksheet1 has material number and plant number. Worksheet2 only has material number (along with other columns of info). What I am looking to do is look at worksheet2, find the corresponding material number on worksheet 1 and populate worksheet3 with all the info from worksheet1 and worksheet2.
Now this gets tricky for me because worksheet1 has duplicate material numbers, because there are duplicate plant numbers. I need worksheet3 to produce a row that contains all plant numbers, the corresponding material numbers and the rest of the columns from worksheet2.
View 6 Replies
View Related
May 31, 2014
I am trying to pull a value from a report based on two criteria. I want to get the most recent date that has a value in Col K
1
Col F
Col G
Col H
Col I
Col J
Col K
2
106
ET
Product A
14-May-2014
[Code] ........
I use
{=IF($K$2:$K$60000>0,(MAX(IF($F2:$F$60000=C2,$I$2:$I$60000))))}
On another sheet (I left the sheet names out of the formula to make it easier to read) with C2 as the matching Col F value and it will return the most recent date, May 19th, not the most recent date with a value in Col K, May 18th. The report itself is thousand of lines long and has a couple hundred unique values for Col F.
View 3 Replies
View Related
Jun 20, 2006
I have the following worksheet (see attached). What I would like to do with it to create a report is as follows:
1. Create separate reports for each estimator (KM, JW, WH)
2. Carry over the information in Columns I, A, B & H
3. Each report will filter column I to leave out COMPLETED and just report the number of days remaining is ascending order.
4. Flag data if the days remaining is <=5
5. Generate an email to send out notification of 5 days remaining
6. New report can be generated on any given day
View 9 Replies
View Related
Sep 5, 2007
I would like to calculate the sum of investments based on their expiry date and have the totals per month (and year). I have a table that looks like:
TermInterest rateMaturityBalance
24 Months7.12%11 November 200740,000.00
12 Months7.74%13 November 200750,000.00
24 Months7.05%10 January 200853,889.12
12 Months7.85%11 January 2008120,000.00
12 Months8.02%22 March 200817,000.00
36 Months6.68%30 June 200832,000.00
I'd like to have something like:
Nov 07 90,000.00
Dec 07 0.00
Jan 08 173889.12
and so on...
Admittedly I am an Excel novice, so excuse me if my question is dumb and has a simple answer (actually I hope it has :-) but I have tried to find a solution by searching forums, my books, online help, I tried my luck with sumif and SUMPRODUCT functions, even used the conditional sum wizard, but I can't get it right
View 4 Replies
View Related
Jul 17, 2013
I have 2 sheet in MS Excel (MATTER & REPORT) . Database included at sheet MATTER. Report will be seen on sheet REPORT based on entry date. What formula used to produce report automatically.
View 4 Replies
View Related
Aug 5, 2010
I am looking for code that can generate the report that a user selects.
For example the user clicks reports on the userform. The report userform opens up. The user checks the monthly report button and enters the last day of the month he wishes the report to be for. i.e 7/31/2010 and then clicks generate report. when executed excel creates a new workbook and creates two sheets. the first looks exactly like the master data sheet from the original worksheet but of course with the specified data. the second sheet will populate a summary of the data. I will address that after i figure out how to get the first sheet compiled.
View 1 Replies
View Related
Nov 14, 2009
I need to create a report based on three columns. I have explained in detail about my requirement in the attached sample workbook.
I hope someone will help me to solve the problem through VBA because the items & expenses may vary at times. Hence, I feel a Macro will be the best solution.
View 14 Replies
View Related
Sep 13, 2013
So I am trying to rearrange some cells on a report based of some criteria using an IF formula and getting Blank cells. Here's what I have:
LastRow = Range("A" & Rows.Count).End(xlUp)
With Range("P2:P" & LastRow)
Formula = "=IF($F2=""/FEDERAL EXCISE TAX"",$I2,"""")"
.Value = .Value
[Code].....
So I just want it to identify whats in a cell and populate a related value if the text is present. Do I need to change formatting to find Tex or something along those lines?
View 2 Replies
View Related
Apr 9, 2008
I can generate from my hotel software a list that includes the check in and checkout date for a room- and the number of nights for the stay, and the total dollar amount of that stay. if anyone is willing to look what I have so far, I am attaching the smallest sample set of data I can generate and have it useful
and I have succeded in using vlookup succesfully to transfer the net room amount onto the first night of each stay on the grid.
worksheet "export tape chart" is the data generated by my hotel software-
(except for Col a-- I use this for my two way vlookup match)
I can reorder the output as needed for vlookups & offsets
worksheet "first night rate total NA" is where I have pulled the total room rate into the first night of the stay in a grid
worksheet "first night rate total clean" is just a simple cleanup of the first pulling for iserror.........
View 4 Replies
View Related
Jul 2, 2008
How do you create a macro to copy the information from my weekly reports to a monthly report and be able to update automatically. If you had 4 worksheets (for each week of the month) and 1 mastersheet for the whole month in a workbook. All titles are the same and If you needed to copy all the data that is in the columns, say, A through I, starting with row 4 to however many rows are in a given week. The reports can be made up of numicerial values, text and dates. Let me know if more information is needed or an example worksheet.
View 4 Replies
View Related
Feb 20, 2012
Is it possible to set up the second level of a report filter based on the value of the first report filter. I have a pivot table that needs two report filters: first is the location and the second is the department. What I want to do is have the user be able to select the location and then only show valid departments for that location. I know that this is possible if I set up a form and have the second field be a query against the first. I can create a calculated field but I cannot put that in the report filter area.
View 2 Replies
View Related
