Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

printing a worksheet using vba code

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


Post your answer or comment

comments powered by Disqus
Hello

I know how to manually protect a sheet with a password, but my question is this: How do I protect a certain sheet with a password using VBA code?

From my present knowledge when I say:

then it protects my sheet but with no password, so any user of my sheet could just unprotect it manually by going to Tools
> Protect > Unprotect Sheet

Any hints?

Is there a way, using VBA code, to remove a hyperlink from a cell, while
retaining the cell text content?

Thanks,

Keith

Using the VBA code, I like to print (on the default printer) an existing file called "XYZ.pdf" that resides in the same folder as the Excel file. What would be VBA code for it?

Important: I am not trying to make a pdf file out of an excel sheet/range. All I want is a VBA macro that prints a specific, existing pdf file (in the same folder as the excel file) to my default printer to print the entire pdf file on paper.
Computer OS: Windows 7
Excel Version: Office 2007

Is there a way to add the following code to the current active worksheet using vba?

Private Sub
Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = True
Application.ScreenUpdating = False

        If Target.Address = "$K$46:$L$46" And _
        Range("K46").Value = "n" Then
        Range("K46").Value = "o"
        Range("A1").Select
        Else
        If Target.Address = "$K$46:$L$46" Then
        Range("K46").Value = "n"
        Range("A1").Select
            End If
                End If

End Sub


I want to clear the content of a whole worksheet using VBA.
Does anyone know how to do it?

Thanks!

hi experts...
is it possible to disable the protection of the worksheet, using vba codes? how? thanks....

I cannot get Excel to print an "ENTIRE" embedded Word document from a Worksheet using VBA.

Only the very first page of the the embedded Word document will print.

Does any one have any Idea on what the problem may be?

Thanks in advance for any and all help.
Cheers
Don

I provided a code example below:

Sub m1_001_PrintExcelandWordInfo
Dim voItem As Object
Dim vsWordObjName as string
For Each voItem In ActiveSheet.OLEObjects
' assumes only one OLEObject in the worksheet
vsWordObjName = voItem.Name
end if
Next
ActiveSheet.OLEObjects(vsWordObjName).PrintObject = True
ActiveSheet.PrintOut
End Sub

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

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

hey folks,

I'm creating a button on a worksheet using VBA and then want to add code to the buttons click event. So far i have this:

dim btnCode as string
dim ws as worksheet

set ws5 = thisworkbook.worksheets("Graphs")

ws5.OLEObjects.Add classtype:="Forms.commandbutton.1", _
                                  link:=False, _
                                  Left:=2783, _
                                  Top:=0, _
                                  Width:=90, _
                                  Height:=32

    btnCode = "Sub CommandButton1_Click()" & vbCrLf
    btnCode = btnCode & "Application.ScreenUpdating = False" & vbCrLf
    btnCode = btnCode & "Call diffTable" & vbCrLf
    btnCode = btnCode & "Application.ScreenUpdating = True" & vbCrLf
    btnCode = btnCode & "End Sub"
    Debug.Print btnCode
   
    ThisWorkbook.VBProject.VBComponents("Graphs").CodeModule.AddFromString (btnCode)

On the last line I receive a "Run Time error '9': Subscript outof range". Any able to shed some light on this?

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

Hey all,

I'm having some trouble getting an Formula array to properly execute in a cell range using VBA Code. Here's my code:


	VB:
	
    If .Range("A11").Value = "" Then 
        Exit Sub 
    Else 
        lastrow = .Range("A65536").End(xlUp).Row 
        x = lastrow 
        str1 = "=ROUNDDOWN($D11 - TIME(" + varHour + "," + varMin + "," + varSec + "),0)" 
        str2 = "=$D11 - TIME(" + varHour + "," + varMin + "," + varSec + ") - $J11" 
        str3 = "=VLOOKUP(A11,FOCUS_MARKET,2,0)" 
        str4 = "=IF(ISERROR(MODE(IF(IF(R11C3:R" + x + "C3=$C11,R11C7:R" + x + "C7)>60,IF(R11C3:R" + x + "C3=$C11,R11C7:R" + x
+ "C7)))),AVERAGE(IF(IF(R11C3:R" + x + "C3=$C11,R11C7:R" + x + "C7)>60,IF(R11C3:R" + x + "C3=$C11,R11C7:R" + x +
"C7))),MODE(IF(IF(R11C3:R" + x + "C3=$C11,R11C7:R" + x + "C7)>60,IF(R11C3:R" + x + "C3=$C11,R11C7:R" + x + "C7))))" 
         
        Range("J11").Formula = str1 
        Range("K11").Formula = str2 
        Range("L11").Formula = str3 
        Range("M11").FormulaR1C1 = str4 
         
        .Range("$J11:$M11").Select 
        If .Range("A12").Value = "" Then 
            .Range("A10").Select 
            Exit Sub 
        Else 
            Selection.AutoFill Destination:=Range("$J11:$M" & lastrow) 
        End If 
    End If 
End With 

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

I also tried this with Range("M11").FormulaArray; tried it with Cells(); Offsets() and without R1C1 notations.. and nothing seems to be working for me :$

I've searched the net for assistance and can't figure out why this won't work. Any assistance would be really appreciated.

Regards,
Naki

