Free Microsoft Excel 2013 Quick Reference

macro to run series of reports to PDF

I use the macro below with Hyperion Enterprise Retreive (an Excel add-in) to
run a series of reports and save them to PDF files. The macro works great
with my Win2000Pro machine running Excel 2000, but I've been upgraded to an
XP machine with Excel 2003. The macro gets stuck on the row:
Application.ActivePrinter = "Acrobat Distiller on Ne00:"
Any thoughts as to why?

Thanks,

Marc

Range("$K$1").Select
ActiveCell.FormulaR1C1 = "report1"
Range("$c$21").Select

Dim PSFileName As String
Application.ActivePrinter = "Acrobat Distiller on Ne00:"
Application.Goto Reference:="r1c1"
Let PSFileName = Application.ActiveCell
SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83"
ActiveSheet.PrintOut , PrintToFile:=True
PSFileName = Chr(34) & PSFileName & Chr(34)

Range("$K$1").Select
ActiveCell.FormulaR1C1 = "report2"
Range("$c$21").Select

Application.ActivePrinter = "Acrobat Distiller on Ne00:"
Application.Goto Reference:="r1c1"
Let PSFileName = Application.ActiveCell
SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83"
ActiveSheet.PrintOut , PrintToFile:=True
PSFileName = Chr(34) & PSFileName & Chr(34)

The macro continues on for a list of around 50 reports...


I use the macro below with Hyperion Enterprise Retreive (an Excel add-in) to
run a series of reports and save them to PDF files. The macro works great
with my Win2000Pro machine running Excel 2000, but I've been upgraded to an
XP machine with Excel 2003. The macro gets stuck on the row:
Application.ActivePrinter = "Acrobat Distiller on Ne00:"
Any thoughts as to why?

Thanks,

Marc

Range("$K$1").Select
ActiveCell.FormulaR1C1 = "report1"
Range("$c$21").Select

Dim PSFileName As String
Application.ActivePrinter = "Acrobat Distiller on Ne00:"
Application.Goto Reference:="r1c1"
Let PSFileName = Application.ActiveCell
SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83"
ActiveSheet.PrintOut , PrintToFile:=True
PSFileName = Chr(34) & PSFileName & Chr(34)

Range("$K$1").Select
ActiveCell.FormulaR1C1 = "report2"
Range("$c$21").Select

Application.ActivePrinter = "Acrobat Distiller on Ne00:"
Application.Goto Reference:="r1c1"
Let PSFileName = Application.ActiveCell
SendKeys PSFileName & "{ENTER}", False
ActiveSheet.PageSetup.PrintArea = "$G$1:$AM$83"
ActiveSheet.PrintOut , PrintToFile:=True
PSFileName = Chr(34) & PSFileName & Chr(34)

The macro continues on for a list of around 50 reports...

Is it possilbe to create several individual macros, then create one main macro to run all of the other macros in order??

Hi,

I've done quite a bit of searching in the forum and online and haven't found anything that's generic and can be used at anytime.

What I'm looking for is a way or for code that tells you how long it takes a macro to run from start to finish, something that can be used to time any macro. I've seen some threads in the forum where people indicate that it took x amount of seconds for their macro to run but not sure how to do it.

Thank You.
-Ecow

I've been displaying an Excel report in a browser window
via Java's getAppletContext().showDocument(url,"_blank")
with no problems. I then tried to do the same for
printing only after formatting I wanted to use the
following statements to print and exit out of Excel. The
problem is the application.quit is causing the macro to
run twice and give me two print outs. When I remove the
application.quit I get one print out but the browser
window stays open with my Excel report visible. Anyone
have any ideas as to why it's executing twice?

My code in Auto_Open is as follows:

Application.Visible = False
Application.WindowState = xlMinimized
Application.DisplayAlerts = False
Application.DefaultWebOptions.DownloadComponents =
False
Main

I need to create a macro that will plot a series of data. The only experience I have with macros is using the record option to create a simple module. The data will begin in a colum as x's and alternate between x's and y's for the following colums. Please help!

I have to run a certain VB macro every half hour, starting at 07:00 am till 17:30 in the afternoon.
At the moment I am using a series of

Application.OnTime Time Value ("07:00:00"), "macro to run"
Application.OnTime Time Value ("07:30:00"), "macro to run"
etc etc...

Surely there must be a more "economic" way to do it (a loop?)

Many thanks

BC

I have the 2007 version of excel and started writing some basic macros. I do not have programming experience. The problem i am having is when i record my macro(s) and then run them in my spreadsheet they run from the cells where i recorded the macro. I would like to be able to run the macro from a specific cell.

