Viewing And Printing Selected Worksheets - VBA
Sep 3, 2009
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.
View 9 Replies
ADVERTISEMENT
Feb 28, 2008
i want to create a menu in the worksheet named "Main Menu" that lists all the other worksheets within the workbook with the option to select the required worksheet and view or print it.
View 7 Replies
View Related
Mar 29, 2009
I have a seemingly simple dilemna and wonder if there is a solution... I am not a PRO user, but can get by with my limited knowledge of excel.
My issue:
I create invoices for my business and in the invoice I use the "TODAY()" function to automatically insert the current day when I created the invoice.
Now when I need to go back and look at the old invoice or print it again it shows the CURRENT date, not the original date when it was saved. Is there a way to view and/or print out a file while keeping the original date intact or is there a better way to format a date to avoid this happening in the future.
I have since eliminated the function and just type in the date to avoid this but I have about 100 invoices that are saved that I may need to view their "original" dates on.
View 11 Replies
View Related
Oct 30, 2011
I have large workbook with various sheets. In my first worksheet I have some command buttons that call each sheet as needed. However, I would like each sheet to be displayed either as a web page or in a way that the user does not see all of the menus, bars and so on.
View 1 Replies
View Related
Jan 3, 2007
Here is what I try to achieve:
- I have a "Menu" worksheet with basic Hyperlink to Sheet1, Sheet2 etc.
- When I click on a link (or on the tab), I want a prompt asking for a password (different for each tab). I do not anyone who doesn't have the correct password to see the content of the worksheet.
(I don't know VBA and I couldn't modify the code to fit my needs)
Here is the code posted
Dim sLast As ObjectPrivate Sub Workbook_Open() 'Ensure Sheet1 is not the active sheet upon opening. If Sheet1. Name = ActiveSheet.Name Then Sheet2.SelectEnd
View 9 Replies
View Related
Dec 1, 2009
I would like to adjust the code found in the attached thread so that i can allow one person access to more than 1 sheet
Password Protect Viewing of Individual Worksheets?
View 9 Replies
View Related
Nov 6, 2009
I have a file w/ individuals monthly sales information (per worksheet) that needs to be distributed in one mass email to the entire sales force. I want everyone to have access to the "Summary" tab, but individuals should only have access to their own "Details" tab. I know there has to be a way to accomplish via VBA. However, I'm not proficient in writing code.
View 9 Replies
View Related
Nov 6, 2013
I have a data sheet which I need to print everyday, I need to print Column A plus other individual columns on separate pages. For eg. Column A + B, Column A + C, Column A + D etc until the last column. Besides hiding and unhiding, is there any way to do it via vba? For eg, pop up to ask user which column to print?
View 6 Replies
View Related
Jul 28, 2014
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
[Code]....
how I can modify this code to print just the selected worksheets?
View 6 Replies
View Related
Jul 26, 2009
I have a bunch of workbooks i need to print from a particular sheet each time, which is always called 'calculation'.
View 8 Replies
View Related
Aug 6, 2013
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.
View 6 Replies
View Related
Jan 10, 2010
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?
View 9 Replies
View Related
Jul 15, 2014
I am trying to disable printing in an Excel workbook that has 3 worksheets. I do want any worksheets to be able to print.
View 2 Replies
View Related
Jan 19, 2013
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.
View 4 Replies
View Related
Jul 29, 2011
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.
View 2 Replies
View Related
Jul 15, 2009
I have a workbook, see attached example, which has multiple columns. I want to extract the data for a certain criteria, in this example column E "product".
I then want to take all of the data in columns A to L for the chosen criteria e.g. product 1696 and place it in a new worksheet. I want to do this for every unique product. The example I have given only shows 2 products and limited rows, in reality I could have 50-60 products with hundreds of rows per product.
View 5 Replies
View Related
Nov 6, 2013
I am trying to work out how to copy all data from worksheets that begin with the name 'Sheet' and paste that information onto the next available blank cell in a workbook called 'Results'. I have found how to copy information from all worksheets to 'Results' but not from selected worksheets that begin with the name 'Sheet'.
View 6 Replies
View Related
Aug 4, 2006
How can I count the number of selected worksheets in VBA? I've been looking in the Excel object model, but with no avail. Perhaps I'm overlooking something simple.
View 2 Replies
View Related
Jan 8, 2007
I would like to do is to sort only selected sheets. Can someone give me this additional code that can be incorporated in the code below. (If I just select the sheets I want sorted and run the code below, it sorts all worksheets irrespective of whether it is active or not).
Sub SortWorksheets()
Dim N As Integer
Dim M As Integer
Dim FirstWSToSort As Integer
Dim LastWSToSort As Integer
Dim SortDescending As Boolean
SortDescending = False
If ActiveWindow.SelectedSheets.Count = 1 Then
FirstWSToSort = 1
LastWSToSort = Worksheets.Count
Else
With ActiveWindow.SelectedSheets
For N = 2 To .Count ................
View 7 Replies
View Related
May 21, 2008
I am setting up a macro where the user opens their chosen file & their chosen worksheet which gets renamed & entered into my workbook. I can get them to open a workbook but I am having problems with the user being able to choose a worksheet and copy it over.
View 4 Replies
View Related
Nov 29, 2013
I have an excel file with over 20 worksheets and each of them have around 1200 rows. The first column in each worksheet contains the variable names and then the data associated to it is present horizontally.
I only want to keep around 80 rows from those 1200 rows. They are not in sequence (means they are not in continuous order) so I manually selected those rows by deleting the non required rows step by step.
I did it manually on 2 worksheets but I don't want to do that manually over 20 worksheets. Is there any method that can speedup the whole process.
I am attaching the snapshots of the worksheets..
This one is before I deleted the unwanted rows.
This one is after the deletion of unwanted rows.
View 3 Replies
View Related
Jun 25, 2014
To this point I have been able to successfully write code that will save a constant set of worksheets as a pdf. However, I would now like to alter it to be able to dynamically select the desired worksheets from a list box (I have been able to populate my list box) and then save as a pdf. The last step is where I am have issues. This is what I have thus far..
Dim relativePath As String
Dim Selected As Long
For Selected = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(Selected) = True Then
Sheets("Summary").Range("Q65536").End(xlUp)(2, 1) = ListBox1.List(Selected)
ListBox1.Selected(Selected) = False
[Code] ..........
View 6 Replies
View Related
Aug 9, 2007
I have a macro that changes user selection from formulas to values:
Dim vCol As Variant
vCol = Application.InputBox("Select Column", Type:=2)
If vCol = False Or vCol = "" Then Exit Sub
Set UserRange = Range(vCol & "9:" & vCol & "35")
UserRange.Value = UserRange.Value
End Sub
I have several workbooks that use this macro, and the workbooks can include several sheets.
Is there's an easy way to change the macro so the user selection is changed in all sheets in the workbook. E.g. if the user selection is column H, the formula is changed to values in all sheets in the workbook.
View 4 Replies
View Related
Jun 22, 2008
I have a workbook with 30 some sheets. I would like to have a macro that if i select multiple sheets, will paste the values and formats of those sheets into a new workbook. I would like this to keep the names of the worksheets when transfered to the new workbook.
Thus far, I have been using this code, which does nearly everything i want, but instead of pasting the values, it gives me #VALUE! for nearly all the cells. Most of the cells are using Vlookup and/or Indirect functions to reference other sheets- not sure if this is relevant. The few cells that do paste accurately are either text or simply reference another cell on a different worksheet without a function.
Here's my code.
Sub PasteShtVal()
Dim w As Worksheet
ActiveWindow.SelectedSheets.Copy
For Each w In ActiveWorkbook.Sheets
With w.UsedRange
.Value = .Value
End With
Next w
End Sub
View 9 Replies
View Related
Jun 22, 2014
I have a workbook with over 70 tabs whose position shouldn't be changed. Some of these tabs are colored in yellow (sorting by tab color is not allowed). I need to select these yellow tabs first and loop through them (only yellow tabs) and hide empty rows in the range of A1: G 50 on each of them. Grouping sheets wwon't work because each tab has different last row with data within that range.
View 6 Replies
View Related
Feb 6, 2013
I have a workbook with a series of worksheets with stock lists and pricing that I would like a user to be able to select items on (say, with a checkbox) that would then automatically populate a separate master Order Form sheet. The Order Form sheet is currently blank, with headers, and I would like only those items selected on the various stock sheets to be displayed on the Order Form.
View 4 Replies
View Related
Jan 15, 2012
I would like to know if there is a way to print several different workbooks at once but keeping my printing format which I would like to be Landscape and Fit to one page. Reason is simple as I work in a office where staff is handed in several jobs to do everyday. They finish the jobs and log all the info on the database. I log on to database and put all their daily diaries and because it is all over the place I have to go in each file and set printing preferences which takes an hour in the morning and hour in the evening. I could do with some sort of automation where all diaries are automatically printed in Landscape and Fit on one page.
View 2 Replies
View Related
Apr 11, 2008
I have a report that is generated from a manufacturing process that looks like the example below. the report is 40 pages long when all the data is printed. i am looking for a way to only print this range if a dimension is "out of tolerance". if the dimension is within tolerance, there is always the "garbage" text of plus and minus. if every row is "within tolerance" in the range the cells in the OutTol column would all contain the "garbage" text but it will not always be identical. so, in summary, actual OutTol values = print and all "garbage" = not printed.
NomActDevLoTolUpTolOutTol
Y-0.956-0.9480.008-0.0030.0030.005
Z-1.413-1.4130.000-0.0030.003---*|++++
DIA0.4220.4240.002-0.0030.006----|+*++
POS0.0160.0110.005
View 9 Replies
View Related
Sep 24, 2009
Is there a way so that on his computer he can tell Excel that each sheet to be viewed should have a standard/automatic view set to 100% or another percentage?
View 3 Replies
View Related
Oct 22, 2008
I've done the following steps so far:
Tools
VBAFormatProperties
Checked the "Lock Project from Viewing"
Applied a password
and clicked Ok
View 3 Replies
View Related