We have just refreshed our entire printer fleet to Xerox printers and as part of the project we have selected all defaults to B&W and duplex.
On a multi sheet excel file, If we click print then choose entire workbook and then change the print options to colour, it prints the only first worksheet in colour, and all the others in mono. i have read that excel treats each worksheet separately and does not cascade the changes made made in the first worksheet even if all sheets have been selected.
How can I get them all to print in colour?
We can set up another printer and change the defauts to colour but we dont want to go down that road and have 2 printers installed for each model.
Is there any code out there can will/ can change the properties of the first worksheet and cascade this throughout the workbook.
We use XP and W7 workstations and office 2003, 2007 and 2010. By the end of the year all workstations will be W7 and Office 2010.
I Have a spreadsheet in which data is spreading into column A-Z. I would like to disable copy, paste, ^V,^R,^C, ^D for certain columns (Q-U) of the workbook so that user is forced to enter data manually on these columns.
I have a workbook of some 15 worksheets - however there is just one of the worksheets I want to disable the printing of it. So that when users select print whole document - all worksheets will print except for thus one worksheet
The worksheet tab is named "15.ADMIN WORKBOOK CONTROL ONLY"
I know I need to put some vba code into the code area of that worksheet
I tried this code but it didnt work
Private Sub Worksheet_BeforePrint(Cancel As Boolean) Cancel = True End Sub
Code that will autofilter my sheet for rows that contain either a 1,2,3, or 4 in Column "Z". Then, create a new workbook for each unique value in column "O" and save that to a folder on my desktop named "Contracts". Next, while still filtered by by "Z" and for every unique value in column "O", filter and name a sheet in the new workbook for each unique value in column "B". Finally, while the main sheet is filtered I need to copy and paste the visible cells (minus header row) from columns C,D,E,F,G,H,S, and U to consecutive columns in the newly created worksheet beginning in cell "A9".
I am using excel to create some contracts for a few partners. Each partner could work with several of my clients and I need to give each partner a personalized contract for each of my clients with which they work.
I hope my description of the problem is not too confusing. If so, let me know and I will try to clarify exactly what I am trying to say.
I have tried to combine some code that I already have but, being a newb with loops, I can't seem to get it to do everything.
I have a table that lists names, eligibility and # of people. That is just one worksheet. I have a total of 30 worksheets in that workbook. The column with the names is completely filled out. I entered a number in the eligibility and # of people columns and Excel copied all three cells (name, eligibility and # of people) to the same cells on all of the worksheets. I have no formula in any of the cells, nor do I have anything linked.
Over a period of time I entered dozens of worksheets into a very important workbook. Finally, I decided to put them in alphabetical order. I found a method/formula to perform the task on line. I made the suggested entry and it ran for some time. After it finished: the worksheets were in order. but since then Excel has been slow and many times unresponsive.
I have five excel separate excel files containing values covering more than 500,000 rows each. I want to put then in a single excel workbook without tedious work of copy/paste to sheets of this workbook.
I want to write a macro that will copy data from all worksheets of a specified workbook and copy them into a new workbook.
To give some detail, I receive a report each morning containing failed deliveries. I also export a list of failed deliveries from a system (SAP). These reports rarely match so I must compare the two daily. I do this using INDEX and MATCH functions but now my boss wants all the data in a single report so I would like to harness the might of vba to consolodate all the data in one workbook.
The lists of failed deliveries are contained in worksheets marked mon, tue, wed... so I need to search all worksheets for all delivery numbers and copy all of the data into a new book. This becomes complicated because on Monday there is only one tab marked mon, on tuesday there are two (mon & tue), one wednesday there are three and so on.
I have started on some code but I am getting nowhere fast. I have managed to muster an input box which asks for a date (this will be used to search the file path for a file named "failed deliveries & "mm/mm/yyy")
I've got some code which some one posted me a link to on here which set up a pivot table on more that 1 sheets (using excel 2002).
I inserted this code into a rountine I do daily which works fine.
The problem Im having is that Im getting more and more info which I need to pivot over more than one sheet. So I was going to amend the rountine so It picked what ever sheets are in the work book to pivot.
At the moment I have to go into the code and name the sheets to pivot. Is there some code which can work out how many sheets that need pivoting and just do it with out naming them? and also just add a new sheet with the pivot on rather than having to name the destination?
I've posted the code i got given below and have highlighted the bit where I have to name the sheets.
I used VBA codes to apply different passwords for different worksheets in a single excel workbook. They worked fine. Then i applied a password to the VBA code itself through the VBA project properties. Also worked.
But my problem is that, if any one enters a wrong password to open any of the worksheets, the VBA will open an error message window and when "end" option is clicked, the VBA code will open without any prompt for the password.Thus the entire purpose is defeated.
What I'm trying to do is print only the selected worksheets from a listbox. I have created a UserForm and a ListBox which displays all unhidden worksheets in a workbook. The ListBox MultiSelect control is set to 1-fmMultiSelectMulti. I would like to be able to click CommandButton1 and send the selected worksheets to print, but going to Print Preview first. This is what I have so far:
Private Sub UserForm_Initialize() 'Displays only visible (non-hidden) worksheets in listbox2 Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets
how I can modify this code to print just the selected worksheets?
i have a program that exports multiple invoices to an excel template for ease of formatting and printing. it can export multiple invoices at once, each invoice being on a separate worksheet. i need for the user to be able to print all worksheets at once and have the pages NOT be numbered 1-30, rather 1-2, 1-4, 1-3, etc.
i understand i can use the header to insert page numbers, but they only function the way i would like when you print each worksheet one at a time. however, there could be any number of invoices to print at once. the only drawback is i am unable to use macros due to security risks/settings.
i would imagine there is some concoction of formulas i could possibly use to accomplish what i need, as i know how many rows of data will fit before excel inserts a page break(56 rows of invoice items, and there are 18 rows besides that repeat on every page). so if i could come up with a way to tell excel when to increment the page number in a cell using a formula of some kind, that would be perfect. or, another way of setting up my template so that it will print page numbers as expected.
I have a workbook containing a number of spreadsheets. Some of the spreadsheets are user inputs. The results of the user inputs drive a number of final reports. The final reports (i.e. spreadsheets) are hidden from the user (I don't want the user to be overwhelmed with so many tabs when they open the excel spreadsheet).
I created on the main input tab spreadsheet the following:
1. Check boxes - so that user can select after making his/her inputs the reports that he/she wants to view or print.
Say there are 4 reports (call them Sheet1, Sheet2, Sheet3, Sheet4 - therefore, 4 check boxes. Through the Format Control, the checkboxes have cell links that yield TRUE (if selected) or FALSE if not selected - linked to cells A1, A2, A3, A4 respectively.
2. Option buttons - one for view and another one for print. Through the Format Control, the View and Print option buttons have cell links to cell A5 yielding 1 for View and 2 for Print.
3. Command button - that will clear the check boxes
Issue I would like to know if there is a way to code in VBA to:
1. Unhide the spreadsheets corresponding to the check boxes if selected;
2. Print the spreadsheets corresponding to the check boxes if selected for printing; and
3. Clear the checked boxes to unchecked if the Command button is clicked.
I'm struggling with coding to perform the above tasks.
I have a workbook containing several sheets. New worksheets may be added. From each sheet, I would like to print the first row in range (AB1:AE200) along with any rows below the first that contains the value “Red” in column AB. From what I have been reading, it would seem that a temporary worksheet would be the answer using a copy/paste.
As each sheet would likely contain only three or four rows to then print, is there a way then to get all the data onto a single page, thereby preventing the need to print a single page for each sheet?
I have a workbook with various pages that are all hidden except the main page, on the main page it allows users to select items froms drop down boxes that returns a figure to cell B7 on the selection page.
What i would like to do is press a command button and the hidden worksheet that relates to that figure in cell B7 opens which allows the users to print it then after printing or closing the workbook is hidden again.
it was possible to disable pasting in an Excel workbook. He didn't want Copy disabled, as he needed to paste data out of this tool into other workbooks, but just wanted Paste and Paste Special disabled while viewing this particular workbook.
Public Sub TogglePaste(booPaste As Boolean) Dim cmb As CommandBar, cbc As CommandBarControl, cbp As CommandBarPopup Dim cbpEdit As CommandBarPopup, cbcPaste As CommandBarControl Set cmb = Application.CommandBars(1) For Each cbp In cmb.Controls If cbp.ID = 30003 Then Set cbpEdit = cbp Next cbp For Each cbc In cbpEdit.Controls If cbc.ID = 22 Or cbc.ID = 755 Then Set cbcPaste = cbc cbcPaste.Enabled = booPaste End If............................
In the Dec 2005 Ozgrid newsletter there is some sample code to disable commandbar controls. The example code crashes when it trys to disable the copy button on the Clipboard menu (Excel 2000) and ONLY the clip board menu. All others are fine.
'Disable all Copy menus For Each oCtrl In Application.CommandBars.FindControls(ID:=19) oCtrl.Enabled = False Next oCtrl
Is it possible to open a workbook via vba, but disable the macros? I know this sounds strange, but the workbook is coded to prevent users from saving it, and the only way to save it is to either open it with macros disabled or change the variable OKtoSave to true, but the variable is global only to the sheet and I can't find a way to change it through a macro written in my personal.xls workbook.