Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










SUM Macro


I'm trying to create a SUM Macro that will sum the total in a number of columns.

The number of rows and columns are not fixed and change from week to week.

Range("J65536:AE65536").End(xlUp).Offset(2, 0).FormulaR1C1 = "=SUM(R5C:R[-2]C)"
This brings back the total for the first column (j) but does not return any results for anything else.

I entered the AE65536 to see if I made the column an absolute, whether results would be returned but no luck.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Conflict Between Auto Save&close Macro And Show/hide Sheets Macro
I am trying to make a save&close workbook macro.

I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).

The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.

If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.

The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)

The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.

View Replies!   View Related
Macro Coding: Add A Code To The Sort And Paste Macro That Will Open The Second Spread Sheet
I am making a spreadsheet that sorts and pastes, but I need to know if I can add a code to the Sort and Paste Macro that will open the second spread sheet needed without just already having it open and using the

Windows("estimate sheet one.xls").Activate

View Replies!   View Related
Userform To Control Macro Features. Macro = Search For File Type
I have a macro that I found somewhere on the net to look within a folder and list all the files of a certain file extension.

The macro to do this is in the attached example and is called 'Get_File_Names_Within_Dir_ext'.

I have created a basic userform outline, 'UF1' for the user to define:
Select File Extension
Select Folder to Search
Destination Sheet

I just don't have any idea how to sync the two.

If you type 'exe' into 'TB1_File_Extension' of 'UF1' the macro should search for '*.exe' files within the specified folder.

The search folder 'RefEdit1' box should open a windows explorer box (or some such) so that the user can select the directory in which to search for the previously specified file extension.

'TB2_Destination_Sheet' is a text box for the user to type the sheet within the workbook in which to list the files found within the specified directory.

'CB1_Find_Files' should activate the macro to find any files for the specified criteria.

There is also a Button 'Find File Types' in Sheet1 of the file which should activate the userform 'UF1'.

View Replies!   View Related
Macro Paste- Macro To Get The Values From Cells D29 And H24 In The Resource Calculator Sheet
I need a macro to get the values from cells D29 and H24 in the Resource Calculator sheet and populate it into cells N8 and O8 in the Input form.

Users will then be able to change the information in the calculator and click the macro again to populate N9 and O9 and so on.

Is there a way to do this?

I've attached the file for you to see.

View Replies!   View Related
Worksheet Change Macro Takes Too Much Time When Run With Update List Macro
I have a worksheet in which I have a worksheet_change macro. This worksheet_change macro makes sure that a few cells will keep their colors, even if the user copies and pastes a new value to that cell. This worksheet_change macro runs each time there is a change on the worksheet. Now my problem is that on the same sheet I have an update list macro which updates around 20.000 rows and two columns (which is alltogether around 40.000 values) and it takes a while to run. So.. it takes a loooooooooot of time (too much) when these two macros both run.

My question is that can I somehow disable the worksheet_change macro while the update list macro runs. I mean something like when I start the update list macro to disable worksheet_change macro and when the update list macro finishes, then reenable worksheet_change macro?

View Replies!   View Related
Macros Not Visible: End User To Be Able To Run The Report Multiple Times By Choosing The Name Of The Macro From The Macro Menu
I have written two VBA programs around the same time. Both run on open and pull external data and create graphs. My problem is that I want the end user to be able to run the report multiple times by choosing the name of the macro from the Excel macro menu (i.e. Tools>Macro>Macros) but only one of the workbook macros shows up on the menu. why the other macro is not visible on this menu???

View Replies!   View Related
If Statement In Macro: Macro To Change A Range Of Cells Colours Based On A Single Cell?
1st - Need a macro to change a range of cells colours based on a single cell having a value greater than 0.001. ie. cells A1 - G1 need to change to grey based on cell F1 having a value greater than 0.001 entered in it?

2nd - Also a macro for deleting the text contents of cell C1 based on cell F1 having a value greater than 0.001. Therefor if cell F1 has a number greater than 0.001 it changes the colour of celss A1 - G1 and also deletes the text in cell C1?

