Free Microsoft Excel 2013 Quick Reference

Print Selected Page using VBA

Dear All,
I have 2 Sheets- 1)DataSheet and 2)ReportSheet. The datasheet contains columns like Individual Name, amount etc. The Reportsheet is the claculated sheet derived from the data sheet. The Reportsheet has more than 50 pages. Each Page in the ReportSheet is linked with the Individual Name in the DataSheet. Now,I want to use a print command using a dialog box that selects an Individual Name and print his report page. How can i do this using VBA in Excel 2007.

thanks.
Loknath


Post your answer or comment

comments powered by Disqus
Select sheets using VBA that contain specific word (word from variable).

I want to select all sheets in active workbook whose name contains a specific word. I getting such specific word from variable.

Thanks
Amir

Selecting buttons using VBA
Can anybody help with a small problem I have, I am trying to loop through files in a directory and add amendments. Part of this involves changing the properties of a button on a particular sheet.
The problem is, for some reason these buttons have different names ie “Button18”, “Button 51” etc.
There is only one button on this particular sheet, and I would like to be able to select it regardless of its name.
Here is what I have, but cannot get it to work

Code:
For Each Shape In ActiveSheet.Shapes
Shape.Select
    With Selection
        .Placement = xlFreeFloating
        .PrintObject = False
    End With
  Next


Hello

I have some code that opens the print preview window to do some formatting of the output, but the code will not contimue until the close buton is pressed manually.

Is there a way to close the Print Preview window using VBA so that the rest of my code can continue?

Hi everybody,

I used to have a snippet of code I used to print in IE using Excel VBA. Like a dummy I seem to have deleted the macro that contained that snippet. Does anybody know what that would be. I will post my code (might be useful to someone else trying to do the same thing).

	VB:
	
 billPrinter() 
     'Written By Douglas J Polancih Jr
     '
     'moves down one cell until it encounters a blank
     
     'Opens up view bill on the intranet and enters the value of the current cell.
     'Then prints the bill to the currently active printer
     'On Error Resume Next
     
    Dim IE As SHDocVw.InternetExplorer 
    Set IE = CreateObject("INTERNETEXPLORER.APPLICATION") 
    Do While ActiveCell.Value  "" 'Is cell blank?
        Err.Number = 0 
        IE.Navigate "www.somewebaddress" 
        IE.Visible = True 
        Do While IE.Busy Or IE.ReadyState  READYSTATE_COMPLETE 
            DoEvents 
        Loop 
         
         
         
         'enters pro number and brings up bill
        IE.Document.forms(0).uclLookupPro_txtNumber.Value = ActiveCell.Value 
         
        IE.Document.forms(0).uclLookupPro_btnSearch.Click 
         
        Do While IE.Busy 
            DoEvents 
        Loop 
         
         
        If Err.Number  0 Then 
            a = MsgBox("Pro Number was not found.  Please recheck and enter manually", vbCritical, "Bill Not Printed") 
        Else 
             'Here is where I would like to print the page
        End If 
        ActiveCell.Offset(1, 0).Select 'move down to the next cell
    Loop 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks everybody for reading this.

--Doug

Hi there
I got some very helpful advice from one of your regulars to print only certain columns (ie leave out columns E G I K), but to still have them visible on the worksheet to work on them. The Code works fine.
You can't cant simply use print selected area, because it would thus print it out on different pages.
The problem is it only works when it is run from the VBA editor. I wish to have it working print preview, and by using the normal print button or the normal print command - is this possible.

Here is the code that was given

Sub Print_Sheet()

Range("E:E,G:G,I:I").EntireColumn.Hidden = True

ActiveSheet.PrintOut Copies:=1, Collate:=True

Range("E:E,G:G,I:I").EntireColumn.Hidden = False

End Sub

thank you
Colleen St Claire

