Use Macro To Print Spreadsheet

Mar 29, 2007

Trying to summarise large (but varied # of rows depending on which project is being reported on) table for specific values. I get the size of the sheet by:

FinalRow = Cells(65536, 1).End(xlUp).Row
FinalCol = Cells(1, 255).End(xlToLeft).Column

As all the search criteria are numbers lists from out ERP System, a 'For' 'Next' loop seems to work on the Auto-filter settings, and only display the matching values. As I am only trying to get the number of occurrences, and not do anything with the data in the sheet, the 'Count' function seems to work pretty well:

'Change to " proper" dates and insert TTC columns
Sheets("Data").Select
Columns("C:G").NumberFormat = "d/mm/yy;@"
Columns("F:F").Insert Shift:=xlToRight
Range("F1").FormulaR1C1 = "TTC"
Columns("F:F").NumberFormat = "0"
Range("F2").FormulaR1C1 = _
"=IF(AND(RC[-1]>0,RC[3]=2),RC[-1]-RC[-2],TODAY()-RC[-2])"
Range("F2").Resize(FinalRow - 1, 1).FillDown
Range("A1").FormulaR1C1 = "=COUNT(R[1]C:R[500]C)"
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)). AutoFilter

How can I replace the '=COUNT(A2:A500)' with '=COUNT(A2:A[FinalRow])' and only count the rows I need to? This sheet can change from between 20 rows up to 50K+. I understand that this may look messy, but have only been playing with VBA for about a month, and I shall tidy it up later.

View 4 Replies


ADVERTISEMENT

Print A Spreadsheet.

Nov 2, 2009

I wanna print a spreadsheet. but i want the first 5 columns to be the header on every page.

View 2 Replies View Related

Macro To Print Sheets With Value In Cell A1 But Print Dynamic Ranges On Certain Sheet

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

Print Out Spreadsheet In Dotmatrix Printer

Aug 28, 2006

I have developed one file for invoice printing using VBA coding.

When i take print out from excel workbook the print out is comming in Windows Font (i want like Dos prompt printing).

I want to take printout in Dot matrics printer for invoice printing and the print out has to come like Lotus123 spreadsheet print out (dos mode).

is it possible to get print out like Lotus123? If possible can u please tell me how to change the printing property in excel spreadsheet.

View 3 Replies View Related

Copy Data From A Row To Spreadsheet And Print

May 4, 2007

I have a list of names in column "A" worksheet1. I need to copy the name from "A1" sheet1 to "A10" on sheet2 Then print a range called "Document" from sheet2 Then get the next name "A2" sheet1 etc. down Until it reaches a " " in column "A" sheet1

View 7 Replies View Related

Stop Print Macro Firing Before Print Event

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

Excel Question Mark In Box When Print Spreadsheet?

Nov 16, 2011

My spreadsheet looks normal and I've been using it for months. Today some of the text characters print out as boxes with a question mark. I tried another spreadsheet with the same result. I reset the printer and re-booted my pc with no luck. I did try the Q&A and used =CODE(MID(A1,1,1)) which resulted in 68. But I don't know what to do next.

By the way, the spreadsheet does not show the question marks, only the print out.

View 1 Replies View Related

VBA - Extract Data From MSN Money And Print Into Spreadsheet

Sep 1, 2012

I am trying to write some code that will extract several pieces of data from MSN Money and print into a spreadsheet.

So could outline the code (or the general idea) that would be needed to extract one data point from the internet (an example using MSN money would be great). I haven't been able to find a simple example that highlights how this works in VBA without the extraneous confusion.

To be clear, I don't want to use macros or the standard excel query tools as these won't, I believe, scale well and serve my purpose as I build up into extracting much more data.

(For example, I want to know how to extract 2011 revenue from this page: [URL] ........)

View 7 Replies View Related

Excel - Add Background Picture To Spreadsheet That Only Shows On Print Area

Aug 29, 2012

I need to add a background picture to a spreadsheet that only shows on the print area.

This spreadsheet is being used as a pro forma invoice that can be emailed to customers for them to view & print as required.

At the moment I can add a background image but this appears throughout the sheet and not in the place I need it to i.e. the print area.

View 4 Replies View Related

How To Print Multiple Pages Of Single Page Spreadsheet With Unique Invoice Numbers

Nov 12, 2012

I have a single page spreadsheet. I want to print multiple copies and have a unique invoice number on each page printed in cell O1. I don't want to just send it to the printer as individual print jobs. I would like to send it to print as a pdf in a single 100 page document or if not a pdf then just as a multiple page document.

