Run Another Sub From Within Active Sub
Secondary Sub is located in the same project named, ClearItemtotals
I tried placing it as the last line prior to End Sub both as Call ClearItemTotals() and/or ClearItemTotals.
Neither work.
View Complete Thread with Replies
Sponsored Links:
Related Forum Messages:
How To Run Macro From Active Sheet
I have written the below macro to copy data from a sheet called "Fixit Example 1" to a sheet called "Fixit Summary Example". It works fine, but I'd like to be able to run it from any "Fixit Example XX" worksheet. As written, the data is always copied from "Fixit Example 1" regardless of what worksheet I have open. I tried replacing Windows("Fixit Example 1.xls").Activate with Windows(Active.Worksheet).Activate but got an error "Run time error 424. Object required." Sub MoveData() ' ' MoveData Macro ' Macro recorded 18/03/2009
View Replies!
View Related
Return To Active Cell After Macro Run
I am using Excel to tabulate scores for my employees. We work in a very busy and open office, so there is a need to be able to obfuscate the scores, but also help keep from losing my place while punching the scores. I would like it to be able to return to the last cell that I was punching a score in...I used "ActiveCell.SpecialCells(xlLastCell).Select". I have also used " x= cells(Rows.count,2).end(xlUP).row" followed by "cells(x+1,2).select", but both with no luck... Sub Hide_Scores() Range("B15:EU35").Select ActiveSheet. Unprotect With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0
View Replies!
View Related
Find Method To Search For The Active And Non Active Values
I have a range of amounts in Sheet 1 from F7:Q13 and im using the find method to search for the active and non active values in the cell. Which means that if there's a value in the cell it will transfer the value in Sheet 2, if nothing is found in the cell the cells in Sheet 2 will return as nothing or null. I think the problem lies on the FindWhat variable. Im getting a compiled error which im not sure what is it. I've attached the spreadsheet so you get a better idea of the problem that i encountered.
View Replies!
View Related
Run-time Error '1004' :: Run On Other Computer
I have a piece of VB code that works perfectly on my pc. The spreadsheet has been emailed to a partner (different company) and the code no longer works and they get the run-time error. Private Sub cmdPrintandsave_Click() Dim Company As String Dim irow As Long Dim MyDate MyDate = Date Dim wsD As Worksheet Dim wsD1 As Worksheet Set wsD = Worksheets("AppBDataSheet") Set wsD1 = Worksheets("AppBPrintable") Company = Application.OrganizationName Application.ScreenUpdating = False................
View Replies!
View Related
"Run" Making Macros Run Twice And Stop
I used the "Call" command instead of "Run." Everything finally worked perfect!! Being new to VBA I do not know the reason behind the "Run" command being different and causing my problems. BUT "call" sure is the answer to my recent irritations. After I got the darn things to finally work, I decided to do the responsible thing and use Option Explicit, which helped me clean up my code tremendously!!
View Replies!
View Related
Run Part Of Macro, Pause, Run 2nd Part
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop. Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...
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
Active Area
I have the following macro that works fine. But, I would like it to stop when it gets to the bottom of the active area. Sub Insert_for_SQs() Do Selection.EntireRow.Insert ActiveCell.Offset(6, 0).EntireRow.Activate Selection.EntireRow.Insert ActiveCell.Offset(12, 0).EntireRow.Activate Loop End Sub
View Replies!
View Related
Active Invoices
In column A I have account numbers. I press a button and the selected account number is transfered to another sheet (active invoices) and then is used as a criteria in a msquery. heres the code ....
View Replies!
View Related
Getting The Name Of The Active Button
how can i get the name of the button thats been pressed? i know it will probably involve Application.Caller but i just have no idea how to get it to work Dim BtNm As String BtNm = Application.Caller MsgBox BtNm this doesnt work but that would prob be obvios to you guys.
View Replies!
View Related
Active Window
I've built a macro to run a loop that opens excel files, prints certain sheets and closes them. I have the macro working - to some degree. here is my code (this is excel): Sub RMPProducer() OldPath = "S:RMBS_Performance_Analytics Analysis1 Staging Folder For Monthly Model Templates2007200704VVDeals" Dim t As Workbook Dim s As String Dim a As Window With Application.FileSearch .NewSearch .LookIn = OldPath .SearchSubFolders = False .Filename = "*.xls" .MatchTextExactly = True If .Execute() > 0 Then For i = 1 To .FoundFiles.Count s = .FoundFiles(i) I have 2 problems. First problem is that I am trying to add a function to make the window close once its finished printing. This actually works - but it limits my loop iterations from i = 1 to 2 (so it only runs 2 times and I'm not exactly sure why). If I don't include the statement - this can run for up to 7 - 8 times (these are very large files) then excel runs out of memory. Unfortunately, i can't set the loop to run from i = anything other than one. So What I would prefer is to be able to make the "ActiveWindow.close" command work - because this way I can run the entire loop. But I'm not sure how to. I tried window referencing, I tried adding a new active Window so when the file opens, its opening in the active window already, but nothing works. Preferably, I need to be able to reference both windows, as my next macro will involve referencing 2 windows (one static - always open) and the other the loop above.
View Replies!
View Related
Active X Controls
My excel macro's run fine under Windows 2000 Pro, and Office 2000. The company got a new computer with Windows XP. I now have macros that run OK however I keep getting a pop up message stating. Warning..An active X component is being loaded. I must press OK and the program will run fine. How can I get rid of the Pop up active X message.
View Replies!
View Related
Highlight The Active Row
if you click on a row, it highlights every cell in it, I want to do the exact same thing but then also when you click on a cell only. Preferable (probably impossible though) without the use of VBA, because in my company I think some are "carefull" with enabling macro
View Replies!
View Related
Do Not Update Non-active Sheets
I have a big spreadsheet with a number of sheets, some with dynamic lists which take up a lot of processing power, but which I only use rarely. Consequently the whole spreadsheet is running very slowly. Is there a way to set certain sheets to not automatically update, but retain automatic updating on others? I know you can set Calculation to Manual in the Options, but I need to retain automatic calculation on some sheets.
View Replies!
View Related
Use Active Row In A Formula
I am wanting to use the row number of the current active cell in another formula on a spreadsheet. I have only been able to find info in putting the active row number in a dialog box. Is there a method of returning the active row number and using in a number of formulas.
View Replies!
View Related
BORDER Around Active Cell
I have a spreadsheet with DDE formulas in the cells. When the contents of any cell changes I want to DRAW A BORDER (NOT CHANGE THE BACKGROUND COLOR !!) AROUND THE CELL .. When another cell's value is updated I want to REMOVE the border which was around the"OLD" cell and now place a border around the most recently changed cell, etc. Only the newest active cell should have a border around it !!!
View Replies!
View Related
Multiple Active X Checkboxes
I have a workbook that has a sheet with a lot of columns. I also have a sheet before it which I want to put checkboxes on which will toggle columns on the second sheet hidden/unhidden. I'm using an active X control checkbox form, but I can't figure out how to add multiple checkboxes. Is it possible? Or do I have to just create multiple individual checkboxes? Here is the code that I'm using in the active X control: If CheckBox1.Value = False Then Sheets("Best").Columns("A:A").Hidden = False Else Sheets("Best").Columns("A:A").Hidden = True End If
View Replies!
View Related
Highlight Active Row
I spend a lot of time in spread sheets working with part numbers and sales figures. With a part # in column B and sales per month in adjacent columns, stretching back years. Is there any way to highlight an entire row automatically? ie. I use Ctrl F to search for a number and it goes to B24. I now want the entire row 24 highlighted as the active row. When I move to another cell I want the entire row highlighted automatically. Can it be done.
View Replies!
View Related
Highlighting Active Field
Using excel (i think its 2000, work PC they are behind the times) and i was wondering if there is a way to automatically highlight the active field as i move around the cells. It would be easier to see where my cursor is.
View Replies!
View Related
Only Print Active Sheets
Is there any way to only print active sheets i mean, i have 4 sheets in my workbook, and if i only use one sheet i dont want to print all 4 sheets i would like to print sheet 1 if cell D19 has data in it i would like to print sheet 2 if cell D61 has data in it i would like to print sheet 3 if cell D103 has data in it i would like to print sheet 4 if cell D145 has data in it
View Replies!
View Related
How Can I Tell Which DRAWING Textbox Is Active
I have a sheet with 30 textboxes made with the drawing toolbar. The user enters data, then clicks on a button which formats the page to adjust row size to match amount of info. I am having trouble finding the code to store the currently selected textbox in a variable so that I can place the focus back to the same textbox when the macro ends. Can anyone help me with the code to get the active textbox?
View Replies!
View Related
Find Active Printer
I have excel printing some sheets to pdf and it works fine on my PC. WHen someone else tries to print it wont work because they arent on "Adobe PDF on Ne02:"...they maybe on Ne03 or Ne08 or ne05...you get my point. I saw code on here before as to how to find the proper "port" but after a bit of searching, i cant find it again. Application.ActivePrinter = "Adobe PDF on Ne02:" ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _ "Adobe PDF on Ne02:"
View Replies!
View Related
Active Time In A Cell
is there any way to have a running clock or time in a cell like when you open up a sheet you see it click-by second by second I would like this, so when i print the sheet it shows exactly what time it was when printed
View Replies!
View Related
Protect Non Active Sheets
I have a workbook that has a sheet for every day of the month (1 to 31). When it opens it automatically activates the sheet for the day corresponding today. What I'm missing is how to enable users to only update that sheet. Example today is the 1st, sheet 1 should be unprotected but all others protected and off course tomorrow it should be sheet 2.
View Replies!
View Related
Active Cell And Offset
ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count).Formula = "=VAR(" & ActiveCell.Offset(0, n_high_count -1) & "," & ActiveCell.Offset(0, -1) & ")" *n_high_count is the negative of high_count I want "ThisWorkbook.Sheets(2).Cells(nrow + 1 + j, 2 + high_count)" to have the formula "=VAR(XX:XX)" where the range is the current row second column to the current row current column -1.
View Replies!
View Related
Copy Active Cell
I have two worksheets with the same column. Would like to edit ex. sheet1 A1 and that sheet2 A1 updates automatically by copying just the value, not the reference.
View Replies!
View Related
Calculate Non Active WorkSheets
I have about 5 worksheets, and 2 of the work sheets have about 20k rows by 10 columns... everytime i change one of the other 3 worksheets a calculation takes places.. is there a way to stop these 2 worksheets from being calculated if they havent been touched? it is taking about 30 seconds to do the calculation even though i have only changed 1 row on another worksheet.
View Replies!
View Related
Check Only Active Sheet
I have this macro that looks in all sheets in column A, except the invoice sheet. How do I alter it to only check the active sheet? Dim ws As Worksheet For Each ws In ThisWorkbook.Sheets If ws.Name ("Invoice") Then On Error Resume Next With ws .Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True With .Columns("A").CurrentRegion .Offset(2, 0).Resize(.Rows.Count - 1, 3).SpecialCells(xlCellTypeVisible).Copy _ Sheets("Invoice").Cells(Rows.Count, "A").End(xlUp)(2) End With .Cells.Rows.Hidden = False End With Err.Clear End If Next ws
View Replies!
View Related
Jump To Last Active Sheet
Is there a way to create a macro to jump to the last active sheet? For example, the last sheet I was on was Sheet1 and now I'm on Sheet20 and I want to jump back to Sheet1 without scrolling through the tabs at the bottom of the screen.
View Replies!
View Related
Counter For (active) Sheets
I have a rather big worksheet with lots of sheets in it. I need to see in the frontpage how many sheets there are in total (-2, the frontpage & end sheet don't count) and how many sheets are currently shown (ie. not hidden, as there are vba-filters which sheets to display). I don't really mind how it is shown, it could be one cell with 14/53 meaning 14 sheets shown of a total 53 or in seperate cells.
View Replies!
View Related
Specifying Active Worksheet In VBA
I used the "record macro" and went through my data manipulation steps. But its not transferable to other worksheets because it imbedding the name of the worksheet in the some functions (sort, pivottable,...). How can I change the name of the worksheet to "activeworksheet"?
View Replies!
View Related
Active Buttons Required
The buttons are located on the PFM Tracker sheet with what I need them to do. In addition I'll give you a breakdown of what is required. Currently when the workbook is opened the user is forced to activate the macros. One of these macros disables the Cut, Copy Paste features within Excel. I need the first button 'Enable Copy' to request a password for activation and once entered the Cut, Copy, Paste feature to be enabled. Then when finished the 2nd button 'Disable Copy' when clicked to re-activate the Cut, Copy, Paste Disabled macro. Hope that explains it. I have left the PFM Tracker sheet unprotected for who ever wants to play with it. (Not that protection would stop some)
View Replies!
View Related
Name Of Active Workbook From Add-in
I have an add-in (MyAddin.xla) that is called from my open_workbook event in my workbook (MyBook.xls): Private Sub workbook_open() AddIns("MyAddin").Installed = False AddIns("MyAddin").Installed = True ThisWorkbook.Activate Run "MyAddIn.xla!WbookOpen" End Sub I put this in my WbookOpen procedure: Sub WbookOpen(pWorkbook As Workbook) MsgBox "Wbook Open Active workbook is " & ActiveWorkbook. Name End Sub The message displayed is: Wbook Open Active workbook is MyAddin.xla I thought this would be: Wbook Open Active workbook is MyBook.xls.
View Replies!
View Related
Reference To Active Row
I want to arrange a dataset in blocks of 8 lines and for this I need to refer to the actual row of the active cell. Below code does not work - the "Row" does not change as the active cell moves down through the rows
View Replies!
View Related
Index Of Active Worksheet
I've been a somewhat casual Excel user and now need to do some VBA with a workbook that has several worksheets in it. In particular, the workbook has sheets that have been added "out of order", that is, the leftmost sheet is not the first one added to the book. I need to write a VBA routine that looks "backward" from the current worksheet to "previous" sheets, i.e., sheets to the left of the current worksheet, but not to the right, so I can't refer to them as Sheet1, Sheet2, etc. and they actual display names that are not of that form anyway. I am aware of the "Worksheets" collection and the fact that I can "subscript" that to refer to the sheets in a left-to-right form. I know that I can refer to the current sheet as "ActiveSheet" and I can determine properties like its name with "ActiveSheet.Name", but is it possible to find its index in the Worksheets collection? I'm looking for a function or property such that "ActiveSheet.Index" will allow me to refer to it as "Worksheets(Index)" and particularly to allow me to refer to preceding sheets as, e.g., "Worksheets(Index-1)". If worse comes to worst, I can always cycle through the worksheets and check the name of each worksheet like this: Sub Button1_Click() Dim index As Integer Dim strASName As String strASName = ActiveSheet.Name index = 0 Do index = index + 1 Loop Until Worksheets(index).Name = strASName MsgBox "Index of current worksheet is " & index End Sub And while I'm at it, is it possible in Excel to rename a control like "Button1" to something more sensible and mnemonic, like "btnRecalculate" or whatever?
View Replies!
View Related
Reference Active Cell
I've got 3 columns say A,B,C. Column C (row1) = Column A (row1) * Column B (row1). I'm trying to apply this formula to all the cells in Column C. I've tried typing the following formula in all of column C. =OFFSET(ActiveCell,0,-1) * OFFSET(ActiveCell,0,-2) Obviously the ActiveCell reference doesn't work. This would be simple in VBA but here on excel how would I reference to the cell itself where the formula is contained?
View Replies!
View Related
Active Network Printer
Is there a way to test for an active default network printer? In my module the code sets the paper size and a few other printer settings, if my default network printer is down or offline the code fails. If I could test for active default printer I could remove this error.
View Replies!
View Related
VB - Paste In Last Active Cell
I'm working on this Macro process - and I can't figure two things out. 1) How do I get it to past contents in the second blank cell at the bottom of a list in a specific column? IE - data in B1:B17, paste new list in B19. What I'd REALLY like, is for it to paste in B8, but if B8 used, THEN paste to the second to last blank cell in column B. (if thats even possible) 2) I have VB open an excel workbook, grab data, copy and then past into 1) (above), but then close that workbook. However it always asks "do you wish to save?" and "Do you wish to keep data on clipboard" How can I program it to say "no" for both of these windows prompts? Here's an excerpt of my code:
View Replies!
View Related
Return To The Last Active Sheet
I have a spreadsheet that is nearly 100 worksheets. I am new to VBA and would like to put a button on each tab to return me to the last active sheet. I've tried other codes I've found on here but with no luck. I'm not sure if i'm posting them to the right area within VBA.
View Replies!
View Related
Finding The Active Cell
how to simply find out what the active cell is (This would be the cell the curser is currently in). It is already displayed in the Name Box on the menu in Excel, but I can't find a way to access this data automatically.
View Replies!
View Related
|
TRACKER