Hi.
I have created a program for myself to generate the code for an HTML
pictures page, using about 7 parameters I have specified (width,
height, border, gap etc...). I would like it to be able to check if
the current rownumber in the array (as it is filled in) is in a list
of numbers on my spreadsheet. This is so that I can write into my
spreadsheet the numbers of the portrait orientated pictures, and the
array will put into a box that this picture number is portrait
orientated (important for data later in my code).
I have only been using VBA for a few days, and I have managed to
produce all of my code using help files and google groups, but I can't
find how to do this anywhere.
Also, if possible, I'd like to be able to write my list in one cell -
using the same style as when you print selected pages. ie, 1-12, 14,
16-21 would select pictures 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 14,
16, 17, 18, 19, 20, 21. Is this possible?
Please help!
Simon

Evening all,

Here's my problem
I'm trying to get a macro going that will only print certain pages of a document, in this case I want all the pages from 1 to the current page selected.
I've dimmed "p" as a variant and it has the value of the current page (say 3 for example).
Then the code goes like this:

With Word <---previously dimmed as word.application

.PrintOut Range:=wdPrintFromTo, From:="1", To:="p"

I've also tried p without the quotes, omitting the range variant, p dimmed as an integer, using Pages:="1-" & p instead of the From, To thing and other stuff but it ain't working!>!>! Sure if I make p a number than that works okay but I need it to be a variable because it's gonig to be different each time.

Any suggestions, because I'm sure the rainforests aren't going to appreciate me carrying on with this.

Thanks, Tris

I have an Excel file in which a worksheet contains 365 identical tables except for the date.

I wish to make all these tables individual print areas i.e end up with 365 print areas.

I wish to do this automatically using VBA code.

My only attempt to was use the following code on each table using a loop.


	VB:
	
 PrintArea() 
     
    Application.ScreenUpdating = False 
     
    Worksheets("Sheet1").Activate 
     
    For t = 1 To 13141 Step 36 
         
        Range("A1")(t).Activate 
         
        ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address 
         
    Next t 
     
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I used Step Into to view the operation of the code one line at a time.

Unfortunately as soon as the second table became a print area the first table print area was deselected
As soon as the third table became a print area the second table print area was deselected and so on.

The result of running the code was simply to make the last table a print area.

Any suggestions please ?

I am quite willing to upload a copy of the file if this at all possible.

Regards

Flynne

Is it possible to count the number of pages for a PDF file using VBA? I have over 500 pdf file info that I need to input daily. The specific filenames of the PDF files are placed in COlumn A while their corresponding (total) page count are inputted in Column B. Anybody got brilliant ideas?

Thanks!

I have an Excel file in which a worksheet contains 365 identical tables
except for the date.

I wish to make all these tables individual print areas i.e end up with 365
print areas.

I wish to do this automatically using VBA code.

My only attempt to was use the following code on each table using a loop.

Sub PrintArea()

Application.ScreenUpdating = False

Worksheets("Sheet1").Activate

For t = 1 To 13141 Step 36

Range("A1")(t).Activate

ActiveSheet.PageSetup.PrintArea = ActiveCell.CurrentRegion.Address

Next t

Application.ScreenUpdating = True

End Sub

I used Step Into to view the operation of the code one line at a time.

Unfortunately as soon as the second table became a print area the first
table print area was deselected
As soon as the third table became a print area the second table print area
was deselected and so on.

The result of running the code was simply to make the last table a print
area.

Any suggestions please ?

I am quite willing to upload a copy of the file if this is at all possible.

--
FLYNNE

I found the following code that prints selected rows on a single sheet. It doesn't work quite like I need it to.

There are two main issues:

1. The blank rows between the selected rows are not removed (i.e. if I select row 3 and row 7, there are 3 blanks lines between the printed rows).

2. It only prints in portrait mode. I need it to print in landscape.

I am not a VBA programmer and know nothing about it. I am asking if someone can add to or fix the code so it works like I need it to.

