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

Free Microsoft Excel 2013 Quick Reference

Macro set print area for changing data Results

Hi All

I have an Excel 2003 spreadsheet that has data that is dumped from
another application. I wanted to automate setting the print area each
time the user creates the file, my problem is that the amount of data
is not fixed and will change everytime they update the info.

I can't assume that there will always be text in the last cell in the
bottom right hand corner as this cell may be blank. However, the
spreadsheet print area will always be 7 columns wide, and the last cell
in Column D will always have data.

Does anybody have any idea o fhow to do this ?

Ta

Karen

--
ksp
------------------------------------------------------------------------
ksp's Profile: http://www.excelforum.com/member.php...fo&userid=6267
View this thread: http://www.excelforum.com/showthread...hreadid=487498

Hi All

I have an Excel 2003 spreadsheet that has data that is dumped from another application. I wanted to automate setting the print area each time the user creates the file, my problem is that the amount of data is not fixed and will change everytime they update the info.

I can't assume that there will always be text in the last cell in the bottom right hand corner as this cell may be blank. However, the spreadsheet print area will always be 7 columns wide, and the last cell in Column D will always have data.

Does anybody have any idea o fhow to do this ?

Ta

Karen

Hi
I am developing a spreadsheet which prints parts of several worksheets.
The ranges to be printed change depending on the data.
I have the ranges I want to print as text strings and so far I have used the GOTO function to select the ranges by pasting in the text and then used SET PRINT AREA, but in the macro I end up with the selected range as the print area.
That means when I run the macro, i always end up printing the range that was selected when I recorded the macro.

thanks for your help,
Ray

I would like Excel to automatically adjust the print area for a pivot table.
As I select different options, the # of rows change and I have to manually
select the print area to receive the correct printout.

I found one other post about this and I tried to follow the instructions but
I couldn't get it to work. I think I had to write a macro but I'm not sure.
This was what the other thread suggested:

dim i as long
dim j as long
dim rng as range
set rng=range("b1..e1") ' the address of first row of data you want to
print out
j=0
for i = 1 to rng.columns.count
j=worksheetfunction.max(j,rng.cells(application.Rows.Count-rng.Row,1).end(xlup).row)
next i
activesheet.pagesetup.printarea =
rng.Resize(j-rng.row+1,rng.Columns.Count).Address

I consider myself to be an advanced user in Excel but I'm not good with
writing code. I can record a new macro, no problem --> but I'm a novice at
writing one.

If you have a solution to this problem, please write the instructions for a
"dummy". I won't take it personally

I am using Excel 2003.

--
Thanks!

I have been searching without real success for a soloution to a print area problem. Hopefully this one us easy for those blessed with the more ability than me.
I have a worksheet named "TQUOTE" with the print area defined as A1:E:286 resulting in 5 pages printing ,however if there are only 3 pages of data I will be left with 2 blank pages being printed. ( along with the header rows which are set to repeat for each page ). I should mention this worksheet is included in a list of sheets to print based on the response to a user form.
Would it be possible to have the print area change based on the an empty cell that related to the next page break? eg. If the page break for page 1 ends at row 55, if a cell in row 56 were empty then set the print area to be A1:E55
If the page break for page 2 ends at row 85, if a cell in row 86 were empty then set the print area to be A1:E85 and so on ??
I do not have the skills yet to write this macro , and i bow to thiose that do !
any and all help would be greatly appreciated.
Jeff

Hi guys,

Hopefully you can help with this. I did a quick search and couldn't find anything that matched my exact requirements. Our situation is as follows;

We run a small gardening shop and use a simple spreadsheet to track of various things.

Row A contains headers, with the data following in the rows underneath. This table is now quite large, and we therefore hide 600 or so rows so that only rows with data from the last week is shown. We often need to print this for easy reference. I currently do this by highlighting the area I want and setting it as print area. The print therefore doesn't include hidden cells, which is what I need.

The problem we have, therefore, is that we have to manually select this print area each time. My experience with macros is VERY limited, to the point where I can record one which will select the print area and print. However, as the list gets longer and more rows are hidden the range obviously needs to change, and my simple macro will not keep up.

So my question is as follows. Is there a simple macro I could write to assign to a button that could "keep up" with the moving range? So either it always prints the header row and the, for example, 50 rows beneath (exlcuding the hidden ones), or, even better, it prints the header row and all rows with data in them that are relevant to the last week. Each row has a cell for the date it concerns so assume this may be possible?

Hope this made sense and look forward to your responses.

I have a spreadsheet and I want the macro to set the print
area for me each time since the amount of data will be
chaning on a regular basis. I have the following from
using the macro recorder but it is not working. The macro
recorder hardcoded the cell addresses based on the current
data. I'm trying to change it to change the print area to
the selected cells. Any suggestions would be
appreciated. Here's the code I tried:

Sub Print_Area()

Range("A1").Select

Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlDown)).Select

(The above lines properly select the data I want. The
following line is where I have the problem. I just don't
know the proper syntax.)

ActiveSheet.PageSetup.PrintArea = Selection

End Sub

Thanks for the help.

I have several pivot tables that expand and contract with data. I am
constantly changing print areas to accomodate all the data or to make sure
the data prints in a nice format on a single sheet.

Does anyone know how a macro could be set up that could print any data that
is continuously present in rows or columns? I'm an advanced excel user, but
not a programmer. I can record macros, and write very, very basic macro code.

Any help would be greatly appreciated.

Todd.

