Where To Place The Macros
Nov 6, 2007
I have created a excel spread sheet to be set up like a schedule, including multiple macros through the sheet, when I started it put the macros into "Module 1", now if I wanted to use this same worksheet and copy it and create multiple sheets in one file via the tabs does it make a difference where the macros reside? if it remains in "Module 1" it will only be in one spot will this create problems later on or should the each reside in the sheet objects.
View 9 Replies
ADVERTISEMENT
Aug 17, 2006
When you enable macros the sheets 2, 3, 4 ect are visible but if you disable macros, you only see sheet 1 and and you can place a message on the sheet saying this will only work with macros enabled.
View 2 Replies
View Related
Feb 22, 2007
I am after a macro to do the following, my visual basic skills are very limited (non existant):- Look at the date in cell A1 on Sheet 'Live Report' and err 'remember it' Copy a range of cells from A3 to A10 on 'Live Report' Go to sheet 'Monthly Summary' and find the date that had been remembered previously (this date will be in column A on 'Monthly Summary' which will probably be a mixture of values and formulas). After the date has been found paste special and transpose the 'values only' copied range from 'Live Report' (A3 to A10) in column B on 'Monthly Summary' next to the date that has been found in Column A.
View 2 Replies
View Related
Oct 19, 2006
I have a macro that imputs data from an external database and puts it into a temporary worksheet. This data has 3 columns (ID, Date, Amount). I am then making another sheet which has X number of tables (one for each ID), with the years being the column headings, and months being the row headings. ie.
| ID X
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
| ID Y
| +------+------+------+--->
| | 1999 | 2000 | 2001 |
+------+------+------+------+--->
| Jan | $100 | $250 | $300 |
+------+------+------+------+--->
| Feb | $200 | $300 | $200 |
+------+------+------+------+--->
| Mar | $300 | $250 | $100 |
+------+------+------+------+--->
| Sum | $600 | $800 | $600 |
| +------+------+------+--->
Currently I have a few hidden fields for the DSUM Criteria. I start making the tables. And then filling in table based off of the month and year. Doing so I need 3 criteria:
>= First Day of the Month
<= Last Day of the Month
= ID #
The problem is it takes Excel too long to fill in the 3 criteria fields, calculate the result, copy the result, and place it into the correct place on the table. Is there another way to get this data into the correct tables faster? Instead of using DSUM?
View 8 Replies
View Related
Aug 26, 2009
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies
View Related
Jul 28, 2008
I have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
View 9 Replies
View Related
Jul 23, 2005
What is the commands or script for deleting a macro automatically using
another workbook macro.
View 9 Replies
View Related
Jul 14, 2008
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies
View Related
Dec 14, 2009
I have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
View 11 Replies
View Related
Apr 13, 2007
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
View 2 Replies
View Related
Jan 21, 2007
What I have is a long list of about 100 items. I would like to set up an excel sheet that allows me to place checkboxes in each row and then whatever checkboxes have been checked, to simply collate and print out ONLY the check boxes that have been checked.
I have gotten as far as to create and rename the checkboxes but my skill ends there. I also need to know how to get each checkbox to correspond to a row.
I appreciate ANY help that you can give me.
EDIT: this doesnt NEED to be checkboxes, it can be optionbuttons if that is easier... simply something to select/deselect and print.
1.1 a blah
1.1 b blah
2.1 a blah
2.1b blah
3.1a blah
3.1b blah
3.3.a blah
etc blah
etc
View 10 Replies
View Related
Jan 19, 2009
We're doing a "Biggest Loser" competition at work. I have a spreadsheet that calculates the % of body weight lost. I need a formula that will look at the results and display 1st, 2nd, 3rd places. Here is this weeks results...
View 2 Replies
View Related
May 7, 2014
How do I do this?
View 3 Replies
View Related
Dec 5, 2008
I am trying to figure out how to insert a decimal into a number. For ex: 1234 to become 12.34. I tried creating custom or special formatting but I cannot figure it out.
View 3 Replies
View Related
Mar 13, 2009
how to control this,it should be 0.55. is they a wat too round off too next highest hole number? Here is a example.
View 2 Replies
View Related
Dec 11, 2009
The code below writes the names of all active worksheets into the A column of the active worksheet. I have a situation where I have 2 workbooks open (3 if you count PERSONAL.XLS). One of the worksheets has a consistent name, the other has a name that changes every week (not in a consistent pattern).
I'd like to be able to use the routine below to get a list of open workbooks, ignore the one I'm working in and PERSONAL.XLS, and instead put the remaining filename into other Private Subs in a User Form to run other routines.
View 6 Replies
View Related
Apr 12, 2009
Spreadsheet 1: So in column "A" I have all of the dates written out. Then i need column "B" to put the appropriate name next to the date that person chose in spreadsheet 1.
Spreadsheet 2: I have a live form that feeds data into a different spreadsheet.
I am using google spreadsheets.... The formulas are the same as in excel
View 14 Replies
View Related
Jan 6, 2012
I need to place cell value into one function (I cannot modify this function it is built in) .
For example:
Function is
CODE
A2=KGF
=ABCDFE_(my value of A2 thus KGF)_GHIJKL
Thus the end result:
=ABCDFE_KGF_GHIJKL
I can concatenate the string but I cannot evaluate it .
=CONCATENATE("ABCDFE_",A2,"_GHIJKL")
is there a way to do it ?
View 4 Replies
View Related
May 22, 2012
I have a column of percentages where I only want to show 1 decimal place if the value is less than 1%. The rest of the percentages I want without decimal detail.
I've tried custom formats and cant quiet get it right. Am I close with this?
[>1]#.#%;###%
View 2 Replies
View Related
Jul 10, 2012
We currently have a macro that has a limitation that it only works on 8 sizes, and only upto 12 Colours. We want to change this to make the number of sizes and colours totally variable. how to change this bit of code below so that the cells selected are not fixed but are variable depending on the number of sizes and colours as entered in sheet 2.
Current Code for When 8 Sizes and 12 Colours
Code:
Sheets("Sheet2").Select
Range("B30").Select ' This is Fixed
Selection.Copy
Sheets("Sheet3").Select
Range("G2:G109").Select ' Want this to be variable based on the number of rows needed starting at G2, this can be found out from Sheet2, Cell G28
ActiveSheet.Paste
[code]...
On Sheet 2 Cells G23 is used to enter number of Sizes, and G24 is used to enter number of colours. Cells G25, G27 and G28 then have formulas to work out other values that will be needed in the macro.
View 1 Replies
View Related
Aug 6, 2012
I'm trying to copy a worksheet and place it at the end. I will rename it afterwards. I'm using the following code but it doesn't work.
Private Sub Copy_sheet()
Dim wbk As Workbook
Dim wsh As Worksheet
Set wbk = ActiveWorkbook
wbk.Worksheets("Template").copy After:=wbk.Worksheets(wbk.Worksheets.Count)
Set wsh = wbk.Worksheets(wbk.Worksheets.Count)
wsh.Name = "New Sheet"
End Sub
View 4 Replies
View Related
Oct 8, 2007
Is it possible to place Text in a Cell if a CF is True?
So Far I am unsuccessful.
I have this CF in Column S
=IF(ISBLANK(R10346),"Need Dept & Cat")
View 9 Replies
View Related
Mar 19, 2008
I have the value 26.06 in cell A1, I want to have everything left of the decimal place in Cell B1 and everything right of the decimal place in Cell C1, is this possible. Bearing in mind A1 will be variable (ie it could be 159.65).
View 9 Replies
View Related
Apr 9, 2008
Not using VBA is there a way to take the worksheet name and place it in a cell?
View 9 Replies
View Related
Jul 29, 2008
is it possible to place time in a textbox on a userform?
I am trying to make 5 textboxes to display different time zones.
View 9 Replies
View Related
Aug 6, 2008
I use Excel 2002 - I have a column with Text in its cells. The cells usually range from 30 to 130 characters including spaces. I need to use word wrap.
I would like to be able to see where the 55th character is ( Including spaces ) - but I also need to see the rest of the contents of the text in the cell.
I have thought about coloring the first 55 character/spaces ?
or inserting a character at space 56 ? Example below
XXXX XXX XXX
XX XXXXX XXX
XX XXXX XXXX
XXXX XXX XXX
XXX XX~XXX X
XXXX XX
View 9 Replies
View Related
Aug 11, 2009
I have a spreadsheet where I have the first and last name in two separate columns. In the same spreadsheet, I have a column where there is an Email Convention. The email convention can change from row to row. Is there a way to take the first and last name columns and make it format according to the email convention column. For are some samples.
First Name Last Name Email Convention
Ada Smith @mail.uch.org
Ann Blanc _@chs.net
Bob Jones .@halifax.org
View 9 Replies
View Related
Aug 25, 2009
I currenlty have a spreadsheet that works well but uses a lot of array formulas and, therefore, is running VERY slowly. The slow speed makes it impractical to work with, so I am trying to replace the array approach with the DMIN function. However, I am having trouble getting the DMIN function to find the minimum value in a column range according to a criteria in the same arrays did for rows.
Here is my problem:
I currently have arrays (composed of dates) in rows and I have converted this to non-array columns. I have pasted a sample of the new worksheet below.
Column D ["Original End"] has dates that are static. Column F ["New Start"] has dates that can change according to user inputs. I want Column G to find the date in a Column D that is greater than the date in Column F in the same row.
In the table below, you can see that I was able to get this to work for cell G3; however, when I pull the formula down, it stops working. Here is the formula I have in cell G3: =DMIN($B$2:$E$15,"End",$I$2:J3).
Conceptually, I want to write the following function in cell G4: =DMIN($B$2:$E$15,"End",">"&F4), where the criteria is 'greater than date in F4' - but this obviously doesn't work.
View 9 Replies
View Related
Oct 26, 2009
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort?
Private Sub ConditionalRowDelete()
Dim NumRows, iLine As Integer
ActiveSheet.UsedRange.Select
NumRows = ActiveSheet.UsedRange.Rows.Count
Dim CircuitType As Range
Set CircuitType = Range("C" & iLine).Value
For iLine = NumRows To 2 Step -1
If Range("A" & iLine).Value > "6999" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
For iLine = NumRows To 2 Step -1
If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then
Rows(iLine).EntireRow.Delete
End If
Next iLine
End Sub
View 9 Replies
View Related
Jul 27, 2006
I was wondering if anyone knows code that will check to see if there is a certain value in a cell, and if there is place a "1" in another cell in that row.
View 2 Replies
View Related
