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...
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
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 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 a routine that highlights a selected Active Cell,
but when I printout the spreadsheet this highlight is not printed.
I have been pull my hair out (what's left!), to try to show this highlighted cell when printed.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Range("d22:u45"), Target) Is Nothing Then
Range("d12").Value = " Out"
Range("e14").Value = "0"
Else
If Intersect(Range("d22:u45"), Target) >= 3 Then
Range("d12").Value = Cells(19, ActiveCell.Column).Value
Range("e14").Value = Cells(21, ActiveCell.Column).Value
Static rngPrev As Range, PrevColor As Integer
Dim TempColor As Integer
TempColor = Target.Cells(1, 1).Interior.ColorIndex
If Not rngPrev Is Nothing Then
If PrevColor <> xlLineStyleNone Then .......................
I have a workbook that need a macro to print current active cell
and the surrounding 6 rows 4 cols.
Also the selection printed need to fit
to page pref landscape on printing
the active cell varies day to
Is there a code I can put on commandbutton to show Print Preview on active sheet in excel 2010 ?
View 3 Replies View RelatedI am trying to write a macro that publishes the print area as a non-interactive web-page to a file that sits in the same location as the spreadsheet from which the macro is being run.
I am using the the following macro:
How do I write a vba code to print active sheet to pdf, auto save in specific folder then mail using outlook
im using 'Nitro PDF Creator'
also id like it to automatically save the pdf as the same file name as the active worksheet and also auto enter the following when opening the email server:-
Address: Cell D18
Subject: Cell D21
Email body:
find attached quotation
Optional installation available on request
Trying to put together a macro that looks down active sheet for all cells that contain a value, sets a print area and then prints !
Is this possible?
FYG, I have a column that run from 3 - 2000, which contains a formula, which may produce a value depending on corresponding cells.
I used this code from a post on a similar topic, but excel is complaining code
in bold
Private Sub Print_Area_Click()
Dim lastCell As Range
Set lastCell = Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0)
Do Until Application.Count(lastCell.EntireRow) 0
Set lastCell = lastCell.Offset(-1, 0)
Loop
ActiveSheet.PageSetup.PrintArea = Range(Cells(1, 1), lastCell).Address
End Sub
I have a big spreadsheet with a number of sheets, some with dynamic lists which take up a lot of processing power, but which I only use rarely. Consequently the whole spreadsheet is running very slowly. Is there a way to set certain sheets to not automatically update, but retain automatic updating on others? I know you can set Calculation to Manual in the Options, but I need to retain automatic calculation on some sheets.
View 9 Replies View RelatedI have a rather big worksheet with lots of sheets in it. I need to see in the frontpage how many sheets there are in total (-2, the frontpage & end sheet don't count) and how many sheets are currently shown (ie. not hidden, as there are vba-filters which sheets to display).
I don't really mind how it is shown, it could be one cell with 14/53 meaning 14 sheets shown of a total 53 or in seperate cells.
I have a macro that runs when the workbook is saved, it starts to enter in certain formulas onto the worksheet, how can I have this maro run only when 2 specific sheets are active
So basically I want the macro to run if either worksheet "Sales Jan-Jun" is active or worksheet "Sales Jul-Dec" is active, if they are not the active sheets I want to exit sub
This is what I tried without success
If ActiveSheet ("Sales Jan-Jun") Or ActiveSheet ("Sales Jul - Dec") Then
Exit Sub
Else
Is there a code I can use that will only have autocalc functioning on the active sheets? If not, how about for only the current Files that are active?
View 1 Replies View RelatedI have a workbook that has a sheet for every day of the month (1 to 31).
When it opens it automatically activates the sheet for the day corresponding today.
What I'm missing is how to enable users to only update that sheet.
Example today is the 1st, sheet 1 should be unprotected but all others protected and off course tomorrow it should be sheet 2.
I want to create a macro to check through all sheets in an active work book, if a sheet is empty, then delete it?
View 2 Replies View RelatedI have a large workbook where the tab colors differ. I don't want the first few sheets or the last dozen or so to sort but I want to be able to activate/group numerous sheets and then run a macro that will take those sheets and sort them by color.
View 4 Replies View Relatedis it possible to keep the same active cell when switching sheets in the same workd book. I am in sheet1 and cell C15 is my active cell. When i move to Sheet2 it either defaults to A1 or the last place i was in this sheet. I guess it would be the Worksheet_activate() and deactivate function but not sure
View 3 Replies View RelatedThat particular question has been solved, but now i need it to work with multiple values from combobox.
So for example,
if "Master" is selected in combobox3, it will delete sheets 7, 8
if "CSR" is selected in combobox3, it will delete sheets 1, 8
if "Original IND" is slected in combobox, it will delete sheets 1,7
The current code will work when "master" is selected, however i am getting compile errors when i select "CSR" or "Original IND"
WhenI select CSR, it does create a new wb for me (without sheets 1,8) correctly but i get a Run time error.
Run-time error '-2147417848 (80010108)': Automation error
The object invoked has disconnected from its clients.
When Original IND is selected, i get a compile error and it highlights 'Make the new workbook active newWBK.Activate
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
code to pull up all the sheet from closed workbook to active opend workbook.
Closed Workbook name : Create Position
Active Workbook name : EIB builder
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 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 RelatedI'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 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 RelatedI 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 need a macro that will print a hidden page that is linked to the page I have active.
It needs to be able to find the correct hidden page that corresponds to the active page because there are multilple pairs of the hidden/visible pages. The name of the hidden page is the same as the visible one with "printout" added to the end. When created, the sheet code names are sheetn and sheetm; n and m being consecutive numbers.