View Replies!   View Related
Macro Doesn't Work On Button, But Fine From Macro Menu!
I've recorded a macro that copies an entire tab into a new spreadsheet then goes on the copy and paste information from one tab to another.

When I run the macro from the Tools>Macros menu it works perfectly.
But when I copy the code and add it to that of a button it fails and posts the following error: Run-time error '1001': Select method of Range class failed.

The first attachement shows the code for the macro as it is alone, and the second shows how I simply copied and pasted it into the 'view code' window of the button.

Needless to say I'm a beginner at macros and only every record them, I can usually make stuff work that way but this has me stumped!

View Replies!   View Related
Macro To Export And Import A Module And Assigning Different Macro Automatically
Macro to export and import a module and assigning different macro automatically ...

View Replies!   View Related
Macro Stops Running The Macro Completely And Hangs In The Middle
Here in our department we made a pretty elaborate macro that takes a report and sorts them out to 17 different sheets in a one workbook. This Macro pulls a file from a specific location on our server and then opens the CSV sorts it out color codes all the important information and saves it back onto the server under you specific initials.

They are four PC's along with our Managers laptop that run this Macro daily.

About 3 weeks ago my Managers laptop stops running the Macro completely and hangs in the middle of the whole thing. Eventually crashing Excel.

We try to remove the modules and re-import them back into the personal macro workbork but this does not work. The Macro's did not change and still fully function on the other four desktops to this day.

I uninstall Office on my Managers laptop and reinstall. Import the Modules again and still hangs up in very same spot it did 3 weeks ago.

I've tried to lower the macro security to the lowest level also and I've still had no luck with this laptop. I don't understand. The Macro's function perfectly on other PC's but will not function on this laptop.

View Replies!   View Related
Running A Macro When A Value Is Reached In A Specific Cell Used To Call Up Macro
I have a Sheet sheet1 and I want to run a macro when the cell D2 in Sheet1 is equal to 10,7,5,and 3. I only want this macro to run when those values are reached the macro then puts the data onto a sheet called wps. The macro is run as a module and is a sub macro.


View Replies!   View Related
Delay Macro (Put In A Statement Within A Macro That Populates Cells With The Values)
Put in a statement within a Macro that populates cells with the values that I want it to but instead of populating all at the same time, is it possible for the values to be delayed.

I have designed a mock spreadsheet (attached) it has two columns 'Before' and 'After'

After = Before values (in this mock)

When you press the button, the values are populated straight into the 'After column' can we add the delay between the values? So that the values dont come up straight away.

View Replies!   View Related
Macro Recording 2007: Record A Macro To Insert An Autoshape
I recently installed Excel 2007, and have shared others' joy in searching for things on the ribbon. I tried to record a macro to insert an autoshape. Excel creates the macro, but drawing the autoshape is not recorded. I'm sure I'm missing something obvious ...

View Replies!   View Related
Macro Disable/enable Still Appears After Macro Deleted From Workbook
I have a question concerning Macros and the Disable/Enable prompt. My boss has a spreadsheet which has been used for years and he recently wanted to and did remove the macros from the spreadsheet (they were no longer necessary), but the disable/enable prompt still appears when the spreadsheet is opened. I replicated this in a test spreadsheet with a simple insert line macro and received the same results. Is there a way to remove the macros and the disable/enable prompt once they are removed? I know about setting the security to low to not see the prompt, but I would think that once the macros are deleted, the prompt should not appear any more.

View Replies!   View Related
Macro To Delete Vba Code - Macro Security Warning
I have recently grabbed the very helpful "Macro to delete VBA code" from this site, and it's working but with one small problem.

My worksheet runs a lot of code, then deletes all macro code and saves itself.

The problem is the next time i open I still get the macro security warning!

I've checked thoroughly and there is definatley no vba left anywhere.

If I open the document, enable macros, and save it, then open it again, I no longer get the warning.