I am trying to store the data from a notepad into excel sheet. I require this to print labels.

I want to convert all the cells in the sheet to "Text" format using VBA code. I have tried CStr but it doesn't seem to work.

If i try to store "00001" into a cell using CStr("00001"), it still takes it as 1!!!!!! Is there any other way to do this???

I am trying to close a worksheet without saving it in excel. Does anyone know the VBA code to close the sheet and not have the message box popup?

Thanks,

I have a shape that was added to a chart (and the chart is embedded in a worksheet) using an adaptation of Jon Peltier's wonderful "ShadeBelow()" VBA code (here). This shape is "part of" the chart--meaning, for example, that you can't drag it outside of the chart area. (To create this effect manually, simply create a chart on a worksheet, select the chart, and then insert a new shape inside the chart area.) I would like this shape to be "removed" (or "dissociated") from the chart and added as a shape on the worksheet--in the same exact spot. I suppose one approach would be to apply some VBA cut-and-paste trickery, but I'm hoping that there is a simpler solution. Any suggestions?

(Oh, and I don't think the technqiues shown here and here will help, as my shapes are "custom" curves.)

(If you're curious, I'm using the shapes (there are several) to indicate regions on the chart, and as such they should be behind the data points and gridlines. I can accomplish this by setting the chart's and the plot area's fill colors to "no fill" and moving the shapes to the background--but this is only possible if the shapes are "on" the worksheet, rather than "on" the chart.)

Any and all suggestions are welcome. Thank you!

Hello,

I'm having the following little problem, and i'm wondering if anyone
can help me solve it.

I have two columns on a worksheet, the "A" column contains a date, and
column "B" has in some cells (but not in all cells) an "x" or an "X".

Now I want to do the following using VBA code.

The VBA code has to check each row if the date in column "A"
is the same as the current date AND there is an "x"or an "X"
in the cell in column "B" of that row.

If this condition is true a messagebox has to appear.

As soon as the condition has been true once and messagebox
mentioned above has been displayed once, further checking
if the condition is true is no longer necesary.

Is there a simple way to do this using VBA ?

Thanks for any help.

Best regards,
Frits

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,

Is there a way to track changes made to a worksheet using VBA?

I was thinking that perhaps there was an event procedure. I envision
that the change could be displayed on a hidden sheet when a change is
made - similar to the tracking changes tool but without sharing a workbook.

I'm on a deadline with this one and so any help would be greatly
appreciated. I've asked about this issue before and haven't received
any replies and so I'm thinking that maybe this cannot be done.

Thanks in advance for any help,
anita

Hi there

To create a database to link data from a Pervasive.SQL database to
Excel, I run the CreateDB.exe utility. This is similar to useing the
ODBC manager in the Control Centre.

Apart from clicking next a few times the only user input is the Name
and the Directory.

Is it possible to create the database from within Excel using VBA code
prompting for these 2 variables or getting the info from worksheet
cells.

I would appreciate any help.

Thanks

Michael

I am wanting to use VBA code to open a text file with the
import wizard. Does anyone know how I would invoke the
import wizard to open a text file in VBA? Any assistance
on this matter would be greatly appreaciated.

Hello everyone. On a monthly basis I have a procedure to extract data from a website and place that data into an excel worksheet. it does not take long to do, as there is a "button" on the website that you can press to "dump-to-excel". However I would like to use VBA code to inlcude this procedure into some macros I have already developed. The only "variable" in the procudeure involves the user inputing dates in the following format: dd-three letter month abreviation-YYYY. The web data is contained in a nice table in the center of the site. The number or rows varies, but the number of columns is fixed at 5. The intranet site uses IE. The following code sort of works, but as you can tell it does not prompt the user to enter a date. The code below is a good start, I think.

Sub WB()

URL = "http://cds.worldbank.org/Pages/CurrMain.aspx?rate_date=31-mar-2010"

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = True


'get web page
IE.navigate2 URL & Ticker
Do While IE.readystate <> 4 Or _
IE.busy = True

DoEvents
Loop

Do While IE.document Is Nothing
DoEvents
Loop


End Sub
I would like to save the table into a worksheet called CDS_Raw.xlsx. Any suggestions would be greatly appreciated. Note that the code above does bring the website open.

I am assuing I should declare a variable say fdate

and use that somewhere in the URL?

I would like to send single worksheets as HTML mails to various single mail
recipients, getting their SMTP address from a table in the workbook. Can
this be done using VBA?

Hi all,

i would like to do the following things using vba coding, can someone who's familiar with coding assist me by helping me with the vba codes.

Issues:
1. I would like to send active worksheet as the email body for ms outlook mail message, in addition, i would like to attach the entire workbook as an attachment in the same email

2. I do know from the .SendMail,under the subject line, i am able to insert text and the date as well using formula such as format(date, "mm/d/yy"), but i would like to ask whether can i extract the date from one of the cell in the workbook instead. I need to do that because i am preparing a report for the previous work day (i.e. if i am preparing a report on Monday, in fact i am preparing a report for close of business Friday); and using the format(date, "mm/dd/yy") formula won't work

e.g. I want to put "Report as of 16 June 2010" at the subject line of the email message.

Thanks for any help

I wish to copy a worksheet using vba but not include the VBA code and userform with the copy. IS this possible if so how? Thanks




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