I have a workbook containing over 75 sheets. I would like to print only a select few of those sheets using a macro. I did download the "sheets to print" workbook submitted by Dave Hawley ,and have tried (in vain) to adapt the code so that it does not display ALL of the workhseets in the userform box. I want to limit the sheets displayed in this box to onnly those that I name.
View 8 Replies
I got some code from here on how to display all graph names in a List box and choose which ones to print, but i think some syntax is missing and i'd like to print the charts i've chose, not diplay the msgbox
Private Sub CommandButton1_Click()
Dim intIndex As Integer
Dim strMsg As String
For intIndex = 0 To ListBox1.ListCount - 1
If ListBox1.Selected(intIndex) Then
If ListBox1.List(intIndex, 3) = "TRUE" Then
strMsg = strMsg & "Chartsheet " & ListBox1.List(intIndex, 1) & vbLf
Else
strMsg = strMsg & "Chartobject " & ListBox1.List(intIndex, 0) & " on sheet " & ListBox1.List(intIndex, 1) & vbLf
End If
End If
Next
If strMsg <> "" Then MsgBox strMsg
End Sub
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] ....
I would like to be able to array (group) all the sheets in my workbook that are listed in a Multi Select ListBox on a userform. To be exact I would like to select the required sheets I want from listbox 1, add them too listbox 2, then array (group) all the sheets in listbox2. The code to add items from list 1 to 2 is below, but I am unsure of the code to then group the sheets in list 2
Private Sub CommandButton2_Click()
Dim iloop As Integer
For iloop = 1 To ListBox1.ListCount
If ListBox1.Selected(iloop - 1) = True Then
ListBox2.AddItem iloop
End If
Next
End Sub
Sub ComboBox1_Chg()
For Each Sheet In Worksheets
If Sheet. Name <> "CoverPage" And Sheet.Name <> Sheets("CoverPage").ComboBox1 Then
Sheet.Visible = False
Else: Sheet.Visible = True
End If
Next Sheet
End Sub
It works if I step through it (F8) but the ComboBox doesn't work. It's named ComboBox1, and in the properties the ListFillRange shows all of the names in the list in the ComboBox correctly.
I have a Document with multiple sheets in it and I ask it to print entire document which would be 7 sheets (1 Page printed per sheet) but only the first five will print, then it comes up with a second print queue that I need to use and OK to print the balance of them with. I'm printing To a PDF creation program. it saves and Prince the first 5 pages then asks for another new name of the document to save the next few pages. my PDF Creator works fantastic with any other program. I just need to know how to tell it to print as many sheets as I have to print without it doing that. so I can print all sheets in a single PDF document.
View 2 Replies View RelatedI have 2 sheets that I want to print off, these are called Questions & Answers.
I have the following code to print off Answers, how do I amend this to print off Questions & Answers?
'Print!
.PrintOut
.Columns("A:A").Select
Selection.Font.ColorIndex = 1
.Range("A1").Select
.Visible = True
End With
Sheets("SEARCH").Select
End Sub
I have 23 sheets out of a much larger number of sheets, all in one workbook. How can I print only the first 23 sheets all at once?
View 6 Replies View RelatedI've been feverishly searching for a pre-existing macro to print specific sheets assigned manually by the macro to the windows default printer (or better still bring up the printer dialog box and you can select which printer!)
The macro is attached to an image on Sheet 3 called "Details"
The respective sheets I want to print in succession are Sheet 2 "Letter Of Estimation", Sheet 6 "Labour & Equipment SOR" and Sheet 7 "Labour Only SOR".
Can anyone help me with this please? I've been searching for a while and come up with various bits of code that perform intricate loops based on user input, and other unrelated tasks I can't seem to ween out of the code by myself.
I am trying to select a variable amount of worksheets in the workbook and print just those.
I was trying to do something like...
Code:
Sub print_sheets()
shtcount = Sheets("Master List").Range("A278")
For i = 2 To shtcount
Sheets(Array("i")).Select
Next i
' ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub
...but this wasn't really working...
I have a workbook consisting of about 20 worksheets. I have VBA code that prints any sheet that is used but skips any pages that are not used. This works fine but if there are more than one user printing sheets at the same time on our network printer, the pages get all mixed together and they have to pick through the stack to find their pages. Is there a way that the used pages can be assembled and then all printed as one printjob so that each users pages will all print together simplifying the sorting process.
View 9 Replies View RelatedI know I can print sheets as such:
Sheets(Array(2,4,5,8,9)).printout
I want to populate an array list based on sheet names, but cannot figure out the last line for syntax:
Dim ws As Worksheet, arr() As String
ReDim arr(0 To Sheets.Count-1)
For Each ws In Worksheets
If InStr(1, ws.Name, "Crp-") Or InStr(1, ws.Name, "Reg-") Or InStr(1, ws.Name, "Grp-") Then
arr(counter) = ws.Index
counter = counter + 1
End If
Next ws
Sheets(arr()).printout
As I debug it the array is filled with the proper sheets, but I for some reason cannot figure out the syntax for this line? Does it have anything to do with me declaring the array as a string and using integers as the index?
On right clicking a closed file and if print is clicked, it is opening the file and printing the activesheet only and then closing the file. I want that it should print all the sheets continously and not only the Active sheet. How ths can be done?
View 9 Replies View RelatedIs there any way to only print active sheets
i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets
i would like to print sheet 1 if cell D19 has data in it
i would like to print sheet 2 if cell D61 has data in it
i would like to print sheet 3 if cell D103 has data in it
i would like to print sheet 4 if cell D145 has data in it
I'm in the process of placing a button on sheet#1 that will print any of the 5 sheets in the workbook that have been used... a sheets use is determined by cell I53 being > 0...
I've initially used this code to test I53, but it shows all sheets, regardless of I53 value...
Sub PrntUsedShts()
Dim ws As Worksheet
For Each ws In Worksheets
If Range("I53").Value > 0 Then
ws.PrintPreview
End If
Next ws
End Sub
Ideally, the finished code will send all selected sheets collated as a single print job...
Should I be looking at putting test positive sheets into a sheet array?, then printing the sheet array?
Once again, your valued input is greatly appreciated...
I use templates (.xlt) to generate reports for different organizations. In some cases certain pages in the reports are hidden.
When printing I get blank pages where pages are hidden. How do I not get these pages printed at all?
I do use page breaks to format the reoprts.
3 Sheet Excel document- What i'm trying to do is compare the contents of Column A sheet2, with Column J sheet3.
I would like only the variances printed on Sheet A. So- Sheet A says "The following was found in Sheet2!A, but not Sheet3!J"
Demo excel spreadsheet attached. Comparing "NASC Column A" with "RQ4 Column J"
in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.
View 14 Replies View Relatedwith an event macro. Basically I have a workbook with 11 worksheets and have created a macro that increases the value of cell J3 by 1 in Sheet1. However at present it only works from Sheet5, but I now need it to run from Sheet6 and Sheet11 as well and not from any other worksheets. The code I have is as follows
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.CodeName = "Sheet5" Then
With Sheet1
.Range("J3").Value = .Range("J3").Value + 1
End With
End If
End Sub
I have a workbook with many worksheets. All I want to do is print to PDF those that are visible (I can use just the code name number can't I?) and ignore those that are hidden. It seems simple enough but it will run and do nothing.
Dim sMsg As String, FName As Variant
Dim myArray() As Integer
Dim i As Integer
Dim j As Integer
[Code]....
I'm trying to select multiple sheets and print them out. At this time the code is only printing out the "Work Order" sheet. I'm guessing it's something to do with the PrintOut command trying to print the active sheet and not the array?
Code:
Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
ActiveSheet.PrintOut Copies:=1, Collate:=True
I have also tried the following but it just prints out every page in the workbook.
Code:
Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
Sheets.PrintOut Copies:=1, Collate:=True
I've also tried the PIDOOMA approach with this and failed
Code:
Dim TechnicianPack As Variant
TechnicianPack = Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
TechnicianPack.PrintOut Copies:=1, Collate:=True
In making my label printer, I will need to set the print area for multiple sheets based on a range generated in a cell. E.g. Sheet1 might need cells A1:P1200 set as print area, Sheet2 might need cells A1:G694 set as the print area, Sheet3 might not need to be printed, etc..
I have no problem generating a formula to state what the print area for each sheet should be, respectively, but passing that info into VBA has me really stumped. (Using indirect in the Page Setup menu doesn't work after the value has changed, it changes the value to a static reference, which is bizarre). I suspect I'd want to use the Sub Workbook_BeforePrint, but I'm not 100% on that. I will generally have 3 or more specific sheets selected using a button-triggered macro that gets a cell value of sheet names and selects those sheets.
The following, within a macro prints the sheets requested:
Sheets(Array("Sheet1, Sheet2, Sheet3, Sheet4, Sheet5, Sheet6")).Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
But I need to print only sheets the have data in cell A6. This is due to the footer page numbers (Page Of Page) I only need to print sheets with data in A6 of each sheet.
using a macro to print all sheets in a workbook. During this process each sheet has a print area to print as well. A button will be placed on all the sheets. No matter which sheet you are on I would like it to print all sheets. The code I have works I think, but I would like to not have to go by sheet names in case there are some added later, etc. Also, is there a way to do this without going to each sheet and the user seeing all this? Would like to do it in the "background" so to speak.
Sheets("sheet1").Select
'Range("B2").Select
Range("H1:K1").EntireColumn.Hidden = True
Range("O1:S1").EntireColumn.Hidden = True
Range("V1:W1").EntireColumn.Hidden = True
Range("Y1:AC1").EntireColumn.Hidden = True
Range("L1:N1").EntireColumn.Hidden = False
'Print range column A thru AD
ActiveSheet.PageSetup.PrintArea = "A1:AD84"
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.Zoom = 58
' .FitToPagesWide = 1
.BlackAndWhite = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, collate:=True
Range("H1:K1").EntireColumn.Hidden = False
Range("O1:S1").EntireColumn.Hidden = False
Range("V1:W1").EntireColumn.Hidden = False
Range("Y1:AC1").EntireColumn.Hidden = False
Range("L1:N1").EntireColumn.Hidden = True
Range("A1").Select
I'm trying to write a macro to select all the Sheets in a Workbook, and set some properties [Auto ColumnWidths, Landscape, and Fit to 1 page wide] for all of them.
I don't know the names of the sheets, nor how many there will be - this part is tagged on the end of a long macro that creates new files and pastes various data into them. The code below only seems to work on the Active sheet - not any of the others selected. Curiously, I can set a specific column width for all sheets, but not Auto Widths.
With ActiveWorkbook
Sheets.Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End With
Sheets.Select
ActiveWindow.Zoom = 80 ' This line works!
Cells.Select
Selection.RowHeight = 13.5
Selection.EntireColumn.AutoFit
Columns("C:C").Select
Selection.ColumnWidth = 34 ' This line works
Range("A1").Select
I have 25 pages in a workbook/spreadsheet. On the front page I want to insert a macro button which when clicked, displays a message box presenting a list of pages in the workbook with tickboxes next to them - so the user can tick which pages he/she wants to print. I also want a 'select all' tickbox which when clicked - selects all the pages. However, I do not want a tickbox for all 25 pages in the workbook - only a selection of them. how to write this code so I can specify which pages will be displayed in the print tickbox screen that appears when the macro button is clicked?
View 2 Replies View RelatedI am working on a userform in which user can select diffrent sheets for print.
I want all selected sheets to be printed in a single pdf and it could be great if it not ask for a path to save file.
I have a workbook that has multiple sheets for patients (each named with SSN) with all such sheets having identical structure. I have the VBA code below that prints all sheets. Each sheet will have 7 pages when printed. Is it possible to amend the code so that it prints page 1 and page 3 from each sheets only?
VB:
Sub PrintSpecificSheets()
Dim WS As Worksheet, mySheets()
Redim mySheets(0)
[Code]....
In a school gradebook, there are 7 sheets for grading. In each sheet, student names and other info are in cols B through E, and headings are on rows 11 & 12. Grades are in cols Z11:AB..., AC11:AE..., and AO11:AQ..., where ... would be the last row of grades associated with a student.
My obstacle is that the grading cols contain formulas down to row 80, so Range(Selection, Selection.End(xlDown)).Select goes down to row 80 instead of stopping on the row of the last student's name on that sheet.
Another obstacle is that student records are on every other row (odd rows) starting on row 13. Their grades are in the same row.
I have created a macro that loops through and creates an array of the visible sheets. Now I would like for it to print each of those sheets out. I think my main mistake is in my declaration of the array type, since I have not worked with arrays much before.
how to make the following code operable. Currently when I run it I'm getting a "Run-time error (9): Subscript out of range" error.