Hi,
I am looking for help with page setup macro. My worksheet (excel 2003) contains lots of details and requires different page setup for printing e.g.
(a) Cells B3 to R3 downwards: Data details, which user may add more rows of records
(b) Cell S1-Z20: Data summary (formula) linked with (a)
(c) Cell AA1-AK15: Pivot Table linked with (b)

Therefore, I set buttons, linked with 3 macros to do page setup for the different ranges. However, I had some problems i.e.

1. For print area (a), how to define print area parameter in a macro to check the last row of data. i.e. check for last "blank cell" of column "C-E" (others columns contain formula before input the data)

2. In the future, if I or user insert new columns in area (a), is it possible to setup macro that have print area for (a) - (c) shifted without changing the parameter in the macro?

Thanks
DT

Hello
I am trying to write a macro that will allow the user to print out the sheet from the beginning to the last used row. The catch is that I have formats and formulas all the way to the end (A1:O319). My basic format is a frozen Pane (for a header and summary) from A1:O18, then the user inputs their data starting on B19. The columns never change, and the rows will be variable. I tried a dynamic range to no avail, and have most recently tried this code:

	VB:
	
 printter() 
    Dim myrange 
    Dim Rng, cell As Range 
    Set myrange = Range("A320").End(xlUp) 
    Set Rng = Range("A1:" & myrange.Address) 
    For Each cell In Rng 
        If cell = "" Then 
            myrange = cell.Address 
            MsgBox cell.Address 
            ActiveSheet.PageSetup.PrintArea = "$A$1:O" & cell.Row 
            ActiveSheet.PrintOut 
            Exit Sub 
        End If 
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
My next plan would be to write to check to see the last used cell in column B, since that cell has only formating, no formula, and is a required entry cell
Any help would be greatly appreciated.
Thanks.

I have a (semi-inherited) macro that prints different pages of a workbook, based on whether and what their entries are. One of the pages contains a text box, where users will enter different lengths of text. When the macro runs, it prints right the first time but also "resizes" the text box so that future print attempts only print part of it.

I put "resize" in quotes because the text box does not appear to change shape. However, when I look at its properties it suddenly says it is much smaller than it was before (and still appears to be).

I can't figure out why on earth this is happening! Has anyone come across it before?

Here's the code in question (the problem is on the "narrative" page):

Sub CLCMprintall()
'  prints all pages (with data) on the CLCM spreadsheet

Dim x, a, b, c, d, e, f, g, h, i As String
Dim y, z, q As Integer
    
ActiveSheet.Unprotect Password:="***"
    
'notes active cells on each sheet to return cursor there afterwards. 
x = ActiveSheet.Name
Sheets("page 1").Select
a = ActiveCell.Address(True, True)
Sheets("narrative").Select
d = ActiveCell.Address(True, True)
'(repeat for other worksheets - also sets values for Loan2Amt, Q & Z,
'which govern whether other unrelated sheets need to be printed.)    
'
'tests whether there is text in Overflow sheet and sets print area if there is
If q <> 0 Then
    Call OverflowPrintArea
End If
'
'tests whether there is a Loan 2 (by testing Loan 2 amt) and 
'adds Loan 2 collatersl to print area if there is
If Loan2amt > 0 Then
    Call PrintColl2
End If
'
'Calls macro that sets the Narrative's print area based on size of text box
Call NarrativePrintArea
'
' tests whether there is anything on the notes page, addendum page, 
' or overflow, and includes them in the print area if there is
If z <> 0 And q <> 0 And y = 0 Then _
Sheets(Array("Page 1", "Collateral", "Page 1 Addendum", "Financial   Info",
"Narrative", _
"Covenants & Checklists", "Policy Reference Page", "GDSC", "Overflow")).Select _
Else
'(lots of other options on the different permutations,
'showing what to print in each case)    
              
    Application.Dialogs(xlDialogPrint).Show

'HERE is where the problem appears - when I step thru it and 
'watch in the Immediate window, the text box size is fine before 
' the dialog command but artificially shrunken afterwards.

' After the sheets are ungrouped, the spreadsheet defaults to 
' selecting the same cell on each spreadsheet, even though 
'it may be locked.  This part of the macro selects the
' correct starting cell on each sheet.
Sheets("page 1").Select
Range(a).Select
'(etc etc for the rest of the sheets)

Sheets(x).Select
ActiveSheet.Protect Password:="***", DrawingObjects:=True, 
contents:=True, Scenarios:=True
ActiveSheet.EnableSelection = xlUnlockedCells
'
'sets hidden areas on Overflow (that were hidden just for print area) back to un-hidden
    If q <> 0 Then
        Call OverflowRelock
    End If
'
Sheets("page 1").Activate
ActiveSheet.Range("$c$7").Select
End Sub

Sub NarrativePrintArea()
'Sees how big the text box on the Narrative tab is, and adjusts print area accordingly.

Dim Outside, BHeight, BRows, Parea As String
Worksheets("Narrative").Unprotect Password:="***"
BHeight = Worksheets("narrative").Shapes("text box 16").Height
BRows = BHeight / 16
Application.Goto reference:="picspace"
ActiveCell.Offset(BRows, 0).Range("a1").Select
Outside = ActiveCell.Address
Parea = "$a$15:" & Outside
Worksheets("narrative").PageSetup.PrintArea = Parea
Worksheets("narrative").Shapes("text box 16").Select
Worksheets("narrative").Protect Password:="***", DrawingObjects:=False, contents:=True, Scenarios:=True
Worksheets("narrative").EnableSelection = xlUnlockedCells
'
End Sub
Thank you!!

