Print Macro Based On Cell Values
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
ADVERTISEMENT
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
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
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
Feb 13, 2008
I have a range "RangeOne" and i want to check the range row by row.
In each row I want to check for value in second cell. If the value is 10 or more then the entire row in the range should be selected and then execute a macro. If the value is 0 to 10(but not 10 exactly) then the row in the range should be selected and then execute macro2.
View 3 Replies
View Related
Jan 8, 2014
a macro/code to accomplish the following:
I would like to insert a row if:
Difference in values in Column B are more than 2 .... OR ...Difference in values in Column C are more than 2
Specimen
Value 1
Value 2
[Code]....
In the above table, rows would be inserted after specimen A, B, D, and E.
View 3 Replies
View Related
Feb 20, 2009
I might be using something completely wrong here, but can anyone tell me if this code is possible or am I being very naive.
H4 and I4 are cells in which I want to enter dates, and then I want these dates to be used in a custom filter on another page.
Below is what my limited understanding of VB came up with.
Sub Date_Range()
Dim First As Date
Dim Last As Date
First = Range("H4").Value
Last = Range("I4").Value
Sheets("Graph Data").Select
Selection.AutoFilter Field:=1, Criteria1:=">=First", Operator:=xlAnd _
, Criteria2:="
View 9 Replies
View Related
Aug 22, 2009
I am trying to copy two cells from one worksheet to another in the same work book based on the value in Column B of one of the sheets. This is just a building block to a larger script I am going to create.
Below is my attempt but I keep getting an error at Range(Cells(x, 3)).Select.
View 7 Replies
View Related
Jan 23, 2013
I have recorded a macro to sort a range of cell based on two values that is dependent on time in another cell. I now want the macro to run automatically when refreshing the workbook with F9, so as the time changes so will the sorting. Everything works fine except the sorting doesn't refresh when F9 is refreshed.
My recorded macro is:
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, FillCT As Long
For X = 1 To 4
If Cells(Rows.count, 1).End(xlUp).Offset(0, X - 1) <> "" Then FillCT = FillCT + 1
[Code] .....
View 4 Replies
View Related
Aug 3, 2009
the post 5 for the actual issue. This being my first post could not update it correctly. I have put my views int he 5th post which will be more clear.
View 9 Replies
View Related
Dec 19, 2013
a macro to insert rows based on certain cell values in column A.
I have uto 300 rows of data. Below is an example of column A.
R1
1
2
3
4
5
6
7
[Code]...
If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.
If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.
(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).
I have excel 2007
View 5 Replies
View Related
Aug 3, 2014
I have an Excel sheet that contains a few thousand folder paths in the first column. The first few cells in the column look similar to below.
C:UsersNameDesktopFolder Copy12010360
C:UsersNameDesktopFolder Copy12010361
C:UsersNameDesktopFolder Copy12010362
C:UsersNameDesktopFolder Copy12010363
C:UsersNameDesktopFolder Copy12010364
C:UsersNameDesktopFolder Copy12010365
I am trying to create a macro that will copy any folders that exist in any of the paths listed in the first column. The folders should be copied to the path of the open workbook containing the macro. Below is the macro I have currently, much of which was taken from information I found in this thread [URL]....
VB:
Sub wrapper3()
x = 1
Set fs = CreateObject("Scripting.FileSystemObject")
While Sheets("Air").Cells(x, 1) <> ""
v = InStrRev(Sheets("Air").Cells(x, 1), "")
dest = ActiveWorkbook.Path & Mid(Sheets("Air").Cells(x, 1), v, 99)
[Code]...
This code seems to work fine if all of the folders exist to be copied. My problem is that some do not and it is creating a "Path Not Found" error for which I need a fix. If the folder doesn't exist at the path nothing should be copied and the next path can be evaluated.
View 2 Replies
View Related
Jun 12, 2009
This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):
1) I want user to input starting cell. So I need to create a button for that.
2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.
If the value is greater than original cell then logic needs 2 outcomes:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.
View 2 Replies
View Related
Apr 13, 2006
I am trying to create a macro that opens a .pdf with the name of the pdf equal to a cell value. For a simple example: Cell "A1" contains the text "distinct" and I want the file prexisting file "C:distinct.pdf" to open when the macro is ran.
View 3 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
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
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
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
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
Feb 27, 2008
I have someone to setup a macro for me to print from 1 to 100, but I would like to set a criteria print only if the cell(A1) = "A" then run the macro. How can I modify this macro?
For a = 1 To 100
Range("C1").Select
Selection.Copy
Range("B5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next a
View 9 Replies
View Related
Oct 18, 2006
I have a "submit" button macro which user would click after he has finished his input. This macro would update a reference number on the worksheet named "orange" and then print out this worksheet.
My purpose is "orange" is printed out with a reference number.
Below is the 1st code.....
View 9 Replies
View Related
Nov 3, 2008
There's a macro I'm trying to figure out for a calendar I'm working on. Here's what it has to do.
1.) Selects the cells for the current month (I assume the user would have to click on the current month itself) plus the two upcoming months. i.e. November, plus December and January.
2.) Sets the selected cells as the area to print, then prints those cells.
If there's anything that might be a challenge, the numbers for the days are all text boxes, if that makes a difference. I've been experimenting with different formulas with no luck.
View 9 Replies
View Related
Feb 21, 2007
a macro to print a series of worksheets only when a certain cell (probably would have to be a named cell as lines may be added to some of the worksheets at some stage) in that worksheet exceeds zero.
View 9 Replies
View Related
Mar 18, 2007
I have a workbook that need a macro to print current active cell
and the surrounding 6 rows 4 cols.
Also the selection printed need to fit
to page pref landscape on printing
the active cell varies day to
View 9 Replies
View Related
Nov 13, 2013
I am trying to print one of the Sheets in my Workbook by looking at a specific cell that can have one of the sheet names.
View 7 Replies
View Related
Jun 29, 2007
I need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.
View 3 Replies
View Related