View Replies!   View Related
Macro - Repeat Macro A Specific Number Of Times
I have created a macro that copies the outcome of a specific calculation (that is driven by random numbers) and pastes it (as values) into a seperate table. It then recalculates the sheet, copies the new outcome and pastes it into the same table at a row beneath the previous one.

However, I would like to be able to determine how many times this loop is repeated (i.e. how many rows of outcomes will be generated) without having to change the macro each time. Is this possible? Maybe by just linking it to a cell where the number of outcomes/repeats is specified.

View Replies!   View Related
Macro SortWorksheets Stopped By Each Worksheet Until The Macro Is Ended
I have in my Workbook_open a macro,called Workbook_SheetActivate
with a line : Msgbox ActiveSheet.Name ....

In a sheet I have a macro SortWorksheets ....

My problem is , when I run the macro SortWorksheets it always stopped
by each worksheet until the macro is ended, this because the macro by opening workbook.

How can I make the macro SortWorksheet in one run?


View Replies!   View Related
Macro That Takes Workbooks Or Files And Runs Them Through Another Macro
i need to make a macro that takes workbooks or files and
runs them through another macro. i already have the 2nd macro
done and it is working perfectly i just need to know how to make the one
that finds the other files and runs them all through the macro i already made. My boss said that he will have about 150-200 files to run through this macro.

View Replies!   View Related
Macro Progress Bar For Macro Taking Long Time
I have a macro that is taking a long time, so long that I think something is wrong but I dont really have a great way to check. I want to put up a little window showing the progress either by saying "i'm on row XX" or having a progress bar.

Looks like msgbox requires action before the code continues so it is not a good thing to use if you want to get progress. My formula looks for bad values in a data file and fixed them. It takes forever. I would like to have a box there that simply tells me what row it is working on.

Clearly I dont want to hit the OK button for it to continue processing. I want it to update the message box on its own and then take away the message box when it is finished processing.

View Replies!   View Related
Creating A Macro, That Creates A New Button, Which Itself Runs A Macro..
I have a long complicated macro that processes a ton of data and gives the output in a new sheet. I then want to create new buttons (or some sort of user clickable triggers) on this new sheet that runs another macro.

I have got the actual adding of buttons in the sheet working, but I can't find a way to automatically assign macros to newly generated buttons during runtime. Is this even possible (or are there any clever tricks I could use to get around this?)

View Replies!   View Related
Open File Macro: Same Directory As Macro Workbook
I created folder on my desktop to hold various documents. I have master excel document which contains forms and command buttons that open documents from same folder. Is there a way to change code instead of having full file path to specify current directory. I'm worried that if I move this folder to another location then my open file button will not work since location is changed.

Also when I open with command button word document, I have a button on word document to close. I can close document but word application is still active. Can I close word application with the document at the sam time. This is the code I'm using: "ActiveWindow.Close"

View Replies!   View Related
Restrict Macro Keyboard Shortcuts To The Workbook Containing The Macro
I've created Keyboard Shortcuts to run Macros in "Workbook A". Problem is, if I have "Workbook B" open in the same Excel instance/process as "Workbook A" the Keyboard Shortcuts will run the Macros in "Workbook A".

Is there a way to restrict the Keyboard Shortcuts to only run the Macro if Workbook A is active?

Or is there a way to code the Macro to run its procedures only if Workbook A is active?

View Replies!   View Related
Pause Macro - Select From A Drop Down - Restart Macro
I am trying to pause a macro on a protected sheet, select 2 adjacent cells (initially protected), utilize an existing drop down box to select a name from the list, copy the name from the list into the range of cells, then re-start the macro.
I had no problem when there was just one name (see John Smith below). I tried to use the InputBox command but needed to actually type in the name.

ActiveSheet.Unprotect
Range("C27:D27").Select
'ActiveCell.FormulaR1C1 = "John Smith"
' Application.CutCopyMode = False
' ActiveSheet.Paste
'Range("c27:d27") = InputBox("Enter value")

