Im writing a macro to assist me in creating a group of dependent lists. I need the syntax for naming a range with the content of another cell. For example: Cell A1 contains a word. I want to name the cell range B1 through B5 with the word currently populating cell A1. Is there a command I can use?
Right now, starting from A1 I use the copy command to copy the content of cell A1. I then select the range B1 through B5, place the cursor in the NAME BOX and press paste. See my crude macro below which performs everything but pasting the name.
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?
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 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)
I just started learning VBA and I can't figure out how to create a macro that will update my date range. I need it to automatically add the next date in the next blank cell so it would be like this:
1/1/13 1/2/13 1/3/13 1/4/13
Here is an outline of my work book: GANTT4-1.xlsm
Id like a date to be added if the maximum date in row 6 is exceeded by a date in column k.
Sub REMOVE() ActiveSheet.Unprotect Password:="P@ssw0rd!" Set Rng = ActiveCell ActiveCell.EntireRow.Delete ActiveSheet.Protect Password:="P@ssw0rd!" End Sub
However I would like not to apply that macro when I am in the row 12:26! btw, I am running my script by pressing CTRL+D.
Last thing would it also be possible to "extend" the exception to another range ? (name manager -> row 84:276 called "NEXT") if possible to define two name manager for the exception to implement to the existing macro.
I have a worksheet using several columns and 100 rows. Column B and column C can contain a value "x" in either coulmn but not both in a specific row. The macro illustrated works perfectly for what I need but there must be a way to point the macro to a range of rows rather than using an "if" statement for each row as I've done.
I have a worksheet with a range of data that will change each month. Columns a,b,c and d hold data. Row 1 is my header row and the last row contains totals.
I need the range of column E to equal the value of Z1, which is declared as the last day of the month.
From my research on trying to apply this function, I have peiced together several codes and am unable to get my end result. I want to add a worksheet and rename it with text formula on my main "Macro" sheet from cell D1. The name in cell D1 is a date that changes based on a formula. I can get the macro to add the worksheet but then it stops at renaming it:
Sub NewTab() ' ' NewTab Macro ' Macro recorded 9/10/2009 by andrea_1 '
' Dim wsNew As Worksheet
Set wsNew = ThisWorkbook.Worksheets.Add ActiveSheet.Select ActiveSheet.Name = Worksheets(Macro).Range("D1").Value
1) column A (rows 1-54) needs to be printed with every printing 2) other columns (b-e) or (f-i) etc need to be printed out with each printing
Is it possible to setup a macro to do this as long as I know which columns need to be printed every time (will be same number of rows every time)
Basically, there is info in column A that needs to be printed every time, then I want to be able to print out a months worth of data (b-e for january) for each printout. It is essentially a calendar that I need to print up a hard copy of.
I know zero about vba and much of what I have found on the internet has only confused me more.
i am getting is it only formats H11:AE12 & H14:AE15, It does not format the first range or any others. Am i doing something wrong? I recorded the first one H8:AE9 then just added the ranges for the others, have i missed something?
I am trying wihout suceess to create a range to copy in a macro each time the macro loops. This is thus a dynamic range. My failed attempt is listed below. How is this done?
ColumnAdjust=2 For I = 1 To 282 Range ("B5").Select Range(ActiveCellOffset(0,ColumnAdjust),ActiveCellOffset (0,ColumnAdjust+1)) Range(Selection, Selection.End(xlDown)).Select Selection.Copy . . . ColumnAdjust= ColumnAdjust=2 Next I
I need the macro to create a named range based on a last column and row that can change. It won't change once the report is set up, but since this is a template that will be used for hundreds of projects, it needs to be flexible to accomodate a varying numbers of columns or rows
The starting cell will always be A10, but the ending cell can change depending on how many columns and rows are in the table.
Right now, the code I've created is
Sub NamePivotData() Dim LCol As Long Dim LRow As Long Dim RngEnd As Long
[Code].....
I need it to refer to ='Calendar Setup'!$A$10:$*$** where * is the last column in the table and ** is the last row in the table.
I am trying to create a macro such that in a given column (AB 0 -100), if the cell entry is "1", then another cell in the same row will be copied (C) and pasted to a third column (F).
All of the entries in column AB are 0 or 1.
So, for example, if AB23 = 1, the contents of C23 are copied, and pasted in to F23.
If AB24 = 0, nothing happens.
Code:
Dim i As Integer For i = 1 To 100 If Range("AB" & i).Value = 1 Then Range("C" & i).Select Selection.Copy
I am trying to write a macro to sort a range of data by another column. Here's the code that I already have :-
Code: Public Sub sort_data(sort_range As Range, key_range As Range) ' MsgBox sort_range.Address ' MsgBox key_range.Address sort_range.Sort Key1:=key_range, Order1:=xlAscending, Header:=xlYes End Sub
However I get a run time error 1004 stating that the sort reference is not valid.
1. I can create basic macros .. but how do I write a macro to hide a range (of 10 rows) named "dmargin". That for my button no. 1.
2. How do I write a macro to unhide the named range "dmargin" for my button #2.
I tried using VB codes to achieve both conditions -- it works but it either continuously protects or unprotects my ws -- so I think it is better to use a low level codes (macro buttons).
However if the number comes up twice it will only find it in the first instance, how to find the number on the next line instead of the next different number?
I am getting he Subscript out of range error when trying to run this macro. I have the macro on a macro workbook and I am referencing another spreadsheet.
Sub Dater() Static Count As Integer Dim Wb As Workbook Dim Ws As Worksheet Dim FileStr As String Set Wb = Workbooks("Pending Approval by Responsible User") Set Ws = Worksheets("Pending Approval by Responsible")
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
I have a workbook and I am looking to write a macro that will do the following in a sheet named Monthly Chart.
Could someone assist me (or let me know if this is possible) in writing a macro to do the following: First search in column C for the last row with a value, then select the range C(last row with value):P(last row with value), then copy that set range into a worksheet named Chart2.
Macro to add in a row in a named range, at the bottom of the range. the code i currently add in only to a certain row i need to add into a named range at the bottom.
Sub AddPersonnel() With Sheets("DOR") .Unprotect Password:="techedit" Rows(56).Insert Rows(55).Copy Destination:=Range("A56") .Protect Password:="techedit" End With End Sub
I've got a task I would like to automate but am not 100% sure on how to best do this. I have used the macro builder, but it always specifies a range of cells for my sorts and subtotals.
Essentially this is my issue, I'm responsible for an inventory report that I have to complete 3 times a week and they insist on using excel, not access. I merge my data, and always have to sort the data a certain way, then I apply subtotals to several lines, and then filter by one column containing the word total so that I can apply my formatting.
My problem is, I use three workbooks, with about 6 worksheets each and have to do it for every worksheet, but when I create a macro it always specifies a range, but the number of rows can change day by day!
Below is a copy of my macro, if you have any tips on how I can make this universal so that I can run one macro and sort, subtotal, and filter at least all sheets in one workbook and that would be great! In the example below I had 12,678 rows of data, but as stated before this changes all the time, and is different for each sheet (one sheet is all units, then each unit has their individual sheet also).
Macro:
Sub SheetTotal() ' ' SheetTotal Macro ' Total Unit Sheet ' ' Application.CutCopyMode = False Range("A1").Select ActiveWorkbook.Worksheets("EAB UNIT TOTALS").Sort.SortFields.Clear ActiveWorkbook.Worksheets("EAB UNIT TOTALS").Sort.SortFields.Add Key:=Range( _ "D2:D12678"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _ xlSortNormal.........
When I'm recording a macro it records the range I'm currently doing... example - whatever figure is in Column A I want to multiply by 2 - the result being in Column B. I have figures down to Row 10 in Column A, so the range is A2 - A10. (A1 being a header).
Is there a code I can put in the macro that doesn't set the range to only A2 - A10, and will multiply anything that's found in Column A - except for when the Cell is blank?