ETA, 2/17: Thanks those of you who read this; so far no luck. This is now crossposted; a friend suggested I try a different forum. If you want to see that post, here is the URL: http://www.mrexcel.com/forum/showthr...08#post1844608 . If I get a solution there I will add it here and mark this thread as solved.

ETA, 2/23: solved - see link above to crossposting - still don't know the reason, but the work-around seems to be to set the Narrative print area off of the outside corner of the text box, rather than using the size properties of the text box (which still are wonky).

Hello all,
I have a large workbook (131 sheets, actually) and have a print area set on each sheet past Sheet 3. The first three sheets have a TON of random data on them and are significantly larger than subsequent sheets.

I am looking for some VBA code to select all sheets after worksheet 3, and then to print each sheet (while respecting the print areas already set). It also has to bring up a printer select dialog box (the default CTRL+P one is fine, my user won't change the settings...) because he moves between offices (and hence different printers).

Thanks in advance everyone! Much appreciated.

Edit: using this code, but I want to know how to be able to get the print dialog...
Sub Print()

' Select the first sheet for printing
Worksheets(4).Select
' Select Multiple Sheets (sheet 5 and on...)
For i = 5 To Worksheets.Count
    Worksheets(i).Select False
Next i

ActiveWindow.SelectedSheets.PrintOut Copies:=1

' Back to sheet 1
ActiveWorkbook.Sheets(1).Select

End Sub


Hello again

I have a macro that I run on a daily basis and picks up various different data from different external sources.

The data populates in Columns A to S.

I want to create a button on the spreadsheet that sets the print area for all cells that has data in them and sets the page setup as landscape with the scaling being to about 75%

(remember this can change on a daily basis - one day i could have 10 rows populated, the next i could have 200)

Can this be done?

Thanks in advance.

How to return the result of a cell's formula as an argument within a
running macro?

Hi to All,

I'm sorting a table to extract data which becomes a new variable
array (variable number of rows but columns don't change and top left
is always the same cell address - in this case C7)

Alongside this array in cell R5 I have a formula which automatically
calculates the variable array dimensions. Let's say for example this
returns the text string C7:M32

The macro gets to the point of having done the sorting, the new data is
all nicely arranged in the variable array and cell R5 has updated
itself. Perfect so far!

I then want the macro to set the print area to the array dimensions
calculated in R5 and this is where I get stuck. I have found heaps of
examples on how macros can write data to a cell but nothing on how a
macro can read data from a cell to be used as an argument within the
macro. Perplexed. A great big "Thanks!" to anyone who can help.

Cheers,
RonW.

PS: please don't assume I know anything at all about VBA and macro
programming . . .

Microsoft ExcelHow Can I Import a Fixed-Width Data File into Microsoft Excel?How Can I Sort a Spreadsheet By Cell Color?How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?How Can I Read Custom Summary Information Properties for an Excel File?How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?How Can I Import Multiple Worksheets into an Access Database?How Can I Search For Values in an Excel Worksheet?How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?How Can I Determine the Value of the Last Cell in an Excel Range?How Can I Convert an Excel Serial Number to a Date and Then Back?How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?How Can I Copy Data from Excel to PowerPoint?How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?How Can I Update and Then Break All the Links in an Excel Spreadsheet?How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?How Can I Add a Background Picture to an Excel Worksheet?How Can I Replace Numeric Values in an Excel Spreadsheet?How Can I Copy Data From One Spreadsheet to Another?How Can I Replace Text in an Excel Spreadsheet?How Can I Set the Print Area in an Excel Spreadsheet?How Can I Delete Specified Rows in an Excel Spreadsheet?How Can I Color Every Other Row in an Excel Spreadsheet?How Can I Copy Selected Columns From a CSV File to an Excel File?How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?How Can I Export an HTA Table to Excel?How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?How Can I Assign a Background Color to Cells in a Spreadsheet and Then β€œSum” Those Cells?How Can I Sort a Row in an Excel Spreadsheet?How Can I Remove the Password When Opening an Excel Spreadsheet?How Can I Import an Excel Spreadsheet Into an Access Database?How Can I Save a Table in an Access Database as a Spreadsheet?How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?How Can I Configure Excel to Autosave Every 5 Minutes?How Can I Save an Excel Chart as a Picture?How Can I Change the Font Color in Excel If a Specified Condition is Met?How Can I Convert an Excel Spreadsheet to XML?How Can I Set the Default File Path in Excel to a User’s Home Directory?How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?How Can I Add a Total Row to an Excel Spreadsheet?How Can I Create a Custom Date Format in Microsoft Excel?How Can I Keep the Screen From Updating While Running an Excel Script?How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?How Can I Determine the Background Color of a Spreadsheet Cell?How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?How Can I Copy a Worksheet to a New Spreadsheet?How Can I Use Windows PowerShell to Automate Microsoft Excel?How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?How Can I Add a COUNTIF Formula to an Excel Spreadsheet?How Can I Copy All the Comments From an Excel Worksheet to a Word Document?How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?How Can I Create a New Excel Spreadsheet at Midnight Each Night?How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?How Can I Convert a Number to a Date in Excel?How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?How Can I Determine the Last Row in an Excel Spreadsheet?How Can I Remove All the Formatting from an Excel Spreadsheet?How Can I Change the Footer in an Excel Spreadsheet?How Can I Add Additional Worksheets to an Excel Workbook?How Can I Sort Worksheets in a Workbook?How Can I Import a Fixed-Width File into Microsoft Excel?How Can I Add a Hyperlink to an Excel Spreadsheet?How Can I Build an Array from a Column of Data in Excel?How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?How Can I Center Text in an Excel Cell?How Can I Change the Background Color of a Cell Depending on the Date?How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?How Can I Insert a Column into a Spreadsheet?How Can I Save a Single Excel Worksheet to a CSV File?How Can I Password-Protect an Excel Spreadsheet?How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

How to use Visual Basic for Applications (VBA) to change UserForms in Excel

How to Programmatically Manipulate a UserForm

How to Programmatically Create UserForms in Visual Basic for Applications

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
β€’ How Can I Import a Fixed-Width Data File into Microsoft Excel?
β€’ How Can I Sort a Spreadsheet By Cell Color?
β€’ How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
β€’ How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
β€’ How Can I Read Custom Summary Information Properties for an Excel File?
β€’ How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Search For Values in an Excel Worksheet?
β€’ How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
β€’ How Can I Determine the Value of the Last Cell in an Excel Range?
β€’ How Can I Convert an Excel Serial Number to a Date and Then Back?
β€’ How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
β€’ How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
β€’ How Can I Update and Then Break All the Links in an Excel Spreadsheet?
β€’ How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Add a Background Picture to an Excel Worksheet?
β€’ How Can I Replace Numeric Values in an Excel Spreadsheet?
β€’ How Can I Copy Data From One Spreadsheet to Another?
β€’ How Can I Replace Text in an Excel Spreadsheet?
β€’ How Can I Set the Print Area in an Excel Spreadsheet?
β€’ How Can I Delete Specified Rows in an Excel Spreadsheet?
β€’ How Can I Color Every Other Row in an Excel Spreadsheet?
β€’ How Can I Copy Selected Columns From a CSV File to an Excel File?
β€’ How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
β€’ How Can I Export an HTA Table to Excel?
β€’ How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
β€’ How Can I Assign a Background Color to Cells in a Spreadsheet and Then β€œSum” Those Cells?
β€’ How Can I Sort a Row in an Excel Spreadsheet?
β€’ How Can I Remove the Password When Opening an Excel Spreadsheet?
β€’ How Can I Import an Excel Spreadsheet Into an Access Database?
β€’ How Can I Save a Table in an Access Database as a Spreadsheet?
β€’ How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
β€’ How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
β€’ How Can I Configure Excel to Autosave Every 5 Minutes?
β€’ How Can I Save an Excel Chart as a Picture?
β€’ How Can I Change the Font Color in Excel If a Specified Condition is Met?
β€’ How Can I Convert an Excel Spreadsheet to XML?
β€’ How Can I Set the Default File Path in Excel to a User’s Home Directory?
β€’ How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
β€’ How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
β€’ How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
β€’ How Can I Add a Total Row to an Excel Spreadsheet?
β€’ How Can I Create a Custom Date Format in Microsoft Excel?
β€’ How Can I Keep the Screen From Updating While Running an Excel Script?
β€’ How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
β€’ How Can I Determine the Background Color of a Spreadsheet Cell?
β€’ How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
β€’ How Can I Copy a Worksheet to a New Spreadsheet?
β€’ How Can I Use Windows PowerShell to Automate Microsoft Excel?
β€’ How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
β€’ How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
β€’ How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
β€’ How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
β€’ How Can I Create a New Excel Spreadsheet at Midnight Each Night?
β€’ How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
β€’ How Can I Convert a Number to a Date in Excel?
β€’ How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
β€’ How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
β€’ How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
β€’ How Can I Determine the Last Row in an Excel Spreadsheet?
β€’ How Can I Remove All the Formatting from an Excel Spreadsheet?
β€’ How Can I Change the Footer in an Excel Spreadsheet?
β€’ How Can I Add Additional Worksheets to an Excel Workbook?
β€’ How Can I Sort Worksheets in a Workbook?
β€’ How Can I Import a Fixed-Width File into Microsoft Excel?
β€’ How Can I Add a Hyperlink to an Excel Spreadsheet?
β€’ How Can I Build an Array from a Column of Data in Excel?
β€’ How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
β€’ How Can I Center Text in an Excel Cell?
β€’ How Can I Change the Background Color of a Cell Depending on the Date?
β€’ How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
β€’ How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
β€’ How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
β€’ How Can I Insert a Column into a Spreadsheet?
β€’ How Can I Save a Single Excel Worksheet to a CSV File?
β€’ How Can I Password-Protect an Excel Spreadsheet?
β€’ How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
β€’ How Can I Randomly Assign a Font to Characters in a Word Document?
β€’ How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
β€’ How Can I Count the Number of Sentences and Paragraphs in a Word Document?
β€’ How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
β€’ How Can I Tally All the Items in a Microsoft Word Document?
β€’ How Can I Reset the Revision Number of a Word Document to 1?
β€’ How Can I Retrieve Field Values in a Microsoft Word Document?
β€’ How Can I Change the Paragraph Case in Microsoft Word?
β€’ How Can I Insert a Manual Line Break into a Microsoft Word Document?
β€’ How Can I Search For and Modify Formatted Text in a Word Document?
β€’ How Can I Search For Red Text in a Microsoft Word Document?
β€’ How Can I Change the Default File Save Format in Microsoft Word?
β€’ How Can I Put the File Name in the Footer of a Microsoft Word Document?
β€’ How Can I Add a Page X of Y Footer to a Microsoft Word Document?
β€’ How Can I Save Each Paragraph in a Word Document as a Database Record?
β€’ How Can I Replace a Specified Font in a Microsoft Word Document?
β€’ How Can I Apply a Theme to a Microsoft Word Document?
β€’ How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
β€’ How Can I Insert a Date Field in Word?
β€’ How Can I Add a New Item to the Microsoft Word AutoCorrect List?
β€’ How Can I Clear All the Formatting From a Microsoft Word Document?
β€’ How Can I Change the Default Highlight Color for a Microsoft Word Document?
β€’ How Can I Run a Macro After Opening Up Word?
β€’ How Can I Center-Align a Picture in a Word Document?
β€’ How Can I Change an Existing Hyperlink in a Microsoft Word Document?
β€’ How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
β€’ How Can I Insert Text Into an Existing Microsoft Word Bookmark?
β€’ How Can I Get a Total Page Count for All the Word Documents in a Folder?
β€’ How Can I Set the Document Orientation in Microsoft Word to Landscape?
β€’ How Can I Replace Text in a Microsoft Word Document?
β€’ How Can I Search For (and Reformat) Highlighted Text in a Word Document?
β€’ How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
β€’ How Can I Get a List of Available Metadata for Microsoft Office Documents?
β€’ How Can I Add Multiple Tables to a Word Document?
β€’ How Can I Search a Word Document for All the Words in Double Brackets?
β€’ How Can I Add Centered Page Numbers to the Footer of a Word Document?
β€’ How Can I Create a Table and Fill the First Column With a Range of Dates?
β€’ How Can I Rename a Word Document Using the First Three Characters in That Document?
β€’ How Can I Search for and Highlight Words in a Microsoft Word Document?
β€’ How Can I Right-Align a Single Column in a Word Table?
β€’ How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
β€’ How Can I Change the Font Name and Size for an Entire Word Document?
β€’ How Can I Set Word’s Revision View Mode to Final?
β€’ How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
β€’ How Can I Set Word’s Default File Location to be the User’s Home Directory?
β€’ How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
β€’ How Can I Change File Locations for Microsoft Word?
β€’ How Can I Insert a Symbol into a Word Document?
β€’ How Can I Hide a Specific Toolbar in Microsoft Word?
β€’ How Can I Extract Word Paragraphs That Use a Specific Style?
β€’ How Can I Insert Files into a Word Document?
β€’ How Can I Convert 1,000 .RTF Files to Word Documents?
β€’ How Can I Add a Hyperlink to a Word Document?
β€’ How Can I Put the User Name into the Footer of a Microsoft Word Document?
β€’ How Can I Determine Which Version of Word is Installed on a Computer?
β€’ How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
β€’ How Can I Import Multiple Worksheets into an Access Database?
β€’ How Can I Add a Record to a Database Using Windows PowerShell?
β€’ How Can I Delete a Set of Records From an Access Database?
β€’ How Can I Be Notified if an Access Database File Exceeds a Specific Size?
β€’ How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
β€’ How Can I List All the Fields and Data Types in an Access Database?
β€’ How Can I Append a Value to a Specified Field For Each Record in a Database?
β€’ How Can I Add the Contents of a Group of Text Files to an Access Database?
β€’ How Can I Import a .CSV File into an Access Database?
β€’ How Can I List All the Access Database Files on a Computer?
β€’ How Can I Print a Microsoft Access Report?
β€’ How Can I Compact an Access Database?
β€’ How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
β€’ How Can I List All the Tables in an Access Database?
β€’ How Can I Determine Which Version of Access was Used to Create a Database?
β€’ How Can I Delete a Table from a Jet Database?

Microsoft Outlook
β€’ How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
β€’ How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
β€’ How Can I Set a Reminder on All My Outlook Appointments?
β€’ How Can I Schedule an All-Day Event in Microsoft Outlook?
β€’ How Can I Run a Script Any Time Outlook is Started?
β€’ How Can I Determine the Age, in Years, of an Outlook Contact?
β€’ How Can I Save the Attachments for All My New Outlook Messages?
β€’ How Can I Convert an Extension Number to an Actual Phone Number?
β€’ How Can I Create a New Folder in Microsoft Outlook?
β€’ How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
β€’ How Can I Get a List of Appointments for a Specific Month?
β€’ How Can I Save Emails That Are More Than One Month Old?
β€’ How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
β€’ How Can I Save All My Contacts as VCards?
β€’ How Can I Delete Unread Emails That Are More Than 6 Months Old?
β€’ How Can I Randomly Select an Email From an Outlook Mail Folder?
β€’ How Can I List All the Meetings Scheduled By a Specified Person?
β€’ How Can I Filter Outlook Messages By Email Address?
β€’ How Can I List All the Members of a Microsoft Outlook Distribution List?
β€’ How Can I Determine the Follow-Up Status of Outlook Emails?
β€’ How Can I Tell If Any of My Contacts Have a Birthday This Month?
β€’ How Can I Delete All the Messages in My Sent Items Folder?
β€’ How Can I Start Outlook If It Isn’t Already Running?
β€’ How Can I Connect to the Junk Mail Folder in Outlook?
β€’ How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
β€’ How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
β€’ How Can I Delete All the Email Sent From a Specific Person?
β€’ How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
β€’ How Can I Get Total Size and Number of Items in an Outlook Folder?
β€’ How Can I Convert an Outlook Email Message into a Text File?
β€’ How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
β€’ How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
β€’ How Can I Copy Data from Excel to PowerPoint?
β€’ How Can I Apply a New Template to a PowerPoint Presentation?
β€’ How Can I Configure PowerPoint to Print Handouts Instead of Slides?
β€’ How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
β€’ How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
β€’ How Can I Change the User Information in Microsoft Office?

Download Attachment At Bottom For Live Links

The Hey, Scripting Guy! Archive: Microsoft Office

Microsoft Excel
• How Can I Import a Fixed-Width Data File into Microsoft Excel?
• How Can I Sort a Spreadsheet By Cell Color?
• How Can I Check the Value of a Specific Cell in a Bunch of Excel Spreadsheets?
• How Can I Mark the First Occurrence of a Number in an Excel Spreadsheet?
• How Can I Read Custom Summary Information Properties for an Excel File?
• How Can I Change the Color of a Spreadsheet Cell Based on a Range of Values?
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Search For Values in an Excel Worksheet?
• How Can I Cut a Row From One Excel Spreadsheet and Paste That Row Into Another Spreadsheet?
• How Can I Determine the Value of the Last Cell in an Excel Range?
• How Can I Convert an Excel Serial Number to a Date and Then Back?
• How Can I Insert New Rows Above the Last Row in an Excel Spreadsheet?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Retrieve Hyperlink Information From an Excel Spreadsheet?
• How Can I Insert Blank Rows (and Formulas) Into an Excel Worksheet?
• How Can I Update and Then Break All the Links in an Excel Spreadsheet?
• How Can I Open All the Excel Spreadsheets in a Folder and Run a Specified Macro Found in Each of Those Spreadsheets?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Add a Background Picture to an Excel Worksheet?
• How Can I Replace Numeric Values in an Excel Spreadsheet?
• How Can I Copy Data From One Spreadsheet to Another?
• How Can I Replace Text in an Excel Spreadsheet?
• How Can I Set the Print Area in an Excel Spreadsheet?
• How Can I Delete Specified Rows in an Excel Spreadsheet?
• How Can I Color Every Other Row in an Excel Spreadsheet?
• How Can I Copy Selected Columns From a CSV File to an Excel File?
• How Can I Change the Background Color of Spreadsheet Rows That Have a Date in a Specified Cell?
• How Can I Export an HTA Table to Excel?
• How Can I Delete All the Worksheets in a Spreadsheet Except for the First Worksheet?
• How Can I Assign a Background Color to Cells in a Spreadsheet and Then “Sum” Those Cells?
• How Can I Sort a Row in an Excel Spreadsheet?
• How Can I Remove the Password When Opening an Excel Spreadsheet?
• How Can I Import an Excel Spreadsheet Into an Access Database?
• How Can I Save a Table in an Access Database as a Spreadsheet?
• How Can I Use a Blank Row to Separate Data in an Excel Spreadsheet?
• How Can I Specify the Number of Decimal Places to Display in an Excel Spreadsheet?
• How Can I Configure Excel to Autosave Every 5 Minutes?
• How Can I Save an Excel Chart as a Picture?
• How Can I Change the Font Color in Excel If a Specified Condition is Met?
• How Can I Convert an Excel Spreadsheet to XML?
• How Can I Set the Default File Path in Excel to a User’s Home Directory?
• How Can I Compare a List of Names in One Excel Column to a List of Names in Another Column?
• How Can I Convert a Tilde-Delimited File to Microsoft Excel Format?
• How Can I Search Active Directory for User Names Stored in an Excel Spreadsheet?
• How Can I Add a Total Row to an Excel Spreadsheet?
• How Can I Create a Custom Date Format in Microsoft Excel?
• How Can I Keep the Screen From Updating While Running an Excel Script?
• How Can I Use Information in an Excel Spreadsheet to Rename a Set of Folders?
• How Can I Determine the Background Color of a Spreadsheet Cell?
• How Can I Make the First Character in a Cell Uppercase and All the Other Characters Lowercase?
• How Can I Copy a Worksheet to a New Spreadsheet?
• How Can I Use Windows PowerShell to Automate Microsoft Excel?
• How Can I Tell If a Specified Worksheet Exists in an Excel Workbook?
• How Can I Add a COUNTIF Formula to an Excel Spreadsheet?
• How Can I Copy All the Comments From an Excel Worksheet to a Word Document?
• How Can I Change All the Lowercase Letters in an Excel Worksheet to Uppercase Letters?
• How Can I Create a New Excel Spreadsheet at Midnight Each Night?
• How Can I Select a Column of Data in Excel and Then Paste that Data into a Text File?
• How Can I Convert a Number to a Date in Excel?
• How Can I Locate and Replace Information for a Specific Item in a Spreadsheet?
• How Can I Save an Excel Spreadsheet, and Then Save a Copy as HTML?
• How Can I Format an Excel Spreadsheet So It Retains Leading Zeroes?
• How Can I Determine the Last Row in an Excel Spreadsheet?
• How Can I Remove All the Formatting from an Excel Spreadsheet?
• How Can I Change the Footer in an Excel Spreadsheet?
• How Can I Add Additional Worksheets to an Excel Workbook?
• How Can I Sort Worksheets in a Workbook?
• How Can I Import a Fixed-Width File into Microsoft Excel?
• How Can I Add a Hyperlink to an Excel Spreadsheet?
• How Can I Build an Array from a Column of Data in Excel?
• How Can I Copy Column C of One Worksheet to Column A of a Second Worksheet?
• How Can I Center Text in an Excel Cell?
• How Can I Change the Background Color of a Cell Depending on the Date?
• How Can I Delete Data from a Spreadsheet Yet Keep All the Formatting?
• How Can I Open an Excel Workbook and Retrieve the Names of All the Worksheets?
• How Can I Tell if an Excel Workbook is Open and, If It Isn’t, Open It?
• How Can I Insert a Column into a Spreadsheet?
• How Can I Save a Single Excel Worksheet to a CSV File?
• How Can I Password-Protect an Excel Spreadsheet?
• How Can I Make Changes to and Then Re-Save an Existing Excel Spreadsheet?

Microsoft Word
• How Can I Randomly Assign a Font to Characters in a Word Document?
• How Can I Find a Word in a Document and Change the Background Color of the Paragraph Where That Word Appears?
• How Can I Count the Number of Sentences and Paragraphs in a Word Document?
• How Can I Extract Specific Information From a Word Document and Then Use That Information to Rename the Document?
• How Can I Tally All the Items in a Microsoft Word Document?
• How Can I Reset the Revision Number of a Word Document to 1?
• How Can I Retrieve Field Values in a Microsoft Word Document?
• How Can I Change the Paragraph Case in Microsoft Word?
• How Can I Insert a Manual Line Break into a Microsoft Word Document?
• How Can I Search For and Modify Formatted Text in a Word Document?
• How Can I Search For Red Text in a Microsoft Word Document?
• How Can I Change the Default File Save Format in Microsoft Word?
• How Can I Put the File Name in the Footer of a Microsoft Word Document?
• How Can I Add a Page X of Y Footer to a Microsoft Word Document?
• How Can I Save Each Paragraph in a Word Document as a Database Record?
• How Can I Replace a Specified Font in a Microsoft Word Document?
• How Can I Apply a Theme to a Microsoft Word Document?
• How Can I Add the Last-Saved Date to the Footer of a Microsoft Word Document?
• How Can I Insert a Date Field in Word?
• How Can I Add a New Item to the Microsoft Word AutoCorrect List?
• How Can I Clear All the Formatting From a Microsoft Word Document?
• How Can I Change the Default Highlight Color for a Microsoft Word Document?
• How Can I Run a Macro After Opening Up Word?
• How Can I Center-Align a Picture in a Word Document?
• How Can I Change an Existing Hyperlink in a Microsoft Word Document?
• How Can I Add a Blank Line Between the Existing Paragraphs in a Word Document?
• How Can I Insert Text Into an Existing Microsoft Word Bookmark?
• How Can I Get a Total Page Count for All the Word Documents in a Folder?
• How Can I Set the Document Orientation in Microsoft Word to Landscape?
• How Can I Replace Text in a Microsoft Word Document?
• How Can I Search For (and Reformat) Highlighted Text in a Word Document?
• How Can I Get a List of the Unique Words Used in a Microsoft Word Document?
• How Can I Get a List of Available Metadata for Microsoft Office Documents?
• How Can I Add Multiple Tables to a Word Document?
• How Can I Search a Word Document for All the Words in Double Brackets?
• How Can I Add Centered Page Numbers to the Footer of a Word Document?
• How Can I Create a Table and Fill the First Column With a Range of Dates?
• How Can I Rename a Word Document Using the First Three Characters in That Document?
• How Can I Search for and Highlight Words in a Microsoft Word Document?
• How Can I Right-Align a Single Column in a Word Table?
• How Can I Boldface a Specific Word Throughout a Microsoft Word Document?
• How Can I Change the Font Name and Size for an Entire Word Document?
• How Can I Set Word’s Revision View Mode to Final?
• How Can I Insert Multiple Files Into a Word Document, Putting a Page Break Between Each File?
• How Can I Set Word’s Default File Location to be the User’s Home Directory?
• How Can I Open Word with the Cursor Positioned at the Start of a Specified Line?
• How Can I Change File Locations for Microsoft Word?
• How Can I Insert a Symbol into a Word Document?
• How Can I Hide a Specific Toolbar in Microsoft Word?
• How Can I Extract Word Paragraphs That Use a Specific Style?
• How Can I Insert Files into a Word Document?
• How Can I Convert 1,000 .RTF Files to Word Documents?
• How Can I Add a Hyperlink to a Word Document?
• How Can I Put the User Name into the Footer of a Microsoft Word Document?
• How Can I Determine Which Version of Word is Installed on a Computer?
• How Can I Save Word Documents as Text Files By Using a Script?

Microsoft Access
• How Can I Import Multiple Worksheets into an Access Database?
• How Can I Add a Record to a Database Using Windows PowerShell?
• How Can I Delete a Set of Records From an Access Database?
• How Can I Be Notified if an Access Database File Exceeds a Specific Size?
• How Can I Retrieve the Field Size and a Sample Record For All the Tables and Fields in an Access Database?
• How Can I List All the Fields and Data Types in an Access Database?
• How Can I Append a Value to a Specified Field For Each Record in a Database?
• How Can I Add the Contents of a Group of Text Files to an Access Database?
• How Can I Import a .CSV File into an Access Database?
• How Can I List All the Access Database Files on a Computer?
• How Can I Print a Microsoft Access Report?
• How Can I Compact an Access Database?
• How Can I Use Windows PowerShell to Pull Records From a Microsoft Access Database?
• How Can I List All the Tables in an Access Database?
• How Can I Determine Which Version of Access was Used to Create a Database?
• How Can I Delete a Table from a Jet Database?

Microsoft Outlook
• How Can I Schedule a Meeting Each Monday Through Friday For Two Weeks?
• How Can I Sort Items Retrieved From a Microsoft Outlook Folder?
• How Can I Set a Reminder on All My Outlook Appointments?
• How Can I Schedule an All-Day Event in Microsoft Outlook?
• How Can I Run a Script Any Time Outlook is Started?
• How Can I Determine the Age, in Years, of an Outlook Contact?
• How Can I Save the Attachments for All My New Outlook Messages?
• How Can I Convert an Extension Number to an Actual Phone Number?
• How Can I Create a New Folder in Microsoft Outlook?
• How Can I List the Addresses in My Sent Items Folder in an Excel Spreadsheet?
• How Can I Get a List of Appointments for a Specific Month?
• How Can I Save Emails That Are More Than One Month Old?
• How Can I Create a New Outlook Distribution List Based On the Membership of an Active Directory Group?
• How Can I Save All My Contacts as VCards?
• How Can I Delete Unread Emails That Are More Than 6 Months Old?
• How Can I Randomly Select an Email From an Outlook Mail Folder?
• How Can I List All the Meetings Scheduled By a Specified Person?
• How Can I Filter Outlook Messages By Email Address?
• How Can I List All the Members of a Microsoft Outlook Distribution List?
• How Can I Determine the Follow-Up Status of Outlook Emails?
• How Can I Tell If Any of My Contacts Have a Birthday This Month?
• How Can I Delete All the Messages in My Sent Items Folder?
• How Can I Start Outlook If It Isn’t Already Running?
• How Can I Connect to the Junk Mail Folder in Outlook?
• How Can I Get Access to a Mail Folder That Isn’t a Subfolder of My Outlook Inbox?
• How Can I Get a List of All the Senders’ Email Addresses in an Outlook Folder?
• How Can I Delete All the Email Sent From a Specific Person?
• How Can I Write a Script That Accesses All the Subfolders in My Outlook Inbox?
• How Can I Get Total Size and Number of Items in an Outlook Folder?
• How Can I Convert an Outlook Email Message into a Text File?
• How Can I Get a List of All the .PST Files on a Computer?

Microsoft PowerPoint
• How Can I Retrieve the User Name and User Initials From Microsoft PowerPoint?
• How Can I Copy Data from Excel to PowerPoint?
• How Can I Apply a New Template to a PowerPoint Presentation?
• How Can I Configure PowerPoint to Print Handouts Instead of Slides?
• How Can I Run a PowerPoint Slide Show From a Script?

Other Office Tasks
• How Can I Use Windows PowerShell to Look at All the Microsoft Office Documents in a Folder?
• How Can I Change the User Information in Microsoft Office?

How to return the result of a cell's formula as an argument within a
running macro?

Hi to All,

I'm sorting a table to extract data which becomes a new variable
array (variable number of rows but columns don't change and top left
is always the same cell address - in this case C7)

Alongside this array in cell R5 I have a formula which automatically
calculates the variable array dimensions. Let's say for example this
returns the text string C7:M32

The macro gets to the point of having done the sorting, the new data is
all nicely arranged in the variable array and cell R5 has updated
itself. Perfect so far!

I then want the macro to set the print area to the array dimensions
calculated in R5 and this is where I get stuck. I have found heaps of
examples on how macros can write data to a cell but nothing on how a
macro can read data from a cell to be used as an argument within the
macro. Perplexed. A great big "Thanks!" to anyone who can help.

Cheers,
RonW.

PS: please don't assume I know anything at all about VBA and macro
programming . . .

I worked on this macro for about 2 hours, with only partial success.

I have credit card data (will attached next post) sorted by tender type. I recorded teh subtotal feature, after selecting the data. I used teh go to, special, last cell, feature to get the end cell for selection, and also set the print area to this range. I did it in relative mode.

the macro worked, but when I also tried to insert three rows above the "grand total" cell, then insert a sub total of just master card adn visa, it in one of these added rows didn't work upon playback.

also - Can I lock the position of my macro buttons? IN properties it's set to locked but they still move when teh data changes. Can i put them on teh tool bars?

HI.
I have about 8 pages of data i need to print.
I've been searching around for a macro that would set my print range automatically on daily bases.
These are the two macros that I found
Sub setprintarea()
Dim myrange As String
myrange = Cells(Rows.Count, 37).End(xlUp).Address
ActiveSheet.PageSetup.PRINTAREA = "$A$1:" & myrange
ActiveWindow.SelectedSheets.PrintOut Copies:=1

End Sub
----------------------------------
Sub PA()
Dim strLCell As String

With ActiveSheet
.UsedRange
'reset last cell
strLCell = .Cells.SpecialCells(xlCellTypeLastCell).Address
'get last cell address
.PageSetup.PRINTAREA = "$A$1:" & strLCell
'set the print area
End With

problem:

my worksheet has data in 37 columns, in first macro, if i change the Cells(Rows.Count, 37). to 36, it will print the correct number of pages with is 8, minus the last column. Now, if i switch replace 36 with 37, it will include the last column in the print range BUT, it will print 8 correct pages plus LOTS of other blank ones...how do i tell the macro to stop at 8 or whatever that page number happen to be.

btw. the second macro does the same thing. good colum number plus infinite pages..

thanks in advance


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