View Replies!   View Related
Macro Fixes Macro: Copies Tabs From The Spreadsheet
I am working on a macro that copies tabs from the spreadsheet the macro (button) is located in, creates a new book for them, and then pastes values so that no formulas still exist. The problem I am having is with named ranges. The named ranges do not reference the new sheet, but rather the old spreadsheet. This is fine because the new file is solely for presentation, however, one macro (which selects print ranges based on user selected checkboxes) tries to reference the old spreadsheet once it runs. I would like the macro that I currently have working, to also make this macro in the NEW spreadsheet so that it can still highlight the correct print area when used. The macro does not reference the name of the workbook at any point, it is purely "Range("ai10") type references so I don't understand why it is trying to open the old sheet back up.

View Replies!   View Related
Macro Delete Page (delete A Sheet In A Macro But When I Run The Macro)
I want to delete a sheet in a macro but when I run the macro, I always get a message warning and I have to answer the msg box to delete the page. Below is the macro command I am using.

Sheets("Tel").Select
ActiveWindow.SelectedSheets.Delete

View Replies!   View Related
Macro To Select VbYesNo When Calling Another Macro
I have a workbook with a 2 macros "PopulateSheetlist" and "SaveEditedversion"

Normally they are each button operated, and prompt the user with vbYesNo style options. I wanted to write a Macro, say "Macroautomatewkbk that would automatically answer the prompts.


Sub Macroautomatewkbk ()

Call PopulateSheetlist

' Always answer YES or OK to any prompts this macro may offer

Call SaveEditedversion

' Answer No for the 1st prompt and YES for the 2nd prompt

End Sub

MsgBox "Both macros "PopulateSheetlist" and "SaveEditedversion"

' Some error handler here, don't know how this should work exactly.
Could any one please explain how to fill in code in the commented sections in the above sample code?

Edit: I tried recording a macro to do the above, but it only showed the zooming and scrolling that occurred, none of the button prompts being answered.


View Replies!   View Related
Pause Macro, Allow Format, Resume Macro
how to interrupt VBA code, allow user modifications to a chart, and then continue the code where it left off with a click of a button. I currently have the code in place where a UserForm pops up at a specific point and tells the user to make adjustments and press OK when done. It works fine but the UserForm displays and the macro resumes when OK is clicked, but I can't figure out how to activate the chart or the spreadsheet to allow editing while that UserForm is on the screen. If only I can get excel to allow me to sleect the spreadsheet and make changes while the UserForm is on the screen, I would be set. Can anyone please help me with any options to allow editing while the UserForm is on the screen so I can edit and then click the OK when done?

I posted something similar to this over two years ago. The way I got around it at the time was to break the code into two separate subs. This time around, the code is making 17 graphs and I want to ask for editing for each one before continuing. That would be a lot of individual sections of code.

View Replies!   View Related
Macro Adustment - Macro That Accesses A Certain File
I have a macro that accesses a certain file that I use every day.
the file name for example is abc207.drf 207 signifies the date...the file name changes every day example tomorrow file will read abc208.drf

Every day I have to go in and change the macro because the file name changes...but the.drf is always there....Any way to write the macro to obtain the file even though the date keeps changing on the new file?

View Replies!   View Related
Macro To Open And Execute A Macro From Workbook
Is it possible to use a macro to:

1. Open Excel from the Desktop

2. Open a workbook

3. Execute a macro from this workbook

If so, how can it be done, specially point 1?

View Replies!   View Related
How Do I Apply A Macro To All Open Spreadsheets Except The One The Macro Is In
I am trying to set up a macro that hides certain columns of data in an automated spreadsheet that I don't need. How do I make the macro that hides the columns apply to all spreadsheets that are open except for the one I am in?

View Replies!   View Related
Macro To Add CommandButton & Assign Macro Code
I written VBA code to create a new sheet and embed a command button on it. I want to assign a macro to this button but when I click on the button the assign macro button is greyed out. I have plenty of macros saved and the VBA code has finished running so why should this be?

