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
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'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...
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 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.
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 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.
Creating a macro to compare 2 sheets in a workbook and print the differences to a 3rd sheet.
Each sheet will have the same number of fields, 5 columns with the header in the first row.
All values in the cells are integer except for the last field which will be a character.
The key is the value in the 2nd column. If it's not in the other sheet, then it's a new record. If it's a new record then highlight it a color depending on what sheet contains the new record. Now if the key is the same in both sheets, then check the other columns to see what's different. If there is a difference, print the record for both sheets in the third sheet and highlight the differences. I attached a sample of what I want.
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 have a "submit" button macro which user would click after he has finished his input. This macro would update a reference number on the worksheet named "orange" and then print out this worksheet.
My purpose is "orange" is printed out with a reference number.
Each employee has a different plan time each day of the week. Mon - 9 Tues - 9 Wed - 7 Thur - 8 Fr - 7
Overtime is really counted after 40
There are 5 sheets for each day of the week mon-fri lets say sheet1..2...3...etc, on each sheet there is a column a with plan and column b with actual hrs. What I'm looking for is a summary sheet for each employee to see where they trend in OT after each day so...
person1 works 10 hrs on mon with a 9 hour plan thus 1 hr trending OT. On the summary page person1 would now show 1 hrs of OT. Now if they would work 9 hrs on tues it would still show 1 hr of OT. On wed however they worked 6 hrs so now the summary page would 0.
I want to add a twist to the sumIf-function, and need some help. My request is similar to lots of other sumIf-issues posted here, which were resolved with the sumproduct-function, but is a little bit different.
I would like to place a formula in cells A2 down that does a running count of unique combinations of columns B and C.
For example, the first record is a white dog, so at that point there is 1 category of dog, next is a brown dog, so there there is now 2 categories etc.
I initially tried to adapt an array formula 1/COUNTIF($B$2:$B8,$B$2:$B8) but this wont quite do the trick.
I can delete duplicate rows easily enough, but I can't seem to capture a couple variables where I would not want to delete the selected row. There are no headings, all data starts in Row 1.
I start in Row 1 Columns C and D, compare that to Row 3 Columns C and D (Every other row). If it is a duplicate entry in both columns, delete that row and the row below it. Pick up at next row and continue. See attached Excel file (Excel 2003). The rows with fonts colored RED are the only rows from this data that should get deleted.
I have a spreadsheet that has 36 columns and 6000+ rows. Each column has a mix of blanks and content, which is an X. See example of the pattern below, column A. I want to run the COUNTBLANK function through the entire column A so that it assigns a value in column B, next to the content it relates to. Line B4, B9, and B11 would have results, in the example below. I want to be able to apply a formula the entire worksheet, all 36 content columns, and over 6000 rows, so that I can obtain the results quickly by filling down, across, etc. I suspect a loop might be in order. Currently, I have to run the COUNTBLANK between one X and another, repeating that tediously over and over again.
---A----------------------------B 1 X 2 blank 3 blank
I have a spreadsheet with three columns (check #, invoice #, vendor #). If a check pays multiple invoices, there is a separate row for each instance of invoice #. (ie if a check pays 10 invoices, there are 10 rows all with the same value for check # and vendor #, but different invoice #s). I need to concatenate the multiple rows to just one row per check #, with all the invoice #s in separate columns on the same row and delete the duplicate check # rows.
However, I can only use 10 columns, so if there are more than 8 invoices matching the same check #, the 10th column will need to contain invoices 9-n concatenated and separated by a space. I have over 5000 rows in the spreadsheet.
I would like cell CM2 to look at cell G2 and get the value there. Here is where it gets interesting. I would like it to reference sheet "LookupTables" in column A.
Cell G2 will be a 4 digit number.
Cells in column A of sheet "LookupTables" will be formatted like this: 3 digit number-4 digit number-4 digit number, like this "001-0001-0001".
If cell G2 exists in column A of the LookupTables sheet, (but it needs to ignore the first 4 digits and the last 5 digits of that number) then it should return that value in cell CM2. I would like it to look through the entire column A of the LookupTables sheet and return all numbers that match the above criteria, and separate them with a comma.
So, if G2 is 0001 and column A of the LookupTables sheet contains several values including:
001-0001-0006 004-0001-0187 098-0001-0874 098-0002-4356 (it should ignore this since the middle numbers don't match the value of G2)
then CM2 should display: 001-0001-0006,004-0001-0187,089-0001-0874
Also, if there are no matches, then it should just return a blank cell.
I have a workbook with many different worksheets containing data. I am looking to find a certain city name in each worksheet and then replace the region name immediately to the left of the city name.
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
I have a few macros that run in a sheet. After the macros are finnished I would like to present to the user a msgbox that asks if they would like to print. if "Yes" is selected, the print macro runs. If "No" is pressed then nothing happens.
I have found a macro which disables all print features in excel. I have rwo other codes to print. When I put this macro, It also disables the print from the other macro. How can I use this code so that normal print features will be disabled but if someone takes the print from the macros, Print will be allowed. The code which disables the print is given below:
Private Sub Workbook_BeforePrint(Cancel As Boolean) If PrtOK Then Cancel = False Else MsgBox "Can't print from here!" Cancel = True End If End Sub
Using Excel 2003 I am trying to write a macro to set the print area according to the amount of data in a particular range of cells. I find I can include this instruction
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 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
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 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?
