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 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 to write a macro where i need to copy set of rows from few columns of an excel sheet to another set of columns in same sheet . My excel looks something like this...
Product
F1020
F1023
F1025
F1120
F1123
F1125
[code].....
Now when i filter this table for Product PR01 only rows 1,3,4 will be visible while the other rows remains hidden
I WANT TO COPY ROWS COMING UNDER COLUMNS
F1120
F1123
F1125
TO
F1020
F1023
F1025
when i use the code
Selection.SpecialCells(xlCellTypeVisible).Copy
i get to select ones those are visible but i am not sure how i can PASTE them to rows visible under column f1020 to f1025
Tried this in a frantic effort
Selection.SpecialCells(xlCellTypeVisible).PasteSpecial xlValues
But got an error for " multiple selection"
I have a form to allow users to select and print pages from a workbook.
Private Sub UserForm_Initialize()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Worksheets
ListBox1.AddItem sht.Name
Next sht
Me.Height = 128
End Sub
Only problem is that this lists all worksheets in the form. Need to modifying it so that it does not list worksheets that are xlSheetVeryHidden.
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'm trying to create a userform that allows the user to swap sheets from visible to hidden status. I've made it to the point where all the sheet names are populated based on their current status in their respective listbox, but I am stuck on using the results of any swaps to newly set their visible property. the code I have in the userform is as follows:
View 2 Replies View RelatedHere is what I think I want to do (however there may be a better way to get to the end result).
Macro 1:
Unhide all worksheets however, first list only the worksheets that are visible.
Macro 2:
I want to be able to hide all worksheets except those in the above list.
My intent would be to have these in the personal file so I could run either process on any file I have open so I think I would need to insert a tab for the list when unhiding & remove the tab after hiding.
I have found many strings unhiding all & hiding all except a named sheet... but can't find anything on the above?
with creating vb code that will select certain rows in a spreadsheet, set the print area to those rows and print them out, and then reset the print area back to default.
The data is all on one sheet, but is split into 5 different areas, each below the next, with the column headers at the top of each.
e.g.
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
header_____header_____header
data_______data_______data
data_______data_______data
data_______data_______data
and so on. At the press of a button it'll set the print area to the first section and print (the section includes the headers as well as the data). Then at the press of another button it'll set the print area to the next section. There could be any amount of rows of data in each section so it needs to cope with that.
I am using Excel 2010.
I found code from the internet attributed to Bob Phillips for VBA to have a dialog box pop up and allow the user to select what worksheet to navigate to.
The code works well except it doesn't exclude hidden or very hidden worksheets.
Code:
Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select worksheet to goto"
'dialog caption
[Code]....
I know there is the line of "If Sheets(i).Visible And i Mod nPerColumn = 1 Then..." but it still allows all sheets to show up in the dialog box.
How it can be modified to include visible sheets only?
I'm trying to retrieve the worksheet names to a specif cell and populate from that point only with the names of the visible sheets, but It keeps returning only the last visible sheet name and not the sheets that are visible. Where the code is failing - I'm self learner and start with VBA macros a couple of months so I'm still learning how to identify when the error show up
Code:
Sub Summary()
Dim wks As Worksheet
ultl = Sheets("Control").Cells(1048576, 4).End(xlUp).Row
For i = 31 To ultl
[Code] ..........
I was looking at the message board about what i want but i didnt find it as a whole
What i mean is, that i found the sum of the visible cell only or the sum of a condition
Anyway what i want is:
I hide some rows using a vba code and i want to sum what is left under condition
I have 5 columns , the 2 have the interest.
The column e5:e5000 have values
The column f5:f5000 have specific text --> ("On","Off","Other")
I want ,using VBA, to know, its time i run my the code, the sum of the visble cells under the "On","Off","Other" condition in 3 cells
eg. E1 will have the sum of the "On"
F1 will have the sum of the "Off"
G1 will have the sum of the "Other"
Right now I am at Column D; From E to I columns are hidden in my worksheet and would like to move to Column J.
View 7 Replies View RelatedI have some code to put a GIF animation into a web browser on a userform. This works fine - on its own. However, once the GIF has loaded I want to run a whole bunch of code in the background. If I run the code all at once, the userform appears but the picture doest show - but when I place a breakpoint between loading the userform and the main sub routine of my code - the GIF loads and the animation is shown all the time my code is running in the backgorund.
View 4 Replies View RelatedI was working on a presentation for work where I wanted to build a 'Family Feud' type board to play a game. In the board I am using activex text boxes with code that would hide the text box to reveal the answer underneath or it would show a custom shape (an X in a box) and play the buzzer sound. The code is pretty simple, first I would make the shape visible, then I would play the sound then the shape would be made invisible. If I step through the macro everything works fine, but when I run the macro, you never see the shape. It's almost as if the sound plays before the shape shows up and then it is made invisible again. I tried putting a wait and a sleep command between making the shape visible and playing the sound but that made no difference. If I remove the code to hide the shape at the end and run the macro, the sound plays and then the shape appears. Is there anyway to have the shape appear prior to or at the same time as the sound plays?
Code:
Private Sub CommandButton8_Click()
Application.ScreenUpdating = True
ActiveSheet.Shapes("First Strike").Visible = True
Play_Strike_Sound
'The previous line refers to another macro that has the code commented below. The sndPlaySound32
'function is one I picked up from cpearson.com
'sndPlaySound32 "C:\_Fin SysSoundsff-strike.wav", SND_SYNC
ActiveSheet.Shapes("First Strike").Visible = False
End Sub
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've used VBA to make a picture appear when all the correct answers have been entered and it all works well. However, not to be too mean to 15 year olds, many of my students can be devious little feckers, and I want to hide and password protect the VBA code so that they can't just change the pictures visible section to true. I can password protect the workbook and worksheet, but not the VBA.
I'm entering the Visual Basic editor and I can see my simple script. I then click Tools - VBproject properties - protection. I'm clicking the "Lock Project for Viewing" box then filling in the password and confirming the password and clicking OK. But I can still see and edit my script, despite protecting the sheet and workbook.
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 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 Related3 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'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