Example. If i have formula that i run constantly and want to run the macro, i would like to be in the cell, hit my hot key and have the macro run from that cell. I am using this for a stock portfolio tracker, so lets assume i have this set up:

- A1 - number of shares
- B1 - stock price
- C1 - commission

In cell D1 i would like to run my macro that multiply's A1 by B1 and adds C1 to give me my cost basis. Then i would enter in similar info for another stock in row 2 and run the macro.

Can anyone inform on how to create a macro that i can run from specified cell rather then the cell(s) it was recorded in?

Thanks for the help

Quick question I want to get this macro to run on every sheet in the workbook that starts with A, C or P.


	VB:
	
 CopyJob() 
    Range("A1:N55").Select 
    Selection.Copy 
    ActiveWindow.SmallScroll Down:=15 
    Range("A63").Select 
    Selection.ClearComments 
    Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _ 
    xlNone, SkipBlanks:=False, Transpose:=False 
    Selection.ClearComments 
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ 
    SkipBlanks:=False, Transpose:=False 
    Application.CutCopyMode = False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I thought it would be as simple as

	VB:
	
 CopyJob() 
     
    Dim wSheet As Worksheet 
     
    For Each wSheet In Workbook 
        If Left(wSheet.Name, 2) = "AJ" Or "CJ" Or "PJ" Then 
            Range("A1:N55").Select 
            Selection.Copy 
            ActiveWindow.SmallScroll Down:=15 
            Range("A63").Select 
            Selection.ClearComments 
            Selection.PasteSpecial Paste:=xlPasteFormulasAndNumberFormats, Operation:= _ 
            xlNone, SkipBlanks:=False, Transpose:=False 
            Selection.ClearComments 
            Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _ 
            SkipBlanks:=False, Transpose:=False 
            Application.CutCopyMode = False 
             
             
             
        End If 
    Next wSheet 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
It keeps giving me back an error of type mismatch for

	VB:
	
 

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


Hi guys,

As the title suggests what do I need to name my private sub in order for it to run as soon as someone clicks on a specific sheet?

Similar to the example of sub auto_open to get a macro to run when a workbook opens.

I am not sure what i did wrong. I am trying to get the macro to run on the entire workbook instead of just one tab. It runs the same tab over and over. Here is my macro. TIA

Sub HideRows()
Dim ws As Worksheet, i As Integer
For Each ws In ActiveWorkbook.Worksheets
On Error Resume Next
With Range("H7:U253")
.EntireRow.Hidden = False
For i = 1 To .Rows.Count
If WorksheetFunction.Sum(.Rows(i)) = 0 Then
.Rows(i).EntireRow.Hidden = True
End If
Next i
End With
Next
End Sub

I'm new to macros so forgive me ahead of time. Here's my issue. I've created a macro that amoritzes and aggregates the cash flows from rows of assumptions for each loan. That's great for one scenario, but i would also like to add 8 more scenarios for a total of 9. I have my assumptions next the loan name across many columns. My list of loans are currently in order like this:

Loan 1
Loan 2

I would like to order them like this with mulitple scenarios:

Loan 1 Scenario 1
Loan 1 Scenario 2
Loan 2 Scenario 1
Loan 2 Scenario 2
and so on

Each scenarios cash flows need to be aggregated in its own worksheet (ex. scen1 and scen2 and so on). There will end up being 28 different loans and 9 scenarios (total of 252 rows of assumptions). I'm not sure how to get the cash flows from each scenario into its correct sheet. Because i have it grouped by loan instead of scenario, it will have to move down each loans respective 9 scenarios before moving to the next loan. As it is moving down scenarios it will need to write the related scenario sheet (scen1, scen2...). Each loan will always be run the same number of scenarios as the other bonds.

It would be neat if i could simply turn the scenarios on and off with a check box or even entering in the number of scenarios i want to run out of the 9 for each loan as well as turning on and off the loans i want to run. Way beyound my skills though.

Here is what i have that does one scenario correctly.

Thanks ahead of time,

Darius

Sub Aggregate_My_Principal_Balances()

numLoans = 28

Worksheets("Mod1").Range("C12:aq393").ClearContents
For currLoan = 1 To numLoans

Worksheets("Assum2").Range("Q38") = currLoan

Worksheets("Mod").Range("gi12:hw393").Copy
Worksheets("Mod1").Range("C12:aq393").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False

Next currLoan

End Sub

Hi everyone - new member here and not a VBA programmer at all but trying to add a capability into a spreadsheet I am developing. I would like to figure out how to limit a macro to run only in the workbook in which it was developed, while adding in the tab name to the print header the macro is generating (if possible, setting it to print on a new line in the header, replacing the & " Daily Report" which is currently in the header). The macro (very simple) is below - currently it runs in all open workbooks which is not what I want.