For example first print run would start at number 1001 and last numbered page is 1101.

View 6 Replies View Related

Option To Print - Use Print Macro

Dec 30, 2008

I have a few macros that run in a sheet. After the macros are finnished I would like to present to the user a msgbox that asks if they would like to print. if "Yes" is selected, the print macro runs. If "No" is pressed then nothing happens.

View 3 Replies View Related

Disable Print But Allow Print From Macro

Jun 11, 2007

I have found a macro which disables all print features in excel. I have rwo other codes to print. When I put this macro, It also disables the print from the other macro. How can I use this code so that normal print features will be disabled but if someone takes the print from the macros, Print will be allowed. The code which disables the print is given below:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If PrtOK Then
Cancel = False
Else
MsgBox "Can't print from here!"
Cancel = True
End If
End Sub

View 9 Replies View Related

Programmable Print Area: Macro To Set The Print Area According To The Amount Of Data In A Particular Range Of Cells

Feb 25, 2009

Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction

View 2 Replies View Related

Macro Fixes Macro: Copies Tabs From The Spreadsheet

Jun 26, 2007

I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.

View 2 Replies View Related

Using If Within Print Macro?

May 28, 2014

The print area for each of the rows (summary tab) on the attached has been set up, though using button 3 we need to run the same print macro but ignore any rows (summary tab) that happen to be 0.

The attached sheet only gives two rows but there is a potential for up to 40 of them!

Would an IF function within the macro/code work?

View 14 Replies View Related

Macro For Print Out

Feb 6, 2010

I need a macro using a command button in sheet 2 that opens the "Print" window to print out the text in sheet 1 (after chosing printer manually of course). I also want a radio button in sheet 2 so I can select if I want to print out page 1-3 instead of page 1-2 in sheet 1.

View 2 Replies View Related

New Print Macro

Dec 12, 2006

What I would like to do is when I click on the print botton or select Print from the File menu, I would like to have a dialog box pop up with a YES botton and NO button asking the question "Would you like to print out the Medication Summary Page?". YES indicates to print off an additional worksheet and NO indicates just print the active sheet.

In the active sheet I have dropdown list in B6 to select a drug name to calculate IV medication drip rates. I also have other worksheets Named the same as in the list. For example, if you select D5W in the list, I have a worksheet named D5W. If you select YES in the message box, I would like to have the Active Sheet and the Medication Summary page print, ELSE just print the Active Sheet.

View 9 Replies View Related

Fix My Print Macro

Jul 24, 2007

I'm close - but I want the 'user' to be able to enter the number zero to either of my prompts without causing the macro to fail. Currently, if a user does not want to print any color copies (i.e. enters 0 - or B&W for that matter) the macro fails. Any ideas?

Sub Print_Report()
Dim CCopies, bwcopies As Integer

Copies = Application.InputBox("How many color copies?", Type:=1)
Copies = Application.InputBox("How many B&W copies?", Type:=1)
Sheets("YTD").PrintOut Copies:=CCopies, ActivePrinter:= _
"network_address_goes_here:", Collate:=True
Sheets("YTD Graph").PrintOut Copies:=CCopies, ActivePrinter:= _
"network_address_goes_here", Collate:=True
Sheets("YTD").PrintOut Copies:=bwcopies, ActivePrinter:= _
"network_address_goes_here", Collate:=True
Sheets("YTD Graph").PrintOut Copies:=bwcopies, ActivePrinter:= _
"network_address_goes_here", Collate:=True
End Sub

View 9 Replies View Related

Macro Print

Sep 7, 2007

I am wanting to add to my macro a print function.

I have a list of names in Col F

And the Cell A1 powers the data on the sheet.

I want the macro to say if C1 is then make C1=A1, if C1="",stop
and I want that to go on for C2, C3, C4, etc.

View 9 Replies View Related

Print To PDF Macro

Aug 20, 2009

I'm trying to write a macro which prints to PDF and I've been looking through all the posts currently on this forum to get something working. I'm using the following code -

Sub PrintPDF()
Filename = "C:Documents and SettingssambMy Documents" & ActiveSheet.Range("Z1").Value
SendKeys Filename & "{ENTER}", False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Adobe PDF:", Collate:=True
End Sub

The macro prints to PDF, but then it stops at the Save As stage, where I have to manually enter the name of the file and click Save. I obviously want the macro to automatically name the file with the contents of cell Z1. I then want it to then automatically press enter.

View 9 Replies View Related

VBA Or MACRO PRINT THE JOB

