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.
.PrintTitleRows = "$1:$1"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
ActiveWindow.Zoom = 80 ' This line works!
Selection.RowHeight = 13.5
Selection.ColumnWidth = 34 ' This line works
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click() Sheet7.Activate Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .RightFooter = " Construction Assumptions" .PrintArea = "CONSTRUCTION" 'the named range to print .Zoom = False .FitToPagesTall = 1 .FitToPagesWide = 1 'this changes depending upon the page selected End With ActiveSheet.PrintOut End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate 'Sheet3.ResetAllPageBreaks 'disabled due to errors Run "HorizontalPrintStuff" 'generic landscape pagesetup With ActiveSheet.PageSetup 'specific pageset settings .PrintArea = "DEVBGTALL" 'the named range to print .FitToPagesWide = 4 'this changes depending upon the .FitToPagesTall = 1 End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
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.
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.
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?
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.
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)
I am trying to write what I thought would be a simple macro to print out specific areas of my worksheets. I have shown the code below; the line causing the problem I have highlighted in RED. I am getting the following error message: "Select method of range class failed".
Reading other posts here. I think this may have something to do with the macro being assigned to a command button in one worksheet (AY114) and I am trying to get the macro to run on both the worksheet that the command button is in (AY114) as well as another worksheet (AY062).
I am trying to find a way to select and print multiple sheets based on a criteria or a list. I have a large worksheet with many sheets. Each sheet falls into one of three categories, and I want to be able to automatically print all tabs in each category. I have all of the sheets rolled up into a summary where I have access to all of the sheet name and print criteria.
I am trying to build a macro to work with a template file.
The template file has set sheet numbers and names (with one exception, see 4) below).
There are close to 40 sheets in all. Some sheets are never printed. Some sheets are always printed. Many sheets are printed only if they are used. Most of the sheets are 'break out' sheets and a variable number of them will be used.
So... Here is what I'm trying to accomplish.
1) Always print Sheet4(Overview1) 2) Always print Sheet6(Overview2) 3) Print Sheets 11 thru 40 IF value in cell G50 on these sheets is > 0. Note that this cell has a name (Total) and I would like to reference the name if possible. (It could happen that further evolution of the Workbook moves the cell up or down a row.) 4) Print Sheet38(Data Sort) IF it exists. Otherwise, print Sheet1(Data). If the information on the Data sheet is limited (eg only 1 or 2 pages), we print it off directly. If the information is more extensive, we copy it to a new sheet and sort it (Data Sort).
I can achieve 1) and 2). I'm not quite sure how to go about 3) as I'm still not very good with macro loops. 4) I haven't tackled yet.
I am working on a "3 worksheet" excel workbook. The first worksheet does not require any header.
I'd like to enter data into the second sheet (say cells A1 and B1), and use VBA to pull from those cells to generate the same custom header for both the second and third worksheets.
For example, I'd like the header to pull "# 123456" from cell A1, and "789" from B1 in sheet two, putting them in a centered header for both sheets two and three (same reference cells from sheet two for both, not new values of A1 and B1 from sheet 3 for sheet 3 header). I'd like to format in a way that looks something like this:
I'm currently running Excel 07, and was able to pull from a cell on one worksheet into that sheet's header but couldn't get it to span multiple sheets.
All sheets are identical in that they have the same headings within the same range.
I want to apply formatting (bold some text, apply borders, center, etc.), but to all the sheets at once. I know I can do this with a simple loop, but I was wondering if there is a more efficient way of doing it.
I've been playing with sheet( array(sheet1.name, sheet2.name))
I don't think I have correct syntax or use using the the above.
I tried coding what I would do if I was performing this manually, which is to select all the sheets and apply the formatting to sheet1. Then all the sheets would have the formatting.
Currently, I have 65536 rows of data per sheet in an excel file. I have a total of 8 sheets. I need to combine all these sheets into one csv file. As my company uses excel 2003, I can't consolidate all sheets into 1 sheet before saving as CSV format as it will exceed the number of rows available in excel 2003.
However, the number of rows and number of sheets in the excel file will change monthly, and I need to consolidate the excel file accordingly.
I am wondering if I could save a new workbook as csv format first before copy and paste all data into that csv file.
Also, all the 8 tabs have the same header. I only need to copy the header on the first tab, and for the sequence tab, I will only need to copy row 2 onwards.
I have recorded a macro based on a sample data.
Sub Macro1() ' ' Macro1 Macro ' ' ActiveWorkbook.SaveAs Filename:= _ "C:Documents and Settingse31425My DocumentsBook1.csv", _ FileFormat:=xlCSV, CreateBackup:=False
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
I have a workbook that contains something like 50 worksheets and they all contain data with the same columns, for example column A is Project, column B is Project Name, etc. I need to convert data in each worksheet into an Excel Table. There has to be an easier way than manually converting each worksheet into a Table. However, when I group all of the sheets, the option to Insert a Table is not available even though the data starts in the same row and contains the same number of active columns in each worksheet. Is there an easier waty to insert a table in all of the worksheet simultaneously?
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.
I've seen a few threads on here about this issue but none of them do quite what I am looking for. I'd like for a single page "report" to be created when a user presses a button (which runs a macro, of course) The macro should be able to run through certain named sheets (even if hidden) and if a cell in any row is red within a sheet then the entire row or rows that meet the criteria should be copied and pasted into the Report sheet.
On the report sheet, for each sheet that has had rows that were copied, I'd like to have the name of the sheet as the header above the pasted rows so that the user knows which sheet the data came from. Any sheet that doesn't have red cells would be excluded from the report. I've attached a sample file but had to limit the number of sheets because of Orgrid's file size limit. Hopefully, you'll see what I am getting at here.
I have a spreadsheet with 28 visible tabs. When I select all tabs and print, I get 3 cover (or banner) pages...I like having the first banner page, but the other two are in the middle of my report. I assume excel is sending 3 print jobs and I'm not sure why.
I've seen a couple of posts regarding this issue when using VBA, but I'm not using VBA...just selecting all the sheets and sending a single print job, but get 3 banner pages.
I'm using VBA code to select several worksheets at once, and print them. The code runs fine, but the result is multiple print jobs, not one job. This is OK when printing to the printer...but undesireable if you want to print to a pdf file, for example.
I've searched the site and found some references to this possibly being an excel problem. Is that true?
I've also checked the page setup setting to make sure that the print resolutions are all the same...that doesn't appear to be the problem either.
Apparently someone set up Excel on this computer to print in what looks like a booklet format and how to get it out. I just want my print/page options to be default so I can print this sheet out regularly.
I am using VBA to copy and paste multiple sheets from one workbook to another. The workbook where the sheets are being pasted has no prior formatting. I want to transfer the *print* formats from the first to the second sheet via VBA. One key thing to note is that the print formats are constantly being changed in the first workbook. So I think I need code to "grab" the formats, then transfer them to the second workbook.
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?
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 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.
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.
I'm trying to print an excel workbook in a particular format. I have several sheets. I would like the information on each sheet to be duplicated directly to the right of the info, on the same printed sheet in Landscape layout. After printing, I need to be able to stack the workbook and cut straight down the middle. This way, I have two identical workbooks with identical margins, perfectly centered.
Is there an easy way to do this without copying and pasting the cell data into the spreadsheet as well?
If there is not, and I must have duplicate tables on the same worksheet, is there an easy way to format the printing so that they print out perfectly aligned?
I've tried to use the ruler function in the print page to stretch, but, it only stretches a particular row/column.
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.
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
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
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