Sub PrintReport()
Dim wks As Worksheet
Dim ftr
ftr = Sheet2.Range("K3").Value
For Each wks In Worksheets
With wks.PageSetup
.CenterHeader = "&"" Times New Roman,Bold""" & ftr & " Daily Report"
End With
Next wks
Application.OnTime Now + TimeValue("00:02:00"), "PrintReport"
End Sub

Thank you in advance for any help you can provide.

Cheers

MadMurr

Hello, I am trying to insert a row above another row with fixed text. I have completed this task, but users will be copying and pasting text into the workbook which might overwrite important cells. Therefore, I need a way of writing some code to have my macro that inserts a row to be running all the time.

Is there a way to have my insert_rows macro to run all the time when the worksheet is loaded and keep running all the time until the user closes Excel ?

Code examples please.

I have a macro that ideally would run automatically whenever the user closes
out of the workbook. I know how to set a macro to run this way when OPENING
the workbook, but is the reverse possible?

I'm trying to get a macro I've designed to run on any worksheet.

However, when I start the macro it errors and needs to be on a worksheet
that has the same name as the one I designed it on.

Is there any way to get the macro to run on a worksheet, regartdless of what
the name is?

I have 8 different macros (all located in the same workbook) that I run. However, I would like a separate macro to run all of the 8 macros. Is this possible? If so, could someone help me?

Please Help!!
Thanks

Experience level: Intermediate Beginner
Excel version: Excel 2007
Access version: Access 2007

I found a conversation concerning "Create an Excel macro to run an MS Access macro" dated October3, 2011 on your web site and attempted to use it to upload football stats from an Excel macro into my Access football database. Below is the code:


	VB:
	
 RunDelQ() 
    Dim ADB As Object 
    Application.DisplayAlerts = False 
    Set ADB = CreateObject("Access.Application") 
    ADB.Visible = False 'True
    ADB.OpenCurrentDatabase ("C:...FBS.accdb") --- Note: I have shortened the path name For this submission 
    ADB.DoCmd.RunMacro "Upload_Stats" 
    Application.DisplayAlerts = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The Access macro I want to run is in a Module I named Upload_stats (same as macro name).

When I run the code I get the follow error message:

Microsoft Office Access can't find the object 'Upload_Stats.'

Following the trouble shooting advise of "Rory - OzMVP", I have changed the line of code "ADB.Visible" to True to ensure the path is correct and database opens correctly, and it does. I also followed his advise an ran the "Upload_Stats" macro from within Access to ensure it runs correctly, and it does. The Access macro is a For-Next loop that opens a Message box that allows me to establish the Excel range to import. Below is a small extract of the loop:


	VB:
	
 
Dim strSheetName, strTableName, strTitle, strRange As String 
Dim intNumber As Integer 
Dim Msg, Style, Title 'Import Range
Dim IBMessage, IBTitle, IBDefault 'Input Box for Range to Import
 
intNumber = 0 
importcount = 1 
 
For importcount = 1 To 51 
     
    Select Case importcount 'Me.OptSelection
    Case Is = 1 'Weekly Game Summary - ASU Fumbles
        strSheetName = "NCAAWeeklyGameSummary_ASU Fumbles.xlsx" 
        strTableName = "tblNCAA_WkGmSum_ASU_Fumbles" 
        strTitle = "Weekly Game Summary - ASU Fumbles" 
        IBDefault = "A1:G_" 
    Case Is = 2 'Weekly Game Summary - ASU Other Participants
        strSheetName = "NCAAWeeklyGameSummary_ASU Other Participants.xlsx" 
        strTableName = "tblNCAA_WkGmSum_ASU_OtherParticipants" 
        strTitle = "Weekly Game Summary - ASU Other Participants" 
        IBDefault = "A1:C_" 
. 
. 
. 
. 
. 
         
        IBMessage = "Enter " & strTitle & Chr(13) & " Range to Import." 
        IBTitle = "Import Range" 
        strRange = InputBox(IBMessage, IBTitle, IBDefault) 
         
        If strRange = "" Then 
            OptSelection.DefaultValue = 0 
        Else 
            DoCmd.TransferSpreadsheet acImport, 8, _ 
            strTableName, "c:TechnolustASUFootballS11StatsNCAA" & strSheetName, True, strRange 
             'OptSelection.DefaultValue = 0
        End If 
         
    Next 

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

Nothing I've attempted has solved the issue.

Any and all help in resolving the error will be greatly appreciated.

michaeld2

