Process Of Executing Macro

Aug 30, 2009

I wanted to know If there a way to hide the process of executing macrob (vba)?
I have a long macro that runs on a file and I want to hide the process. I know there is an option to it, I just don't know it.

View 5 Replies


Time Delay When Executing Macro In VBA

Mar 14, 2013

I am trying to see if it is possible to create an intentional delay when executing a macro. Basically I would like to pause the macro for 5 seconds and then continue with the rest of the code.

Is such a thing possible with Excel?

View 2 Replies View Related

When Executing The Macro, I Get Double The Page References

Aug 9, 2008

I am using Office 2007 and perhaps that is my issue. When executing the macro, I get double the page references than there actually are. My workbook has approx. 330 pages and when the macro finishes I have over 700. Certain tabs have a different page setup, and I assume that the macor only takes the regular pagination into consideration. I have landscape as well as portrait setup.

I also receive the errors upon completion in the bolded areas:

Sub CreateTableOfContents()
' Copyright 1999
' Determine if there is already a Table of Contents
TOCFound = False
For Each s In Worksheets
If s.Name = "Table of Contents" Then
TOCFound = True
Exit For
End If

View 9 Replies View Related

Executing Part Of Macro Only On Cells Meeting A Condition

Jul 11, 2009

I'm working on a spreadsheet designed to track total overtime hours worked in a year; on the spreadsheeet is a column to keep track of each day's total OT, the week's total OT, as well as a column to track the year's total. There is a formula in the week total, but the year total is calculated via a macro (day of the week total, added to the existing year total, result updated, so, each day has it's own button and macro). We have a shift that works a different week than the shift that needs to track overtime, but still must be included in the list.

Therefore, I created a column to place the shift designator so there can be recognizable diffrerentiation. With quite a bit of help from this board, and others, I've created (or been kindly given) the following macro (this is just a part of it) to total the day's overtime and existing year overtime and input the result into the cell. I now need to have this executed only when the condition I specify (say, in cell D1) is met (that would be the shift, for example the text M1 or SST). Please note, the week totals are only for user reference - they do not come into play for calculations of year totals. The below macro actually takes the totals from a day of the week and adds it to the existing year total, placing the result in the year total column.

View 2 Replies View Related

Accidently Executing A Macro On The Wrong Sheet Within A Workbook

Sep 3, 2009

When running a macro is there a way you can execute code on another sheet within a workbook without being in the original sheet?

I seem to have a problem if I accidentally run a macro not on the original sheet whereby the code is executed and fills over data not in the desired sheet and the previous cells information is lost and the only way to fix the problem is to close the workbook and re-open a saved copy as you can't undo?

View 9 Replies View Related

Log Macro Process

Jun 30, 2006

I need to know if it's possible to log the process of a series of macros
either to txt file or to a Sheet, (either way won't matter but txt file would be perfect)

For example:

Sub MyMacro1 ()
End Sub

Sub MyMacro2 ()
End Sub

If this process was logged, then the log file would look something like:

Date Time : Sub MyMacro1
Date Time : End Sub
Date Time : Sub MyMacro2
Date Time : End Sub

But it would be a in text just like a log file.
Is this possible with Excel ?

The reason is because my Workbook is huge and I am forever tweaking and adjusting or fixing and I require reference points.

View 4 Replies View Related

Process Every Nth Row In Macro Loop

Jun 19, 2009

I have the following macro. I need a loop that runs untill there is no more data. The loop should increment at each pass the following 2 Ranges and 1 Rows by 1. What is the VB code that will accomplish this for Excel 2003?

ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.LargeScroll ToRight:=-1
Selection.Delete Shift:=xlUp

View 4 Replies View Related

Macro Progress Bar To Show % Process Done

Oct 25, 2008

As i have a pretty "complex" macro, which takes around 20-30 minutes to do (honestly)....

I was wondering if there was a way to create a progress bar just showing me how many % it has done so for?

Or even tell me which sub routine it is up to? (but % would probably be better)

And preferably a pop-up box one?.. not one on the status bar because i have stuff updated on the status bar already...

View 14 Replies View Related

Macro Stop The Delete Process

Oct 12, 2007

I am trying to speed up this macro, ive already tried turning screenupdating and calculation off, but it still takes forever, and I dont understand why.

The code is basically searching for a given string in column E. There are about 9000 lines to my sheet, which shouldnt take more than a 30 seconds to a minute to complete.

Private Sub CommandButton1_Click()
Application.Calculation = xlCalculationManual

For MY_ROWS = Range("E12000").End(xlUp).Row To 1 Step -1
If Range("E" & MY_ROWS).Value UserForm1.ComboBox1.Value Then
Range("A" & MY_ROWS & ":E" & MY_ROWS).Delete (xlUp)
End If

Also anyone know how to add another condition to make the macro stop the delete process at lets say Range("a1:e8")?

View 9 Replies View Related

Select/process Variable Range With Macro

Dec 15, 2006

I download some data from a commercial real estate site about properties and their owners and process it in Excel. Out of 1,000 records, maybe 20 or so will have the data end up in the wrong fields. This is an artifact of the data source the commercial site uses.

Anyway, what I need to do is to get the data back in the right fields. So, I sort the data to pull together at the top of the sheet all the records with data where it's not supposed to be. So far, so good.

Now from one data download to another the number of records which end up in the sort will be different. And, here's the problem.

I try to record a macro mimicking my selection of the range of the data that needs to be moved. Fine, no problem. And, on the same dataset it works like a charm. But as soon as I put a different dataset into the spreadsheet with a different number of records that need to be corrected the macro fails.

Apparently, this is because the macro has been defined with a certain range of cells selected in the first data set and this same range is used for subsequent datasets with different numbers of errant records.

Basically, what I'm trying to record in a macro is the Shift/Control End and Shift/control arrow commands. But they don't record as such.

View 9 Replies View Related

Make Macro Wait For Process To Complete

Feb 4, 2010

I'm using a pre-made spreadsheet from my stock broker (Interactive Brokers) that retrieves and displays real-time quotes, and allows one to retrieve historical stock data, among other things. I've created a little macro within it to try and automate some common tasks I do everyday - basically I want to retrieve a year of daily stock quotes for "stock 1" from IB's servers, then have the macro wait for the retrieval to be done and written to the spreadsheet (takes anywhere between 10 and 30 secs). After that's all done, then I want to do the same for "stock 2". Then when that's done, the macro proceeds to go ahead and do some calcs on stock1 and stock2. The problem is after my stock1 data request, the macro just keep on trucking through to the next commands while stock1's data retrival is still going on.

so things are getting all balled up. How can I get my macro to wait until stock1's data retrieval is all done?

View 14 Replies View Related

Running A Macro Process On Each Sheet In A Workbook

Jan 27, 2008

I am trying to write a macro that will run another macro (already written) through all the sheets/tabs in a workbook one sheet at a time and will stop once it reaches the last sheet/ tab.

Can you assist me in writing this macro.

View 9 Replies View Related

Batch Process 1 Macro In Multiple Files

Sep 12, 2007

I have a collection of about 200 .xls files in a folder, each contain the same macro. I now need to run this macro in all 200 files and save them but to do this manually will take hours. is there anyway to set excel to batch process a macro in multiple files?? Or maybe i can write a new macro to open up each workbook, run, then save and exit??

View 3 Replies View Related

Select Multiple Files And Have Macro Repeat The Process

Sep 7, 2012

Im trying to get my macro to open allow me to select multiple workbooks, then have each workbook open up and copy A2:N2 and down and paste these into my active workbook, under each other, ie on the next available row (basically combining all the workbook data together to create a big list.

The below code works to open a single workbook, copy the data and paste it in at the next available row, but I have to keep running the macro for each workbook whos data I need to import!

How can I modify this code so I can select more than one file? I need something like for each wb the end next wb...until the macro has done the below for all selected workbooks.

Sub openandcopy()

Dim wb As Workbook
Dim ws As Worksheet
Set ws = ActiveSheet
Set wb = Workbooks.Open(Application.GetOpenFilename)

[Code] .....

View 4 Replies View Related

Speeding Up Macro Process (Graph Algorithm Stuff)

Jul 7, 2009

i'm working on floyd algorithm macro for a project,
problem is, the spreadsheet matrix is huge, it's a 1043 x 1043 spreadsheet
And this is taking wayyy too long, i've been waiting for almost 2 days already..
here's the file :
(You start the macro using "run" command in "input" sheet)

So is there any way to speed it up?
Or if anyone got a supercomputer, could you help me process this? :p

I'm thinking about changing the 10^5 value into infinite value, but i can't find a way to display infinite value..

And here's some reference for Floyd's algorithm,

I have tried to split the algorithm to make it process one iteration at a time, but i've been waiting like 2 hours with no result,
and that's for 1 iteration, can't imagine how long it takes for 1043 iterations
You can do this by deleting the.

View 14 Replies View Related

Macro: Process The Cells For Every Worksheet In A Book Rather Than Just The Active One

Apr 2, 2007

I need to get this macro to process the cells for every worksheet in a book rather than just the active one

Public Sub test()
Dim Lr As Long, i As Long, x As Range, _
v1 As String, v2 As String, v3 As String
Set x = ActiveSheet.Cells.Find("*", searchdirection:=xlPrevious)
If x Is Nothing Then Exit Sub
Lr = x.Row
Application.ScreenUpdating = False
For i = Lr To 1 Step -1
v1 = Cells(i, 2)
v2 = Mid(Cells(i, 3), 1, 1)
v3 = Cells(i, 4)
If v1 "OP00" Or v2 "L" Or v3 "CC" Then Cells(i, 1).EntireRow.Delete
Application.ScreenUpdating = True
End Sub

View 9 Replies View Related

VBA Macro To Process Data After Paste With Text To Columns..

Mar 24, 2009

My problem is that data from previous applications are "bleeding" into new applications while running my macro.

I am utilizing the macro below to paste an application and then to convert the application to upper case in addition to some additional formating changes. After performing the macro I save the newly created document to a different folder. With new applications I repeat the above steps.

I tried to incorporate the clearing of the clipboard in my macro using: Edit=>office clipboard=>clear all. The keys strokes during the " record macro" process do not seem to record in the macro.

I am using Excel 2003, SP2

Sub Process_Application()
' Process_Application Macro
' Macro recorded 3/23/2009


View 9 Replies View Related

Macro Will Not Work To Process Files Stored On Network Drive?

Aug 1, 2014

I have a macro that works just find on my local machine. but it need to run from a network drive as several different people may need to access it.

A brief description of the macro.

The macro looks in an directory called unprocessed for any file named "*.csv" if the files are found it opens the file in excel and then copies data from that workbook to my workbook. after this the .csv file is closed (unchanged) and then it is moved to the processed directory.

Sub files()
Dim directory As String, filename As String, sheet As Worksheet, i As Integer, j As Integer
Dim filetype As String
Application.ScreenUpdating = False


View 7 Replies View Related

Macro Executing Step By Step

Jun 5, 2008

I don't know what exactly is happening, but every step of the macro is happening every 1 second. Literally, the BPM is 60. It will execute a step in the macro every second, when in other macro's i've made, it zips right through as if it was on fast forward.

View 9 Replies View Related

Executing Sub Within A Sub

Jul 17, 2008

I have a combobox which is used to select data (stock symbol) from a list. Following selection of the stock symbol, a VBA routine is invoked from a radio button which accesses the internet to get data for the selected stock. Currently, this is a two step operation. I tried to cut out the need to click on the download button by calling the GetData sub inside the combobox sub as shown in the code below.

View 14 Replies View Related

Not Executing Second Sub

Apr 30, 2013

Why the second sub is not executing?

Private Sub RolloverYear_Click()

Application.ScreenUpdating = False
Dim s As Variant
Dim resp As Long
resp = MsgBox(prompt:="You are about to clear ALL current year payroll information. Are you sure you want to continue?", Buttons:=vbYesNo)
If resp = vbNo Then

[Code] .......

View 9 Replies View Related

Executing Multiple SQL Statements At Once

Oct 4, 2008

if it is possible to use a batch query to execute more than one SQL statement at once from Excel. Something like this:

View 9 Replies View Related

Executing Two Different Commands With Same Button

Dec 17, 2008

I just want to have a column sorted by Z-A order and then A-Z by the same button, I recorded 2 macros on 2 buttons and tried to join codes with "if, elseif".

View 6 Replies View Related

Executing The Same Subroutine For All Checkboxes

Mar 6, 2008

I have 60 checkboxes on my spreadsheet and basically, when any of the checkboxes are clicked on, the same action should be performed.

The only way I know how to do this is if I were to write the same code 60 times... one for each checkbox_click() method.

View 9 Replies View Related

Executing Code In Different Sheets

Apr 2, 2008

I have a workbook with several worksheets. The numbers entered by the user in the cells C7:C9 of sheet "A" will be used to calculate some values in sheet "A" as well as in sheet "B".

Calculations on both sheets involve macros: macro "scale" on sheet "A" and macro "sort" on sheet "B".

I used this code to execute macro "scale" on sheet "A" triggered by the Worksheet_Change function:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("C7:C9")) Is Nothing Then
Exit Sub
End If
End Sub

now, I want to execute macro "sort" on sheet "B" practically at the same time (with the same worksheet_change condition for sheet "A")

All off my attempts resulted in running "sort" in sheet "A" which is not what I want. I couldn't get it to execute the macro in the other worksheet.
Already tried "With Worksheet("B") and "Dim ws As Worksheet, Set ws = Worksheets("B") - no luck...

View 9 Replies View Related

Error Trap Always Executing

May 22, 2009

The error trap does work when there is an error, but it also runs at the end of the code when there isn't an error... I'm not sure what I've done wrong. When I test with data that is correct, it still runs through the code when it gets to the bottom. Have I put the error trap in the wrong place?

I have put in bold the section of the code with the error trap.

Option Explicit

Const TargetDB = "Group Life Pricing Tables v0.1.accdb"

Sub HMUK_Val()
Application.ScreenUpdating = False

'define variables to be used in the process
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sSQL As String
Dim SchemeInfo As Integer
Dim CurrentAge As Integer
Dim CurrentRow As Integer
Dim CurrentSex As String
Dim Message
'check that Scheme data has already been entered
For SchemeInfo = 1 To 13
If Sheets("Working Info").Cells(SchemeInfo, 2).Value = "" Then
Message = MsgBox("Please ensure all Scheme information has been entered before processing member info", vbOKOnly, "Warning")
Exit Sub
End If
Next SchemeInfo

View 9 Replies View Related

Add Spreadsheet While Executing Query

Dec 10, 2009

(Excel 2003)

I've a macro (I recorded) that executes a query (Oracle) and returns rows that exceeds the max (65539). How do I go about adding another sheet therefore allowing the query to continue?

I know how to do it via opening a text file and importing, but do not know how to do it with the use of a query.

View 9 Replies View Related

Executing Checkboxclick With Code

Mar 21, 2007

I have a series of checkboxes that I would like to select/deselect on mass, without having to manually click them individually...

Each of the CheckBox_Click routines change the visibility properties of images.

Changing the CheckBox value with code (true/false)

CheckBox.value = True

will alter the checkbox, but not execute its code...

Run "CheckBox_Click"

did not do it either.

How do I simulate the clicking of the CheckBox, with code?

View 3 Replies View Related

Worksheet_change Executing Selectively

Apr 4, 2007

I have a Worksheet_Change method in my sheet module which has suddenly begun working selectively. I have several target ranges in my worksheet that I need to watch for changes. One range of cells is all pull-down menus (a list of electrical loads through excel's validation), another two cells are looking at the name and location of an electrical panel.

When the user selects an electrical load, the worksheet_change method updates a legend of definitions. When the user enters a new name or location, the method updates similar fields elsewhere on the same sheet.

The problem is, excel has stopped executing worksheet_change when a load is chosen or location entered. It only executes when the aforementioned load and location are deleted. However, the name field triggers worksheet_change just fine!

I have attempted to put breakpoints on the worksheet_change method; Excel does not even execute the method as described above. I've tried breaking on the toggling of Application.EnableEvents (which occurs early on in the execution of worksheet_change). I currently have a global watch on Application.EnableEvents and I'm quite sure it's TRUE before I attempt to trigger a worksheet_change event.

My next step is to create a brand new workbook and copy my spreadsheet and code into it. I'll post the results of that experiment.

View 9 Replies View Related

Correct Syntax For Executing Vba

Apr 26, 2007

What I want to do is make a simple text link inside of a cell so when it's clicked on it executes the CommandButton(x) VBA script.

I'm guessing =HYPERLINK is going to be the best method.

Like so:


View 9 Replies View Related

Copyrights 2005-15, All rights reserved