Print Array Of Sheets
May 20, 2008
I 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?
View 9 Replies
ADVERTISEMENT
Aug 4, 2009
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.
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
Dec 1, 2013
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 Related
Jul 14, 2014
Subscript 9 error that Ive been getting when I run this macro. It has worked in the past, but when I tried to test it today I keep getting errors on the array portion.
[Code] .....
View 3 Replies
View Related
Jul 11, 2013
I have an array and I want to graph it. I don't know how to do that so I am trying to print it to a sheet and graph the data that way.
This is my array:
blkhist(service, 1) = DateDiff("d", blkstart, rawdata(currentdate, 1)) 'x axis values
blkhist(service, 2) = rawdata(currentdate, 16) ' y axis values
blkhist(service, 3) = blkcart 'series #
View 9 Replies
View Related
Mar 19, 2014
I have 9 named ranges on worksheet Sheet1. I want to print every combination of every non-singular range on worksheet Sheet2. Below is a simplified version of the scenario.
There are three named ranges: Letters, Colors, Animals. Say the below are the entries for each range.
Letters = {A, B, C}
Colors = {Red, Blue}
Animals = {Dog}I want to print every combination of Letters and Colors but exclude Animals since it only has 1 entry.
Therefore my result would look something like this:
A Red
B Red
C Red
A Blue
B Blue
C Blue
My thought is to make a multidimensional array GrandArray where GrandArray(1) = Letters and GrandArray(2) = Colors, then recursively go back through every combination and print to Sheet 2. I can set up GrandArray, but stepping through each element is creating mismatch errors.
I'm trying to avoid For loops since my real data has 9 ranges which may or may not be included in the final print.
View 1 Replies
View Related
Jun 14, 2006
The code prints a series of sheets. Afterwards these sheets are "group"ed together. The code can be accessed from 2 different sheets. I need to figure out how to get rid of the grouping and return to the original sheet.
Private Sub CommandButton1_Click()
Sheets( Array("Pipe", "Pipe Flow")).PrintOut
UserForm1.Hide
End Sub
View 2 Replies
View Related
Apr 25, 2009
I 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
View 9 Replies
View Related
Jun 2, 2008
how do i create a sheetsarray to include all sheets between First and Last? I plan to loop through each sheet in this array to copy data to a summary page, as per [url]
View 9 Replies
View Related
Jul 17, 2013
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 Related
Apr 5, 2009
I'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.
View 8 Replies
View Related
Oct 22, 2011
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...
View 3 Replies
View Related
Apr 19, 2008
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 Related
May 28, 2008
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 Related
Aug 20, 2009
Is 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
View 9 Replies
View Related
Sep 9, 2006
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...
View 9 Replies
View Related
Oct 29, 2006
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
View Related
Dec 5, 2007
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.
View 4 Replies
View Related
Jan 6, 2014
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"
View 4 Replies
View Related
Mar 16, 2009
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 Related
Nov 9, 2011
with 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
View 1 Replies
View Related
Feb 27, 2012
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]....
View 6 Replies
View Related
Apr 14, 2014
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
View 7 Replies
View Related
Jun 23, 2014
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.
View 4 Replies
View Related
Aug 19, 2009
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.
View 9 Replies
View Related
Feb 15, 2010
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
View 9 Replies
View Related
Jun 13, 2007
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
View 2 Replies
View Related
Oct 16, 2007
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 Related
Jun 11, 2008
I 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.
View 21 Replies
View Related