I have a customer who wants me to take a scale and send the weight information to an excel sheet along with the date and time of each weight sample. I have connected it and it works great, for testing I made a command click button on an excel sheet that I would click to run the code. The scale sends 14 cells of information to me along with the actual weight being displayed on the screen. My problem is my code works perfect as long as I'm clicking the button to run the code. I worte a code to monitor a cell for changing from "False" to "True" which the scale changes this information when the weight is correct cell D13 will change from False to True. The data is being exchanged via DDE and excel, the weight is enterned in the next empty cell along with the time and date in the next column is the time and date stamp.

Code below
Private Sub CommandButton1_Click()
Dim mycell As Range

Sheets("Sheet1").Select
Range("G13").Select
If [G13].Value = "True" Then
Range("A1").Select
If [A1] > 0.5 Then
'MsgBox (" This Is Working ")

Dim i As Long
Sheet1.Range("A1").Copy
'For i = 20 To 31 Step 1 '12 places for first paste values
Debug.Print i
If IsEmpty(Range("A1").Value) Then
Else
Range("A" & Range("A:A").Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Sheet1.Range("B1").Copy
Debug.Print f
If IsEmpty(Range("B1").Value) Then
Else
Range("B" & Range("B:B").Rows.Count).End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If

End If

End If
End If

End Sub

After I wrote the code if the cell value changes to run my code, I tested it by using an empty cell first and just throwing up a message box if the value changes to prompt me with a message. The problem is when the cell value changes via DDE my Macro does not see the change because the enter key hasnt been pressed.
Code below
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$D$13" Then
MsgBox "woohooo" ' above code was inserted here, and it did not work.
End If
End Sub

If I use the above worksheet change and I enter a value on the keyboard and press enter it works. But, they want an operator weighing parts and the macro to run when the correct weight is true, not by someone pressing enter every time. Any one have any thoughts??

I would like the macro to run when the DDE changes cells G13 to True one time and then wait until it goes false and true again before it runs again. I have attaced the sheet I have been working on. Thanks for any help. You can email attachments directly to me at billf@clarkpulley.com
File attachment info
Hardy Test.xls - works perfect as long as G13 = "True" and the Start Slug Recorder is clicked.
Hardy Test 1.xls - This is the file I'm trying to get when the DDE changes cell G13 from "False" to "True" to run the code for "Start Slug Recorder"

Thanks

I have a macro that ideally would run automatically whenever the user closes
out of the workbook. I know how to set a macro to run this way when OPENING
the workbook, but is the reverse possible?

Hi there,

I have a macro in a file that I want to only be able to run if a certain cell in the worksheet says something in particular. I.e. This macro can only be run on Day 7 of any week. There is a cell on the worksheet that says "Day 7" or whichever day it is. I want to get my macro to run if that cell does say Day 7 and to come up with an error message box if it doesn't. Hope that makes sense! Can anyone help?

Hello All,
Total beginner working on my own. I am a dentist so my abilities are similiar to you guys trying to do your own fillings. LOL
Created my own workbook and surfed around and found some formulas to do some basic calcs.
Tried to insert a macro for hiding zeros and totally screwed up my workbook and had to start all over. (yes I did try to save a copy before inserting but somehow it didn't work)
I am looking for help to insert a macro to hide rows of zeros. I export reports from Quickbooks to a workbook in Excel (2003).
In order to have the Excel sheets match the reports from Quickbooks each time I update I had to select the reports from Quickbooks to include all data (even entries that have all zeros).
I'm sure that there are many improvements that could be made in this workbook. If you see something obvious that I missed please dont hesitate to let me know.

Here is the scenario. I have a template that other users fill out and I run a macro based on the data that they populate in the template. I have three destinations for this data depending on how a particular cell is coded (validation list box with three choices). Currently I have to determine the how the cell is coded and run the corresponding macro. I would like to have one overall macro that can look at the cell with the coding and determine which of the three macros to run and then execute that macro. I was originally thinking of something like an If/Else statement, but that will only work if there are two options. Is there something else that I could use that will make this work?

I'm trying to get a macro I've designed to run on any worksheet.

However, when I start the macro it errors and needs to be on a worksheet
that has the same name as the one I designed it on.

Is there any way to get the macro to run on a worksheet, regartdless of what
the name is?

I have a macro that hides blank rows whenever there is no value in a
particular cell. Two things:
1.) How do I program the macro to run everytime the file is opened

2.) How do I also program the macro to run every time someone adds a value
in any of the cells that determine whether its row should be hidden? In other
words, column B contains the cells in which when blank, the row is hidden.
Now I want to unhide the row if someone enters data in the cell in column B

(Let me know if I need to explain some more)