Sub PrintSelectedCells()
' prints selected cells, use from a toolbar button or a menu
Dim aCount As Integer, cCount As Integer, rCount As Integer
Dim i As Integer, j As Long, aRange As String
Dim rHeight() As Single, cWidth() As Single
Dim AWB As Workbook, NWB As Workbook
    If UCase(TypeName(ActiveSheet)) <> "WORKSHEET" Then Exit Sub
    ' useful only in worksheets
     aCount = Selection.Areas.Count
    If aCount = 0 Then Exit Sub ' no cells selected
    cCount = Selection.Areas(1).Cells.Count
    If aCount > 1 Then ' multiple areas selected
        Application.ScreenUpdating = False
        Application.StatusBar = "Printing " & aCount & " selected areas..."
        Set AWB = ActiveWorkbook
        rCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
        cCount = ActiveSheet.Cells.SpecialCells(xlLastCell).Column
        ReDim rHeight(rCount)
        ReDim cWidth(cCount)
        For i = 1 To rCount
            ' find the row height of every row in the selection
            rHeight(i) = Rows(i).RowHeight
        Next i
        For i = 1 To cCount
            ' find the column width of every column in the selection
            cWidth(i) = Columns(i).ColumnWidth
        Next i
        Set NWB = Workbooks.Add ' create a new workbook
        For i = 1 To rCount ' set row heights
            Rows(i).RowHeight = rHeight(i)
        Next i
        For i = 1 To cCount ' set column widths
            Columns(i).ColumnWidth = cWidth(i)
        Next i
        For i = 1 To aCount
            AWB.Activate
            aRange = Selection.Areas(i).Address
            ' the range address
            Range(aRange).Copy ' copying the range
            NWB.Activate
            With Range(aRange) ' pastes values and formats
                .PasteSpecial Paste:=xlValues, Operation:=xlNone, _
                    SkipBlanks:=True, Transpose:=False
                .PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
                    SkipBlanks:=True, Transpose:=False
            End With
            Application.CutCopyMode = False
        Next i
        NWB.Printout 
        NWB.Close False ' close the temporary workbook without saving
        Application.StatusBar = True
        AWB.Activate
        Set AWB = Nothing
        Set NWB = Nothing
    Else
        If cCount < 90 Then ' less than 90 cells selected
            If MsgBox("Are you sure you want to print " & _
                cCount & " selected cells ?", _
                vbQuestion + vbYesNo, "Print celected cells") = vbNo Then Exit Sub
        End If
        Selection.PrintOut
    End If
End Sub


Hi All,

I am having some trouble trying to select data on a sheet and then print the selection.

I have formatting in 100 rows and columns C to U. But currently there is only data in rows 1-13.

My plan is to select the last cell (U100) and then use 'Selection.End(xlUp).Select to select the data and then use the 'Print' command to 'Print Selection'.

I used the following code:


	VB:
	
Range("C1").Select 
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select 
Range(Selection, Selection.End(xlUp)).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
...but it isn't working. It selects all cells from C1 to U100 instead of C1 to U13.

I basically recorded myself selecting C1 then pressing "Ctrl-Shift-End" and then "Ctrl-Shift-Up" and this did exactly as I wanted (selected cells C1 to U13) but the macro, when run, doesn't.

I can't use

	VB:
	
Range(Selection, Selection.End(xlToRight)).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
as there may be gaps in the data between columns C and U.

Any help on the above (or any suggestions of a better way of printing a selection) is greatly appreaciated.

Many thanks,

Rob. Auto Merged Post Until 24 Hrs Passes;

Follow up to this. On posting, one of the suggested topics has totally solved this.

Thanks for JMAN for posting on this thread.

Cheers!

Rob.

Hi,

Is it possible to interact with web pages using excel? Specifically, I want to go to a webpage, entersome data into a 1 line form, hit enter, and then copy some information from the page that loads into an excel sheet.

At the moment I'm just trying to work out if this concept is possible using excel, before I go about begining a project.

Are there any otehr tools which would be better? I'm most comfortable working with excel whcih is why I was hoping to use that, but I'm willing to give something else a bash if needs be.