View Replies!   View Related
Suppress Message When Macro Called By Another Macro
I have a Macro (AutomateReport) that, among other things, calls and runs a macro (ReportStep1) in another workbook.

ReportStep1 includes three prompts asking the user to press "OK" to confirm that the data included is correct. When I call ReportStep1 as part of my larger macro I would like to suppress these prompts or automatically respond "OK" so that I don't have to be bothered with them.

SendKeys works sometimes, but I am hoping for a more reliable method.

View Replies!   View Related
Macro And IF Function Is True Then Macro 1 (hide) Is Used
I have 2 macros one hides a selection of rows and the other unhides them. What i want to be able to do is when an IF function is true then macro 1 (hide) is used. If the function is false then macro 2 (unhide) works. There is a code in VBA

View Replies!   View Related
Temp Macro To Rename Macro
Is it possible for a macro to rename a macro? I have a Temp macro that I want to rename to Auto_Open (or maybe auto_close).


View Replies!   View Related
Run Macro When Range Value Not Blank, If Not Run Other Macro First
I wanted to modify the below macro but don't know how. Could someone help me please? Please see the bold text below.

Dim shortmonth As String
Dim todaysdate As String
Dim enterdate As String
'GET DATE AND CREATE VARIABLES
enterdate = InputBox("Please enter the date in the following format: MMDD")
shortmonth = Left(enterdate, 2)
todaysdate = Mid(enterdate, 3, 2)

Workbooks.Open Filename:= _
"S:CMU Trade VerificationTrade Discrepancy Report " & [shortmonth] & " " & [todaysdate] & ".xls"
Sheets("Daily Archive").Select

***Do this step when when range A2:N2 is not blank, otherwise, run the "Daily Data" macro first and then do the below steps.

View Replies!   View Related
Macro To Call Another Macro AFTER Ending
I want to execute a macro (MyMacro()) AFTER a different macro (Different_Macro()) ends. Different_Macro() is always run from a menu button while editing a codepane in the VBE. At the end of Different_Macro I have this code:

View Replies!   View Related
Macro To Reassign A Macro To A Button
Upon opening the worksheet, I'd like to have a startup macro reassign a macro to a button that is already created. We have an issue with two buttons in a custom toolbar that for some reason default to an old (nonexistent) version of the file when looking for the assigned macro. If I could get an opening macro to automatically reassign those buttons to the proper macros inside the current file, then we wouldn't have to have all possible users login and reassign and resave, which seems to be the only solution at the moment.

Reassigning a macro to a button doesn't show up when doing "Record Macro", and if creating a new button and assigning correct macro while "Record Macro" is occurring, VisualBasic only shows the creation of the button and not the assignment of the macro.

View Replies!   View Related
Macro - Row Search Macro In Worksheet
to write a Macro which will be able to read a numeric value in a cell and find the equivalent of the row number in the sheet.

For instance, if I type "12" in cell A1, the marco will be able read cell A1, goto and highlight Row 12 of the same work sheet.

View Replies!   View Related
Macro To Enable Macro Security
It's possible to add in my code a line that sets the macro security to low?

I have to put a excel file that contains macros in over 50 computers, and set the macro security to low.
It even exists something like this?


View Replies!   View Related
Macro To Reference Result Of Another Macro
I've looked online and through the forum and can't find a solution.

I'm using the following code...

View Replies!   View Related
Calling A Macro And Formula Macro..
I created two macros. One for refreshing the data I pull from a database, and another one adding some formulas I need. On the formulas macro, I don't know how to have it automatically fill until the last row of data to the left of the column with the formula. (Just like if I double clicked it and it filled down automatically). I also tried to combine these two macros, but I get errors and they don't execute.

View Replies!   View Related
Will Running A Macro Within Another Macro Use Variables?
Will running a Macro from within another macro allow the second macro use the first one's variables?

Example:

Macro A Starts

Variable x set

