Free Microsoft Excel 2013 Quick Reference

Print selection using vba Results

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"

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

Many Thanks


Hey guys,
I've posted this issue on many forums without any luck, so hopefully that'll change here!

I have an excel document with 8 tabs, each containing a chart.

I am creating a button which allows the user to print all charts, to a specific printer on our network, in color!

Here is my code so far:
Sub PrintEmbedded()

    Dim shtTemp As Worksheet
    Dim chtTemp As ChartObject
    'Application.ActivePrinter = "comdocRIC102 on ne06:"
    '.ColorMode = acPRCMColor
    For Each shtTemp In ThisWorkbook.Worksheets
        For Each chtTemp In shtTemp.ChartObjects
End Sub

The commented stuff is stuff I tried. The for loop goes through each worksheet, finds the chart and prints it.
That works fine, but the program needs to use the correct printer, and also set it to color (since it defaults to B&W).

The issue I'm having with selecting the printer is that the name is different on all computers (mine is ne06, my boss's is ne03), so I think that first commented command is useless.

And I also haven't been able to get the colormode command to work.

I've been trying to mess around with the Application.Dialogs(xlDialogPrint).Show, so the user could manually choose the correct printer, and set it to color. But I haven't been able to use it properly.

Maybe someone has a better idea or knows how to use my ideas to make it work! Thanks

Hello Everyone,

Hope someone can help me with this issue.
I use the below code to extract data from another workbook "B1" which runs a macro when workbook is closed. The particular code that runs before "B1" closes, causes excel to hang. If I remove the code from Worksheet "B1" it works fine. Is there a way to bypass/disable the code from "B1" running from the below code. Please note, that I only need the data extracted from "B1" so I dont need any codes to be run on that workbook while the below code is run.

Thanks in advance for any help.


XL Factor.

    On Error Resume Next 
    Dim fn As Variant 
    Dim thisWB As String, newWB As String 
    Dim targetSheet As String, destSheet As String 
    targetSheet = "Todays Movements" 
    destSheet = "B1 Movements" 
    thisWB = ActiveWorkbook.Name 
    fn = Application.GetOpenFilename("Excel-files,*.xls*", 1, "Select your data file", , False) 
    If TypeName(fn) = "Boolean" Then End 
    Debug.Print "Selected file: " & fn 
On Error Goto end_sub: 
    Application.ScreenUpdating = False 
    Workbooks.Open fn 
    newWB = ActiveWorkbook.Name 
    Range(Selection, Selection.End(xlDown)).Select 
    Sheets(destSheet).Range("A3").PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
    Range("O3").PasteSpecial Paste:=xlPasteValues 
    Application.CutCopyMode = False 
    Application.ScreenUpdating = True 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

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?

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.


This is what I have so far and this works fine. I have pre-defined the range but is there a way to print only up to the last row where there is data otherwise no need to print white spaces. Thanks for your time and effort. My code is below:

Sub Set_Print_Area()

Application.ScreenUpdating = False
ActiveWindow.SmallScroll Down:=-24
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
ActiveSheet.PageSetup.PrintArea = "$A$1:$Q$200" With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = True
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLegal
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SmallScroll Down:=-15
ActiveWindow.LargeScroll ToRight:=-1
Application.ScreenUpdating = True
End Sub

G'day everyone.

I need help writing some code to set the print area of a sheet.

The sheet is updated every day by Excel novices who can't or won't learn how to set the print area. So I want to set up an Auto_Close subroutine that will adjust the print area based on the new data range.

I have worked out most of this already, including how to adjust the range and assign it to a Range Variable.

What has got me completely stumped, is when I try to assign this as the new print area using;

ActiveSheet.PageSetup.PrintArea = NewPrintArea

It does not work.

It will work if I specify the range as an absolute range value, but how do I convert either the Selection or the Range Variable into one that the ActiveSheet.PageSetup.PrintArea statement can use??

I hope somebody can help me here, as my forehead is starting to get sore and I'm running out of caffeine.


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

If there is no Print range set then the usedrange is printed if you press the print button.

You can use this for example if the prinrrange is set but is not all the cells
on the sheet.


Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)

"Gary" wrote in message ...
> I am looking for an easy way to use a macro to select a
> print range by determining the last row and column used.
> However, the last row and column used will change between
> users. For example, if the user only used up to row 98 and
> column L is the last column used. I need to select the
> range A1 to L98 and print this range. Thanks for any input.
> gary


Does anybody know how to add entries to Outlook's calender using VBA?

I'm currently developing a time monitoring system where team members access a form, select their name and tell the system from what date they want annual leave, away for meetings, training, etc.

I just need to know how to set the time, date and caption in a calender.

I also want to print the calender from Access if possible. I would be nice to select the date range to print.

Any ideas? I'm really in the dark about this one.

Thanks in advance.

PS. We're using Win 2000 SP4, Outlook 2000 SP3 & Access 2000.

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!

