Macro To Change Range / Action Depending On Worksheet (range To Autofill)
Sep 17, 2013
Currently my Macro should: Turn off any filtersNumber column A from 1 to 1000 (starting in A14)Drags formula from K14-O14 down to last row of data shown in column Athen puts cursor in last empty cell in column B ready for user to enter data
On point 3 - I want the range to be K14-O14 if active worksheet equals "EXCHANGES" but if its on the "VALUATIONS" tab the autofill range should be L14-P14
I have found bits and pieces of macros on the internet and put them together so if my macro below is not the most effective for my needs but here it is in it's current state:
Here is my macro:
Sub AddNewEntry()
'TURNS OFF FILTER IF FINDS ONE ON
Dim wks As Worksheet
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I have a budget worksheet that uses a spending forecast sheet to determine where the spending level for each budget code should be to the end of the current month. In other words, the Forecasted YTD To Month End fomula for each budget code in the budget worksheet is simply:
=SUM(Forecast!D8:F8)
Where column D is Jan, E is Feb, F is Mar etc in the forecast which distributes the budgeted amounts by month.
In each row on the budget sheet I want to evaluate today's date and if we're in March I want the formula to read as above but when we move to April I want it to use G as the end column in the range to sum.
SUMIF seems cumbersome in this circumstance and I wonder if a UDF might suit better.
I want to change the colour of a cell depending on its value, when compared to another row of data identiified by a value in another cell. To try an clarify:
b3 = 1 b5=4 Because b3 = 1 then compare cell b5 with the row g1 as b3=g1 then depending on its postion set a colour
In cell A1, I have the month number (eg, 1, 2, 3,). The month number reflects current month and will automatically change with every month. For example, right now it’s 6, next month it will automatically change to 7. Each two columns in Range A10:X20 represents the data from January to December. I want to use a worksheet event to change the background of the current month two columns in the range to yellow color and the two columns in the range will be visible when I activate this sheet.
I have an excel file that contains two sheets. Sheet2 contains rows of different types of products with each of the columns containing details about the product.
On Sheet1, I have setup a data validation list in which a certain cell contains a drop down box that selects a product from the first column of all the rows on t from Sheet2. I used
If a name is entered in cboRepName combo box, I want to check if the name matches the list of names in the named range "Names" found in sheet repInformation. If the name is not found, then perform tasks.
Here is what I have so far, of course is not working, but I think I am close.
Code: Private Sub cboRepName_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean) Dim repName As String Dim nRange As Range
The gaps are of varying length. So far I have been playing with the Range("a1").Select ActiveCell.Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) 'do copy action ActiveCell.Offset(1, 0).Select Loop of course, this stops when it reaches a blank cell.
I think the best way to do it is if I set a range, and then get excel to go down the column and copy whereever there is cell content.
I am trying to have one cell (G5) populate with a timestamp when a change is made to any cells in a range (some are merged). The code below worked for a second, until it didn't.
Code: Private Sub Worksheet_Change(ByVal Target As Range) If Intersect(Target, Range("F8:G33")) Is Nothing Then Exit Sub Range("G5").Value = Now() End Sub *Getting a yellow arrow by the row starting with Range("G5")
I have this code working fine. It applies conditional formating to two rows ((K3:K65) and (J3:J65)) and checks when a value changes in row B to reaplpy the formatting. I'd like to had add more columns to it. It would have apply the same conditonal formatting on rows ((O3:O65) and (P3:P65)) but when a value changes in row (N3:N65)
So basically, I want to keep the first working part of the code and have it to check at more rows with a different reference for the conditionnal formatting.
Private Sub Worksheet_Change(ByVal Target As Range) Dim rng As Range Dim rngJ As Range If Target.Count > 1 Then Exit Sub Set rng = Union(Range("B3:B65"), Range("J3:J65")) If Intersect(Target, rng) Is Nothing Then Exit Sub Set rngJ = Range("K" & Target.Row) Select Case rngJ.Value Case "" rngJ.Interior.ColorIndex = xlNone rngJ.Offset(0, -1).Interior.ColorIndex = xlNone Case Is >= 20.............................
Have a query that pulls general ledger data. Named Range SALES defined for Sales Account numbers. What is the best way to always have SALES (which pulls as negative number) converted to absolute values?
I have in my mind that it would be some sort of worksheet change event . . . but I don't really grasp the byval/byref TARGET concept by itself, let alone whether it can work on a named range.
I wish to monitor a change in a certain columns at some worksheets. I have - after having consulted John Walkenbach' fantastic book Excel 2007 Power programming with VBA - understood how I do to monitor a change in specific worksheet, by using the Intersect-function in the change-event on that particular worksheet. That works fine.
My situation is that I have an application that I start out with a certain number of workheets in a workbook, and in the course of the work that the application does, it now and then adds worksheets, and these worksheets I would like to monitor for change in a special column. I know, by looking at the names of the worksheets which ones of those in the collection that I want to check.
I therefore used, not the Worksheet_Change event but the Workbook_SheetChange. It doesn't work quite well. I guess my question for now would be:
The call to this event looks like this:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
1) How - in what way - should I use the first argument, the "Sh as Object"? Can I/Should I give it a name?
The error message I get when I use an instruction like this:
If Not Intersect(Target, Range(M:M)) Is Nothing Then 'Do Stuff end if
The method 'Intersect' in the object '_Global' failed
is it possible to have a userform activated as a result of a Worksheet_Change event on more than 2 cells. Currently I have the code below which will activate userforms if either a cell in Column G or a cell in Column T is changed.
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("G:G", "T:T")) Is Nothing Then Exit Sub If Intersect(Target, Range("G:G", "T:T")) = "" Then Exit Sub If Not Intersect(Target, Range("G:G")) Is Nothing Then GlngRow = Target.Row Call Module3.ShowList1 End If If Not Intersect(Target, Range("T:T")) Is Nothing Then GlngRow = Target.Row Call Module3.showlist4 End If End Sub
What i need to do is activate another userform if a cell in Column AC is changed. I tried this code but got an error "Compile Error: Wrong number of arguments or invalid property assignment", which leads me to believe I can't add any more cells to the argument and it highlights the word Range in this line - If Intersect(Target, Range("G:G", "T:T", "AC:AC")) Is Nothing Then Exit Sub..........
I have a worksheey of data and i would like the tab to be a certain colour dependant on the the contents of a range of cells within that worksheet is that possible. i know i need to use VBA and it cna be done based ont he contents of one cell.
So for example i have a sheet of people i am awaiting payment from, based on the last column " bill paid" i want the tab to be Red if there are any blanks in this column/ range and go green when all the cells in the column/ range are filled with "y"
I have the following code that I would like to trigger when cells B26:U26 change to something other then 0 due to a formula? How was I make this happen?
I have this code that works fine until I password the sheet and the range (b9:d65536) is protected. When I protect the sheet and make a change I get a debug error. What would I need to add/change to handle protected cells on a protected sheet?
VB: AutoLinked keywords will cause extra spaces before keywords. Extra spacing is NOT transferred when copy/pasting, but IS if the keyword uses "quotes" ....
I want a macro in one worksheet to run when any cell (in a given range)on a different worksheet (dataentry) is updated. I have spent along time trying to make it work with no avail. The code I use to start my macro is as follows.
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count <> 1 Then Exit Sub
If Target(1, 1).Address = "dataentry!H5:IV72" Then If Not Intersect(Target(1, 1), Range("dataentry!H5:IV72")) Is Nothing Then
On Error Resume Next Application.EnableEvents = False
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?
I have multiple worksheets each with varying amounts of data. I have a number in cell A1 that I need to autofill down to the bottom of each worksheet. All of which have varying amounts of data.
I want macro2 to activate whenever there is a change in Range(C25:C5000). I want macro2 to activate only once even if there are 10 changes to the cells within this range. I tried the code below but it does not work.
Private Sub Worksheet_Calculate() If ActiveCell.Row > 25 And ActiveCell.Row < 5000 And ActiveCell.Column = 3 Then macro2 End If End Sub
I'm using this code to run a macro whenever data is entered/removed from a cell within a range. (two macros and two cell ranges).
[Code] .....
Now I need to do the same thing for a different cell range (E3:E52) and a different macro (FormatChart2). Do I need to make a new Private sub or can I add it into the existing code?
MyFormulaCell is the cell with the formula, and TermnialCell is a variable set on a count of rows. This is working great, and I can loop through this for the columns I need to copy just fine by incrementing values, but as I apply this to several sections, it's taking me up to a minute to run the macro. In this "I want it now" day and age, I'm sure my coworkers for whom I am making this will not be happy to wait that long.
Is there a way that I can incorporate a selected range into this method?
I'd like to set MyFormulaCell to: Range(Selection, Selection.End(xlToRight)).Select and then autofill the formula down the number of rows contained in TerminalCell.
I have worksheets that refernce values in one column off columns in others and do this on daily basis. each day there are blank amounts in random fields which I inturn use a macro to delete rows with blank amounts
I then want to autofill the numbers from 0001 to the bottem line of the sheet where the word "END" always is, as the position of the end word is random each time and autofill always needs a definite range how do I get my macro to autofill down to the word "end" ?
here is the code that doesnt work for me and I've been trying to fix
Sub Macro1() For counter = 1 To 30 Set curCell = Worksheets("Sheet1").Cells(counter, 6) If curCell.Text = "END" Then Range(Cells(1, 6), Cells(counter-1, 6)). _ Selection.DataSeries Rowcol:=xlColumns, Type:=xlAutoFill, Date:=xlDay, _ Trend:=False Next counter End Sub
I am creating a loop that will autofill 16 cells down. It copies the formula from one cell (Z230), pastes that 16 cells down (cell Z246), then changes the lock properties of the cell (Z246) before autofilling down 16 (to cell Z261). At which point the loop starts over again. (copies cell Z246,pastes it to Z262, fills down 16 , etc)
Because the cell I am copying from changes in each loop, I set a variable to grab the address of the starting cell and last cell of the copy.
I then put this into a String so it would return something like this
Range("Z245:Z259")
Problem is I get extra quotes around Range("Z245:Z259") - it comes out as string "Range("Z245:Z259")" This gives me a problem when I try to replace
I have 2 sheets - 1st sheet holds the data that is updated periodically. 2nd sheet pulls the data into A2:k2 and formats it for importing to another program. I need a macro that will copy the data from A2:K2 and auto fill down all rows from sheet 1 that contain data. I have read previous threads for autofill with no success using my below code.
Sub FillDownToLastUsedCell() Sheet 'RawDataA2:K2' Range("A2:K2", Cells(Rows.Count, "Sheet 'RawData'A2:K2").End(xlUp)).FillDown End Sub
The code below works fine, I can copy selected area into new sheet. However, I wish to custom change the Range("A1:AO164") value, for example, at cells(1,1) I key in A1, then cells(1.2) I key in A56, then this changes will reflect to the value in this macro to Range("A1:A56"). I tried using this way :
Dim A as string cells(1,1).text = a cells(1,2).text = b Range("a:b").Select