I am trying to parse some data from some HTML web pages using VBA for Excel
(2002) but cannot
remember the function to open the web pages and assign the source info to a
variable in VBA. Does anyone know how to do this?

Thanks

Hi,
I have the attached workbook where i would like to print the data on the 'Skorby comm'tab. Currently the data goes from upto A189-S189,sometimes this coud be less data and other times could be more.
I undestand there is an offset command to print selected data but i am unsure how i do this,i would then like to set up a Macro to print this workbook out...Your help again would be appreciated...

Dear All,

I am trying to parse some data from an Excel sheet to an IE page using VBA and I have difficulties in sending data to one object that look like a special combobox.

In fact there are 2 comboboxes on that particular page define as following:
- Combobox 1:
<selectname="material[]">
<optionvalue="3">AL</option>
<optionvalue="2">TL</option>
<optionvalue="1">GL</option>

- Combobox 2:
<divclass="ui-widget">
<selectname="partner" id="ID" style="width:300px">
<optionvalue="no"></option>
<optionvalue="3766">COMPANY 1</option>
<optionvalue="3792">COMPANY 2</option>
<optionvalue="3764">COMPANY 3</option>
<optionvalue="3071">COMPANY 4</option>

While I can send the data to combobox 1 using the following code:
IE.document.all.Item("material[]").selectedIndex = "2"

it seems that the same code doesn't function on the second combobox:
IE.document.all.Item("partner").selectedIndex = "3766"

and no data is displayed (nor trigger any errors).

Do you have any ideas how could I accomplish this?

Thanks in advance for looking into this,
Adrian

This problem will be very simple for someone who knows VBA. I do not.

I am pretty hot using Excel but I have come up against a problem. I want something that will select an area of the sheet and print it out.

I have already used Name to give each of the pages a distinct name and so it can have its own print area.

The top right cell of each Named range has an "if then else" statement which will be 1 for that named range to be printed out and 0 if that named range is not to be printed. The VBA routine would then move on to the next Named range and carry out the same routine to decide whether to print it out or not, and so on until the end of the spreadsheet.

I have never used VBA and although I have seen some examples of editing VBA, I have no idea what I would be doing. I am not a total programming novice as, in the past, I used to write low graphics stuff in Basic programming language so I know about terms like goto, gosub, loops, nesting etc.

So can anyone guide me along?

I have spent months converting the useless report document for my work from its original Word file to the more user friendly Excell.

It works really well and has a few people interested. The problem I have is that I used a lot of consatination and formulas to write the repeated information that goes onto different areas of the report (customer, location, measurements units, etc). This means that pages can not be easily edited out of the finished report as that causes #ERROR and #VALUE messages to spring up all over the place. When we had the older Word version of the report the guys usually spent a day after the job, edditing out the unwanted bits of the report. I want to use Excell to sort of do away with this frustrating exercise while avoiding the #ERROR and #VALUE messages.

What I thought of was a VBA routine that will select the pages for printing To understand. There are fact sheets covering all the equipment we may supply to our customers. These are filled out at the end of the job and the printouts are handed to the customer as their hard copy. Not all the equipment in the report will be used and I have set these pages up so that they remian blank. This works well enough at the moment - the blank pages are easily identified for discarding prior to adding the report to the completed folder.

How I think the programme will best work is that it will look for a value in a cell and if it finds the value of that cell is 1, it will go to the next programme line which will tell excell which area to highlight and then the next line will print that area. The VBA programm will then move onto the next page and identify the cell that holds the value, If it is a zero instead of one then it will skip to the next page, and so on and so on until the the vba programme has reached its end. Ideally this programm will be started by a simple button on the spread sheet itself

I am in the process of shopping around for a book on Excell VBA. I intend to use VBA code in future Excell spreadsheets to automate jobs that are not covered by Excell functions and formulas.

Past experience has been 10 years writing spreadsheets which I use in my job as a technician. Spreadsheets cover number crunching, form filling, data manipulation. Applications used - Psion Sheet, Lotus, Excell. Prior to that I wrote programmes in Basic programming language on a hand held computer to carry out the same work I have just described.

Gone on a bit here but without the detail you folks might not understand what I have been up to or where I might be going with this.

Any advice or help will be most appreciated as I am fed up with colleagues at work telling me how easy it would be, but not coming up with the goods themselves :-/

H and ignore the pages that have been left blank (

Hi, Here's a tricky one...

I want to be able to select a specific page in a workbook, and copy it
to another workbook. For example: if sheet 1 prints out over 3 pages, I
need the vba to select the second page of this.

I can use VBA to select manually added pagebreaks, by referring to the
vpagebreaks and hpagebreaks collections of the sheet, but this does NOT
reference automatically added page breaks. This is probably because
auto-pagebreaks have to be recalculated (re-paginated) every time you
add or remove lines.

I also tried looking at things like sheet.pagesetup.printarea, but no
luck.

Is it possible to do something like: thisworkbook.printout() and use
the print to file, to export the pages as a seperate workbook?

Failing this, I shall go tell my client that it can't be done and blame
excel.

Please help,

I am formatting each page to print using vba and it is very cool but slow
with 75 tabs / pages to format.

I can select all tabs (with vba) and manually format all selected tabs but
my "With Reference" only allows 1 tab / Page.

How can I do all the pages (.PageSetup) with one pass?

My Code:
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&A"
..
..
..
..
..etc

What should I change "With ActiveSheet.PageSetup" to so that it can deal
with ALL SELECTED SHEETS instead of doing just one sheet even though all are
selected?

Many Thanks

Ross

Hello All:

I need help creating a VBA code that finds the last column within the print area viewed in Page Break View. The tricky part is that one of the cells in the last used column has text that overflows into 10 adjacent cells. This can be seen in the picture below (and also in the attached Excel file). The critical overflowing cell is highlighted in yellow.

book3.jpg

For my purposes, I cannot use the built-in column autofit feature. Also, for this worksheet, I have the page setup to print to 1 page wide by 1 page tall in landscape orientation.

Using VBA code, how can I determine that Column S needs to be the right boundary of my print area?

Any help is greatly appreciated!

Book3.xls

Hello,
I am trying to print the current page and the next page in a Word document. I have found the wdPrintCurrentPage code, but there doesn't appear to be an easy way to include the next page in the document.
We have many documents that need to print current and next page, so using the wdPrintFromTo or wdPrintRangeOfPages doesn't appear to be a viable option because the page numbers aren't always the same.

Does anyone know how to create a macro that can always print the current and next page?

Thanks!

I need to make a bunch of pdfs from a word document. I want to select the text that i want as an individual document, click a macro button that will print the selection to pdf, and name the title of the pdf whatever is formatted as Heading 1 style font. Each selection will only have one line that is Heading 1.

Any help would be great! Used VBA for excel a lot, but never for Word. Thanks!

Hi everyone, your help was greatly appreciated with the last question I had, and I'm stumped on this one.

I have several pages of text in a Word document that I need to grab using vba and drop into a spreadsheet, and then print.

It appears, from my research, that I can't print multiple pages of an embedded Excel document. That can be avoided by simply embedding multiple documents, which is an acceptable workaround.

My problem is this: I am able to embed the first document just fine using the following code:

ActiveCell.Offset(1, 0).Select
    ActiveSheet.OLEObjects.Add(Filename:= _
        "document1.doc", Link:=False, _
        DisplayAsIcon:=False).Select
    Selection.ShapeRange.Fill.Visible = msoTrue
    Selection.ShapeRange.Line.Visible = msoFalse
This works perfectly. What I need to do next is insert a page break, then the next document, but I don't know how to reference the next cell/row below the first embedded document.

I've tried searching for a solution but have come up empty-handed thus far.

Can anyone offer any advice as to how I can reference the next empty cell/row below the current embedded document so I can insert the next one?

Thanks in advance!

Rich


No luck finding an answer? You could always try Google.