I am trying to add button using VBA macros, below is the code that i am using to create the button, iam trying this in XL 2007
Sub AddButton()
    ActiveSheet.Buttons.Add(4.5, 1.5, 136.5, 26.25).Select
    Selection.OnAction = "AddSelectedCells"
    Selection.Characters.Text = "AddSelectedCells" 
    With Selection.Characters(Start:=1, Length:=8).Font
        .Name = "Arial"
        .FontStyle = "Regular"
        .Size = 10
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .ColorIndex = xlAutomatic
    End With
End Sub
When i try to assign display text (Selection.Characters.Text) with some bigger length ,it is throwing some error saying "Unable to set the text property of the Charectors class"

Selection.Characters.Text = "AddSelectedCells" --->Works fine Selection.Characters.Text = "To Format & Print Benifit Illustration" ---> Gives above said error.

Any info appreciated!

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!

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, _
    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!


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

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

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
            aRange = Selection.Areas(i).Address
            ' the range address
            Range(aRange).Copy ' copying the range
            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.Close False ' close the temporary workbook without saving
        Application.StatusBar = True
        Set AWB = Nothing
        Set NWB = Nothing
        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
    End If
End Sub

So, think your hot stuff.

Im needing to search through the attached document which is truck logs at a mine and use vba to search through the list for each of the different shovels eg SHVL1, SHVL2 & SHVL3, and when the code finds that string, to display the tonnage 2 colums back from it. Im about half way there a i think but am having trouble with strings, if it was numbers i could do it no problem

I have the idea in my head but need a genius to help me with the fine print

Heres what i have so far (that doesn't work) but it gives an idea of what i want

Sub trucks()
Dim a(1000) As String

tonsum = 0

For i = 1 To 548

    a(i) = Cells(i + 1, 7).Value
    If a(i) = "SHVL1" Then
        Range(i + 1, 5).Select
        tonsum = tonsum + ActiveCell
    End If
     Cells(1, 9).Value = tonsum
Next i

End Sub
Hopefully someone konws what to do here..or has done it before

I have a client who developed a very simple MS Access application where he loads data for about 250 stores in a table and prints a report for each store. He wanted to print 250 different reports in pdf form, one for each store, and put the reports in the same folder with a different name for each store. I solved the problem by using VBA code to open the report in design mode, changing the Report caption to the desired report file name, and saving the report. Then I opened the report for print (he had set his PDF printer as the default printer) and applied the filter to limit the report to the desired store. I looped through all the stores till they were all "printed".

Now, I have another department with a similar request, however, his "database" is in Excel. Does anyone know how I can "print" the selected Print Area to the default printer with a specific file name.

Thanks, Eddie

I need to select a user defined list of worksheets all at once using VBA.

I have a string variable containing a list of selected worksheets.
for example: mystring = "Sheet1,"&"Sheet2,"&"Sheet3"

Is it possible to pass the variable mystring as an argument in the following
Sheets(Array(mystring)).Select without it generating an error message ?

Or is there another way of selecting worksheets at once using VBA ?


I am writing a Point of Sale system in Excel using VBA. (It is long story as to why I am doing this and I know it is probably crazy using Excel and VBA as the basis for a point of sale system but it seems to be working!!)

I currently have an Excel worksheet which is used to hold the items in the sale:

Column A = Item Code
Column B = Quantity
Column C = Item Price
Column D = Tax Code

What happens is, when the user enters an item code in column A using VBA I look up the products details which are stored on a separate worksheet and copy across the item's price and tax code. When the user clicks a 'take payment' button on the worksheet I use a userform to inform them of sale total and for them to enter different payment methods / amounts. When the user has taken payment then a receipt is printed out and the sale's data is stored in another worksheet. The person who I am writing this system for has asked me if I could incorporate multibuy deals into the sytem, specifically:

1) Buy X DIFFERENT products from a selection of products and get them @ a set price - this would enable you handle bundle deals eg. bundle 3 items together for a set price
2) Buy X (SAME OR DIFFERENT) products from a selection of products and get them @ a set price - this would enable you do handle 'Buy get one free' kind of deals, '3 for 2' kind of deals and '2 CDs for £15' kind of deals

Working out which multibuy deals apply to the list of items the purchaser is buying has made things more difficult than just adding up item prices for the sale. Obviously I need to add a 'tag' to each product's details identifying what kind of multibuy deal (if any) it is linked to. However at this point I am struggling to know how to program these multibuy deals using Excel Worksheets and VBA. For me what seems to makes it more difficult is that for each product line in the sale the quantity of each item (Column B above) could be greater than 1.

I am not looking for anyone to write the code for me, but if anyone can help by suggesting an outline for a general algorithm to work out which multibuy deals to apply to the sale I would be very grateful!!

If I am posting this query in the wrong place I apologise and could you direct me to the best place to post it.

ALSO posted on:



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