Sep 30, 2009

I have a worksheet with blank tables on 2 template tabs named viz: ON BOARD and NOT ON BOARD, each with 3 sheets to print when viewed on print preview.

What I want is just print ONLY those sheets which have data filled in., i.e if I have data on 1 sheet out of 3 sheets, I want only 1 page to be printed, if there is data on 2 sheets then print only 2 and so on, from both the sheet tabs.

On Page Set up window, I have following specs:

On Page Tab: As follows

Orientation: Landscape

Adjust to: 75 % of normal size

Paper size: 8.5 X 14" Legal

On Sheet Tab:

Print Area A1:O51

Under Print titles:

Rows to repeat at top: $1:$6

These settings are standard for both the sheet tabs.

View 9 Replies View Related

Using Macro To Print Sheet

Jan 13, 2010

I am using a macro with the code below to print the active sheet.

Sub MyPrintOut()
ActiveSheet.PrintOut
End Sub

I assigned the macro to a rectangle. My only problem is that when i print the sheet i can see the rectangle, is there a way around this? I know that i can set the print area but each sheet has a different setup, i tried using .Visible = False for the rectangle but it gave me a few errors.

View 2 Replies View Related

Macro To Print Selection

Nov 20, 2008

I seem to be getting myself into a complete muddle as was wondering if someone to help me with a Printing problem.

On the attached sample, i'm trying to write a macro to print the selection but only print where the status is "Active" to the ned of the selection. However, the spreadsheet is changing on a daily basis i.e. new lines being added and lines being taken off.

So, to take potential of user error out i wanted a macro to print the "active" selection.

View 9 Replies View Related

Print Button Macro ..

Nov 24, 2008

I have a workbook with approximately 260 sheets. These sheets contain financial information. They have to be sorted according to the division within the company, with a total page for each financial division.

I have a code on a blank tab that prints the sheets based on a value in cell B3 on each sheet. (i.e. "VB", "WB", which are initials of our district managers.)

View 6 Replies View Related

Print Range Macro

Jun 10, 2009

setting up a print range macro:

I have a sheet where:

1) column A (rows 1-54) needs to be printed with every printing
2) other columns (b-e) or (f-i) etc need to be printed out with each printing

Is it possible to setup a macro to do this as long as I know which columns need to be printed every time (will be same number of rows every time)

Basically, there is info in column A that needs to be printed every time, then I want to be able to print out a months worth of data (b-e for january) for each printout. It is essentially a calendar that I need to print up a hard copy of.

I know zero about vba and much of what I have found on the internet has only confused me more.

View 14 Replies View Related

Print Macro To Fit Page

Apr 1, 2012

I have a macro to print out page 9 but what i whant it to do is to print and fit to full page. how do i do that?

Code:
ExecuteExcel4Macro "PRINT(2,9,9,1,,,,,,,,2,,,TRUE,,FALSE)"
With ActiveSheet.PageSetup
.FitToPagesWide = 1
.FitToPagesTall = 1
End With

View 1 Replies View Related

Print Macro In Landscape

May 29, 2013

i have the follow code below to print data from A2 to to the last row ihn Col Z (Hidden rows to be excluded)

I can't get the macro to print in Landscape. I also need tpo print with gridlines

Sub Print_all()
Application.ScreenUpdating = False
Sheets(1).Select
Dim LastRow As Long
With ActiveSheet.PageSetup
.Orientation = xlLandscape
End With

[Code]...

View 3 Replies View Related

Macro To Set Print Area

Jul 20, 2007

Is there a macro that can automatically set the print area by determining what the used range is. For example, If P96 is my last used cell, I would want a1: P96 as my print area.

View 9 Replies View Related

Using Macro To Set Print Area

Oct 11, 2007

My macro selects cells containing data - then i want the macro to set the print area to those selected cells - works fine - but the selected cells won't be the same each time i run the macro. Here's how it looks now:

Range("A1:M1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$M$76"

The next time i run the macro the selected cells won't be A1:M76 - how do i have the macro set the print area to whatever is selected?

View 9 Replies View Related

Print Macro Button

Oct 16, 2008

I need to know how to make a box that says "print" which will print out pages in my excel file... What I am picturing is this:

Tab 1 Y
Tab 2 Y
Tab 3 N
... etc.

Making a table as the one above, and depending on whether I input "Y" or "N", it will print that tab...

i've seen this in some financial models and would like to incorporate it into my model. If you could offer some detailed help, it would be greatly appreciated. Please note that I am a complete beginner and would need to be taken step-by-step.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved