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?
I run most of my macros out of a file called "Personal.XLS" and I have this bit of code at the end of this particular macro which is closing personal.xls instead of the active file it is working on. It is set up on a loop and it should go to the next file in the directory but since it closes the file I run the macro from, the macro stops immediately. It appears that it believes "ThisWorkbook.Close" is referring to the file from which the macro is initiated.
I have a macro on a particular workbook that runs when the workbook closes.
This runs fine when I close the workbook itself. If I happen to have other workbooks open and then I close EXCEL rather than an individual workbook, my macro that runs on close always runs when it is not the active workbook.
To put it another way, say that my 'run on close' macro is workbook 1. I also have workbooks 2 and 3 open as well. If workbook 3 is the active workbook and I choose to close Excel (and thus close all three workbooks), my 'run on close' macro runs straight away and, worse still, will run it on workbook 3 (my active workbook).
How can I stop the macro running until it is the active workbook in the closing chain, so that it only runs on the correct workbook? (workbook 1 in my example)
I have a group of Excel (2003) template files, each with a different name. When these files are opened they ask for certain information and save themselves as a unique name. Within these files are VBA routines and functions and, because these files basically do the same things, they have the same name. These routines exist in their own unique modules each with a different name. Unfortunately, in these files there are differences in the number of columns of information these routines act on so once they have been saved I need them to only act on their own columns only.
My problem is, when I have more than 1 of these files open in the same instance of Excel, I try to run a file using a control letter combination Ctrl-e and it sometimes runs the version of the macro in the wrong file. This leads to all kinds of strange thing happening. What I can do to force the correct macro to work in all instances?
The below code is supposed to delete data on the last sheet of the workbook HeadsetOutTimeMay.xls however for some reason it is deleting the data on HeadsetOutTime.xls What am I doing wrong?
For I = 6 To 18 Sheets(I).Visible = xlSheetVisible PrintSubs Sheets(I) Sheets(I).Visible = xlSheetVeryHidden Next End Sub
When it starts printing it starts off with my "JOBCOM" sheet which is sheet20. why it would start printing with that one. That sheet shouldn't even print at all. It's only supposed to print sheets6 to sheet18.
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.
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.
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 MrExcel.com ' 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
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.
I have data in 2 different workbooks, and I need to transfer all data in 'Jx_ex1' to sheet1 in the 'template' workbook, and all data in 'Gr_ex1' to sheet2 in 'template' workbook. All data should be transferred to the same cell numbers, from A1 to Y5000. I actually have loads of workbooks to transfer to the template but if I have an example with the two then im sure I can work out how to modify it. All files will be in the same folder.
i have a button that copies data from cell A5:K5, and pastes it in Sheet2 of my workbook.
The data is ordered like this
------ ------ ------ button1
------ ------ ------ button2
------ ------ ------ buttonX
I have like 40 buttons attached to 4 rows each. And in that group of rows, i have some cells with an IF= formula, and some minor coding. The problem is: If i press button 3,4,17,29 before i press every other button before that. I get an reference code fail at the cell.
If i press the buttons in order. Like 1,2,3,4,5,6,7 - it appears smoothly.
More info: The button copies data from for example A5:K5 and pastes it into the next blank row of sheet2. But i want to be able to press button 2 before i press button1, without getting an error in my =IF cell.
If i press the 40 buttons in a row, and then starts to mix up the button pressing. It appears smoothly.
I have a macro that saves the workbook after it has finsihed the rest of its formatting. I am finding it is saving in the wrong folder. I looked at my code and it is clearly states 'my documents' where I want it to save. Yet it keeps saving on a random folder on a shared drive.
This macro has a problem in that it totals up the colunmns in two places by error
The total should be at the end of the coumns only.
It gives me a total part way through the list which muddles things up a bit.
Sub prepareSAPexport() ' ' prepareSAPexport Macro ' prepare the sap export ready to be compared to CDS ' ' Keyboard Shortcut: Ctrl+s ' Range("A1").Select
I'm exporting a csv file from a membership database with the attendance of members. With a macro i open the file and copy the contend to excel. With the macro the column for the date has *randomly* wrong formats. Example the date in column B:
Aasia Tallah19-03-2009 19:33 Aasia Tallah17/03/09 7:49 pm Aasia Tallah15/03/09 7:53 pm Aasia Tallah14/03/09 6:34 pm Aasia Tallah03-12-2009 20:05 Aasia Tallah03-11-2009 19:56 Aasia Tallah03-10-2009 19:54
I try to change the format of the column, but that does not work. Rows 2,3 and 4 seem to be text but they are not. If i access the edit bar for example the 17th of march and store without changing anything, it changes to the format of the first row. That is how i want it. If i copy the column by hand, every thing is fine.
All of the following i tryed: - format date column of the csv before copying... failed - copy special with only pasting values... failed - preformat the column i paste the date to... failed - delete the sheet i paste to and create a new one... failed - copy the macro to a new workbook... failed
I have a workbook where I have a button to add a sheet, this works fine except for a formula that the macro edits on every sheet so we can have a running total quantity for the project. The problems occurs if the user changes the name of the last sheet in the workbook, then adds a sheet then the formula gets a reference error. The sheets are named 1 , 2 ,3 and so on, but it is nice to be able to change tha name of the sheet to the date.
How to get an automatic macro to run from my first sheet (sheet 1 of 10).
However, on my first page, users must enter 36 numbers in exact order and then those numbers need to automatically be copied to start at A3 on my following 9 sheets.
Right now, it pulls a sheet from another workbook, and saves it to this workbook. It works great. My problem is that I want it to keep what it pulls in, and not change when the other workbook changes. The other workbook has a macro that clears its cells every morning, so new info can be entered.
I want the old info that the code below pulled over to stay the same, so that I can keep a running history of what was entered everyday. At the moment, if I run the clear cells macro in the other workbook, it also clears all in this workbook. What needs changed in the code to make it work right?
I have a workbook, 'MyWb.xls' and a sheet with buttons to which I attach some sheet macros. When I assign a macro to a button I pick the appropriate reference, e.g. Sheet2.MyMacro, and this gets recorded by default as MyWb.xls!Sheet2.MyMacro
When I create a new single sheet workbook with the instruction ActiveSheet.Copy, I end up with a new workbook, say 'Book2' along with the buttons as expected. However the macros attached to the buttons still refer to the MyWB.xls file and not the new 'Book2.xls'. As part of the process of creating the new book I've tried redefining the macro with the instruction
I have 20 excel files each having more that 1000 records, the format and header for all the files are same.
I need to consolidate all these 20 different files into one excel sheet, having only one header row at the top. Since I need to perform this step frequently, I am planning to automate it.