Macro B starts

Macro B uses x from Macro A

Macro B ends

Macro A ends

View Replies!   View Related
Call Macro - Use Macro Name From Cell?
My question: Is it possible to write a code that calls the macro with the name specified in a particular cell? Right now I have some AutoFilter going on making a list that has numbers 1, 2, 3... I also have alot of macros named Search1, Search2, Search3... So I can make a cell, say A28, that combines "Search" with whatever number is in Column C, row 29:

View Replies!   View Related
Macro Running In OS X, Able To Run This Macro On 11 Files At Once
I have this macro that a friend of mine wrote on a windows machine, and I can't quite figure out the code changes needed for OS X. For someone who knows how to do this, I'm sure it would only take a second, but I've been banging my head against the wall on this thing for too long.

Option Base 1
Sub Data_Compiler()
Dim FileSelected
Dim FileName
Dim CompiledDataArray(1 To 30, 1 To 53) ' Array to store our statistics
Dim Counter1 As Integer
Counter1 = 1
FileSelected = Application. GetOpenFilename("Your Files,*.xls", , "Select Files", , True)
If StrComp(TypeName(FileSelected), "boolean", vbTextCompare) = 0 Then Exit Sub
For Each FileName In FileSelected
Application.DisplayAlerts = False................

I can get a "open files" dialogue box to pop up, but I can't figure out how to make sure that multiselect is enabled. I need to be able to run this macro on 11 files at once.

View Replies!   View Related
Check Boxes Macro: Macro That Will Check If The Boxes Are Checked And If Not Ignore Them And If So Run A Certain Macro
I have numerous hyperlinks in cells on a worksheet, next to these I have checkboxes. I am looking for a macro that will check if the boxes are checked and if not ignore them and if so run a certain macro.

View Replies!   View Related
Excel Macro Vs. Non-Macro
is any kind of rule of thumb about from what point is it useful to use macros, and until when one should not use a macro (e.g. only nesting Excel commands/formulas)

View Replies!   View Related
Have Macro Determine Which Macro To Run
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?

View Replies!   View Related
Delete Macro By Macro
I 've a macro (1) written in such a way that it will open the save as dialog box on start up.

it is as under:

Private Sub Workbook_Open()
Dim fileSaveName As Variant, myPath, myFName$

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.xls), *.xls", _
InitialFileName:=myFName)

If fileSaveName False Then
ActiveWorkbook.SaveAs Filename:=fileSaveName
End If
End Sub
Now after saving this book. i work in it. After all work compted the book will be saved in safe place. I want to use a macro to delete that macro (1). So when I re-open it after few days, it should not have macro (1).

View Replies!   View Related
Disabling A Macro From Within Another Macro
Is there a way of disabling one macro from within another macro? The worksheet in question contains a lot of macros. If it is possible, how do I name/identify/call up the macro that is being disabled, whilst the other one is running?

View Replies!   View Related
Repeating A Macro In A Macro
I have a spreadheet with a list of numbers in column A. (6000 or so)

I am looking for a macro that will pick up the first number in A1 (is a 6 digit number)

transfer this number into cell A1 in another worksheet, after this part of the macro i will add my macro that will save & print the second worksheet.

However i can't make the macro repeat for Cell A2, Cell A3....... Cell A6000. All of these will be going into cell A1 in the second worksheet.

View Replies!   View Related
Macro Updated By Macro
How should I update a macro by a macro ? meaning to say that codes are replaced by new codes in module.

Suppose I have workbook named Ayaz.xls containing module named "TDP" which is protectd by VBA Password(Lock Project). I want codes to be updated/replaced by New codes.

For example I have following codes


Private Sub CopySheet1()

With Sheet1.UsedRange
Sheet2.Range("A1").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub
want change to

Private Sub CopySheet1()

With Sheet1.UsedRange
Sheet3.Range("C2").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With
End Sub

View Replies!   View Related
Copyright 2005-08 www.BigResource.com, All rights reserved