Someone kindly wrote this macro for me a good while ago. It has served me well to date
In essence, this macro looks at column A, and at every instance of a value it either creates a new sheet within the workbook, names it, the copies the entire row OR if there is a sheet already just copies the entire row.
Im having huge trubles with the interior color for the cells.
I have a macro that removes the color of a cell ("No fill").
For that purpose, I use the line:
Macro that will strip out all the numbers in 1 particular cell column and and leave the text.
View 9 Replies View RelatedI have a macro that copies data from several workbooks saved in a particular folder to a master workbook.
The problem is that this macro only works when it is included as a module in the master workbook. Unfortunately, I cannot ask the users of this macro to insert a module, copy the code and run it each time they want to consolidate the master workbook. Therefore, I wanted to use this code by including it in a Add-In that I already prepared. The problem is that this consolidation macro does not run when it is in an add-in. Create a code that I can include in a macro add in that would insert a module in the master file, include the code that I already have, run it and then delete the module? The add-in would be password protected.
I have this script below that removes all code from the active workbook. The reason I am removing all the code is because I am running code from my personal library and the code changes the worksheet I am working with.So while stepping through I noticed it moves over to the sheet change sub routine from the active workbook that I am working on.
So I tried to remove all the code from the Active workbook but after I remove all the code it returns a expected end of sub error, where the old worksheet change sub was? but no code is in the entire workbook? So I tried to enter something in the blank sheet mod and it returns a (Cant Edit Module) error.
I have an inventory sheet in which I am trying to accomplish two things:
1. Have comments pop-up when certain cells are selected that will explain to the user what kind of information he should enter. (So far, I have been able to make this work.)
2. When the user begins to enter information into the first of the aforementioned cells, a worksheet change event will automatically select the next cell for him, so that he will be immediately ready to enter information into it. That is, the worksheet will automatically "tab" over to the next relevant cell. (I'm having problems with this part.)
All in all, for each product on the inventory list, there are three cells that the user must enter information into.
Step 1: The ending inventory amount (the amount of product on hand).
Step 2: The user's initials.
Step 3: The time that the user physically counted the inventory.
(Then, when step three is done, the worksheet automatically tabs to STEP ONE ON THE NEXT ITEM. That is where I am failing miserably, although that is not the only place. . . . Keep reading.)
Here is my problem. I am not able to have my cake and eat it too. The comments are popping up beautifully, at the right time in the right way. However, when I enter the appropriate lines of code to make the worksheet change "tab" over the next cell, problems occur: the comments that provide instructions to the user do not stay visible. They flash for a split second, and then disappear. I am attaching a sample so you can see what I mean (and then I'm heading off to lunch with the boss, so it will be while before I can respond). The lines of code that I would like to have working are currently "commented out."
I have hidden columns on a sheet.
I protect the sheet with a password before I send out the file to a 3rd party. However, it seems that all a user has to do is select the sheet cells, copy and paste onto another sheet or file. The protection is nonexistant and the user is able to unhide the columns.
Am I missing something?
Is there a way to "really" protect a sheet so that a user is unable to unhide confidiential columns? Preferably a method without VBA code.
I consider myself a power user of pivot tables, but recently, this one has stumped me.
For some reason every time I create a pivot table now, all the autoformatting options do NOT have borders in them AND is turning all cells color white; so I have to manually change the borders for each report. The autoformatting for non-Pivot Tables works fine still and I cannot find any option or setting that I would have changed to result in this. how to fix this or return the Pivot Table autoformatting back to normal?
I have a small userform (FormA) with two text boxes, a combobox, a help button, and an "OK" button. When clicked, the help button launches another userform (Form B), containing a text message and a "Close" button.
When I enter data into Form A, then click the Help Button to read what's on Form B, then close Form B, the data I had entered into the text boxes and combo box on Form A are deleted.
I've tried fussing with the modality, but that's not changing anything. I know I could write the text into a variable, then re-display it when Form B is closed, but that seems like a little (very little) extraneous stuff in an already bloated program.
I'm trying to have a macro write down an array formula, but when I hit ctrl+shift+enter, the recorder says it can't record. If I write in the macro ...FormulaR1C1 = {=...} then I get the formula as a text. Is there a way to tell the macro that a formula should be entered as an array formula?
View 2 Replies View RelatedI 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 3 Replies View RelatedI have this macro which runs great, but I would like to add a date formula to it before it auto fits the columns. I need cell E5 to Increase its date by 1 month to the last day of month from G5. For examply if g5 reads 2/28/07 I want a formula that will make e5 3/31/07 for every sheet.
Maybe something like this =DATE(YEAR(g5),MONTH(g5)+2,0). but I don't know how to put it in the macro to run on every sheet (except the ones I excluded.)
Sub PayrollAnalysisMacro()
Dim wrkSheet As Variant
For Each wrkSheet In ThisWorkbook.Sheets
If wrkSheet.Name <> "Download" And wrkSheet.Name <> "Recap by DC MTD" And wrkSheet.Name <> "Recap by DC YTD" Then
With wrkSheet
.Range("G5:H71").Insert Shift:=xlToRight
.Range("E5:F71").Copy .Range("G5:H71")
.Range("G5:H71").Value = .Range("G5:H71").Value
.Columns("G:BE").EntireColumn.AutoFit
End With
End If
after HOURS of trial and error. I was able to figure this out and get a formula written that would do what I needed. I'm trying to write a macro or formula that will sum the following
View 2 Replies View RelatedI have a spreadsheet with 2 columns (let's pretend), the first column uses Data Validation for a dropdown list. The second column contains a formula that references the 1st column and uses lookup:
=IF(A35<>0;LOOKUP(A35;NewCons;NewGroups))
So as soon as a value is selected in column A, column B changes to display a value.
Rather than just displaying this value, I would like the cell in column B to be completely replaced by this value, so goodbye to the formula.
Is there a way that I can do this? Will I need to go and study how to use macros?
AS per the attchement, I add a date in the cell H2 and when I select in the cell I2 the date in the column K changes as per the =IF formula..
My question is the following: Would it be possible, once I select the option in I2 to have the formulas in the column K changed for value? I put a example recording a macro!
HTML Code:Â
Range("K2:K4").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
I really wish to have that automaticaly done once I select the option in I2 without running manually a button.
I once heard that in order for a macro to insert a formula you must double all the quotations. Anyway, i did so in the following macro but i got an error message.
View 3 Replies View Relatedclarify here i attached file.
Hyperlink not running while am using hyperlink formula.
Hyperlink_Dout.xlsm
I have a data input worksheet, which uses the following code to fill in the missing zeros when cells are empty.
View 10 Replies View RelatedI'm trying to record a macro that will filldown a formula through a specific range of cells (like G2:g729).
View 2 Replies View Relatedi need a macro to insert the formula i have in Column M row 3, and insert it all the way down to the last letter that is contained in column k. So in this example The Last Letter in Column K is AQ, ( i dont physically mean last i mean last in alphabetical order in excel , such as ( W X Y Z, AB, AC , AD ) AD would be last.
So since AQ is last it would insert the formula 43 times, which is AQ. THe AFter tab should clear any confusion up because this is how it looks finished. I use various sheets like this so the last letter changes, so having this macro be dynamic and not attached to a sheet name would be ideal.
I am using the following Control Shift Enter (CSE) formula to find a value.
View 4 Replies View RelatedI have been having a problem with the worksheet (attached) and was wondering if anyone could give me any help.
I will explain the purpose of the sheet etc first and what it does at the moment:
This work sheet is to record peoples projects, hours related to those projects, and calculate their availability 37.5-(project hours+pto) for Belfast and dublin and 40-(project hours+pto) for chicago, and also their utilization which is =((37.5-Availability)/37.5)*100 for belfast and Dublin and ((40-Availability)/40)*100 for Chicago.
When a project is added I am currently going in and updating the formula for that person to incorporate the new row which their project is on, this is proving to be quite time consuming, and I was wondering if there were any macro’s that could be used so that it would search for a name on the first column and use each of the hours that are in the column for the given week to calculate the availability?
The utilization is not a big problem as it just uses the availability so as long as the availability is correct then the utilization is correct..
If there is not a macro do you know of any way this can be automated so that I do not manually have to add to formula for each cell when a new row is added with a project and hours?
I am trying to write a macro which will get values from column B and C and print the result on column D using a simple function like the one before:
D2 = "text" & B2 & "text2" & C2 & "text3"
I need this to be done in the macro, so that when I click the button, it will automatically create column D. Column B is formula and column C is constants.
I tried something like the one below:
I coudn't find anything on this forum on this subject.
Is there a way to execute [trigger] a macro from within a formula?
e.g. Based on an IF statement result, execute macro1 if true or macro 2 if false?
modytrane
Consider using a Worksheet Calculate Event macro (not Change Event) to
monitor the cell in question and call your macro when conditions are right.
"coal_miner" wrote:
> Greetings. Is there a way you can activate a macro through a formula.
> Example:
> =if(A1=B1,(macro here),"")?
I have a spreadsheet containing 2 sheets. In cell A3 of sheet 1 I have a formula that generates either a blank or "1". In sheet 2 I have a macro called Index which I would like to run when the formula in A3 displays "1".
Having done some research I know that the worksheet change event does not work with formula changes so have tried the worksheet calculate function but to no avail. I have done a lot of searching but cannot find what the problem is.
I have a workbook that will have a different number of sheets every time. All of the sheets have the same format. I have a summary sheet in which I would like to sum the total of Sheet1 thru the Last sheet in every cell from B12 to B24, then I am going to move on to other columns.
I am getting the same error everytime "Object doesn't support this property or method" and the error is #438.
I have tried different formulas, but none of them are working. Here is my code......
Sub FillSumSht()
Dim wb As Workbook
Dim sSheet As Worksheet
Dim LstShtNm As String
Dim LstSheet As Worksheet
Dim TotalSheets As Long
Dim s As Integer
Dim n As Integer
convert the formula into macro
I hv formula as following :-
=Left(B2, Find(" ", B2, 1) - 1)
I likt to convert into macro,
ActiveCell.FormulaR1C1=_ "=Left(B2, Find(" ", B2, 1) - 1)"
But it cannot work
Is there anyone know how to Use sumproduct formula in macro?
I need to know how can I code a Lookup formula in a VBA.
In Sheet1 of excel, I have several records of data in column A to E.
I have designed a userform, in which user enters data of column A in a textbox and on click of a button, it writes the textbox value in Sheet2.
I need to code a formula in VBA, so that column B and E of Sheet1 data is written in column B and C respectively of Sheet2 (corresponding data of textbox value).