VBA Macro - Copying Entire Row From Selected Cell (within Pre-defined Range)
Jun 21, 2014
I am looking to run two separate macros. I have a project plan and I am looking to be able to select a button whereby on-click, a new row is created within a selected cell. However if the cell clicked is outside of a pre-determined range, then the task is automatically entered at the bottom of the plan. I would like for the copied row to go ABOVE the selected cell and have all of the same formatting as the row below (not the top - as is default in Excel).
The second is going to be very similar but will copy a task category (like a header item) and the first task (row) found below. It will also be copied from below and be inserted above the selected cell.Both macros will clear certain cells, whilst maintaining the contents of others, with formula contained. I.E. Columns C,D,E,H,I,J will be cleared.
View 2 Replies
ADVERTISEMENT
Jan 11, 2013
I created a named range formula:
=TEXT(TODAY(),"m/d/yyyy")&" "&TEXT(NOW(),"h:mm AM/PM")
The goal of that formula was to return a Date/Time stamp that is fixed and doesn't change over time. The formula is intended to run in which ever cell that I select to enter it into. I named the formula:
=DS
What I am trying to do is create a macro that will run the formula using a hot key function. I want to be able to select a cell and hit CTRL D and have that above formula run in that cell. I have multiple sheets in my workbook and I need the macro to be able to run on any sheet in my workbook. Below is what I have:
Sub DateStamp()
'
' DateStamp Macro
[Code]....
I was trying to apply the macro to only a certain range of cells on any given sheet.
View 1 Replies
View Related
Nov 10, 2008
I have a macro that will go through a set of numbers and check them against a condition. if the cell complies with the condition i want to copy the entire row to a new sheet. sofar I have tried the following to select the current row but to no avail.
View 4 Replies
View Related
Aug 22, 2013
So I have the code that allows me to search for the a certain value in a cell and then copies a certain cell to the other spread sheets. Here is the code that does that
Code:
Sub SearchMacro()
Dim LR As Long, i As Long
With Sheets("Sheet1")
[Code].....
The code will just need to search for the string "HSFL" and I won't have to hard code all the different variations of HSFL as I mentioned above.
View 9 Replies
View Related
Jul 3, 2013
I'm having a column called "Body". Each cell in this column contains a huge amount of text. I want to select automatically certain values from that text and copy them into other cells. This image will explain what i mean:
I tried to work with the function MID but that doesn't work because the values don't have a fixed position. They don't start always at the same character number.
View 3 Replies
View Related
May 19, 2014
I'm using the following code to delete select rows one at a time. I need the last row in the range to remain therefore I prevented the user from deleting the row one up from the row that contains "Total Hours" (which is always in Column B). The code works great as long at the user clicks into a cell in column B. If the user clicks into a cell in column A, C, D, E, F, G, H, or I then the code allows the user to delete the last row.
I believe I need to search entire rows to determine if the row contains "Total Hours" .
[Code] .......
Attached File : Staffing Report 1.44.xlsm
View 6 Replies
View Related
Mar 2, 2012
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.
View 4 Replies
View Related
Sep 18, 2009
Basically I have a filtered list in colunms A to D. The user selects a cell in column A and I want to then be able to run a macro which copies that cell and the adjacent one in column B, into columns A and B on sheet2.
The complications are
Column B on the second sheet is merged with columns D to E so a straight forward cut and paste of A and B on sheet 1 into A and B on sheet 2 will not work in one cut and paste as it complains about merged cells. It would need to copy and paste A and then copy and paste B to avoid the error.
The row on sheet 2 where it is being pasted needs to be the first row between row 10 and row 30 where col A is blank (so it doesnt overwrite a previous copy and paste as they are basically creating a list on sheet 2 in those rows by runing this macro repeatedly). if there is no blank row then it should insert a new row at row 10 and paste into that
View 9 Replies
View Related
Oct 4, 2006
Trying to add a named range at run-time
Here's what I have so far ...
View 9 Replies
View Related
Jun 9, 2009
I am having some trouble with a variable range selection within a regression. I keep getting an "application-defined or object-defined error." I've isolated each statement to find that the code that is causing the regression not to work is below (the error for that line of code states that the Select method of Range class failed):
View 6 Replies
View Related
Feb 26, 2009
this is the line it gives the error on.
Range("A1").End(xlDown).Offset(1,0).Select
View 9 Replies
View Related
Jun 5, 2009
So I'm correcting macro at work and keep running into this error:
"Application-defined or object-defined error"
View 5 Replies
View Related
Sep 5, 2012
The problem is when I highlight a row with some color the original color of the row is gone, so I tried this code, and again, it's removing the original format and color for the row This is the code from McGimpsey & Associates : Excel : Highlight row with background colors
Code:
PrivateSub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Const cnNUMCOLS AsLong=256
Const cnHIGHLIGHTCOLOR AsLong=36'default lt. yellow
Static rOld As Range
Static nColorIndices(1To cnNUMCOLS)AsLong
Dim i AsLong
IfNot rOld IsNothingThen'Restore color indices
[code].....
How can I retain the range's historical color so that when I deselect the row it reverts properly?
View 4 Replies
View Related
Jul 10, 2012
I use an excel workbook that contains around 20-30 output tables/ranges and i would to have a macro which will enable me to open a specific word document and paste these tables/ranges from excel into the word document. I am unsure as to how to get excel and word to "talk to each other"???
To make things more difficult i would like to be able to set the paste location in the word document, and ideally i would the copy and paste function to be a normal copy and paste and not a paste special as the tables/rnage in excel have already been formatted correctly and column widths adjusted to fit the margin of the word document.
Currently I select the range in excel, switch to word and paste then continue until all tables/ranges are pasted. Not difficult but as certain calculations change these tables/ranges need to be re-copied and pasted to word which gets frustrating.
View 1 Replies
View Related
Aug 29, 2006
I get a run time error 1004 when I run this sort.
ActiveSheet. Range("A1:AC277").Sort Key1:=ActiveSheet.Range("G2"), Order1:=xlAscending, Key2:= _
ActiveSheet.Range("E2"), Order2:=xlAscending, header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
DataOption2:=xlSortNormal
View 9 Replies
View Related
Sep 12, 2007
In several places in my code I need to define ranges and save them in Application.Names. I need to be able to reference the last row and column in that range but I don't know how. Is there a reference within the Range object for the boundaries used to create it?
ie, the B3 in Range("A1:B3")
Not all of the ranges will have content in them and many of them will overlap, so looking for the last non-blank cell won't work.
View 4 Replies
View Related
Oct 16, 2008
How do ask if a entire row has been selected in vba, e.g. from column 'a' to 'iv' with no specific row number.
View 5 Replies
View Related
Oct 28, 2009
In the attached sheet. Cell C6 represents the concatenation of aspects of a product. I want to know if it is possible to use that cell as the named range (there is a corresponding named range) in a lookup. The lookup gives me the product code.
View 5 Replies
View Related
Dec 20, 2013
I need to obtain the max value in a column of numbers (DJ3:DJ242) with the range of the cells considered, set by a slider that produces a value between 1 and 500 in cell BD51
View 4 Replies
View Related
Aug 1, 2008
I have a list of numbers in Column A and in Column B I have a standard Sum formula which sums the adjacent number in Column A and the four numbers before it.
A B
1 =Sum(A1:A5)
3 =Sum(A2:A6)
5 ...
3 ...
5
6
I would like to have a formula which references another cell to define how many rows to sum from the starting cell. So in the above example, the total number of rows which are added together is 5. If I wanted to change all of the SUM formulas to add 6 rows, I would like to be able to change the number in the reference cell to 6 and all of the sum formulas would switch to adding 6 rows.
View 3 Replies
View Related
Nov 14, 2008
I have a button on an excel sheet call 'Delete Row' where I want to put code underneath so that a user can delete an enirerow.
I have the
Selection.EntireRow.Delete Shift:=xlUp
at the moment but I want to check that the user has selected the entirerow before I delete it.
View 9 Replies
View Related
Nov 22, 2011
How to assign a range in excel using a defined (different) cell.
I've this formula
=INDEX($A$1:$A$17,RANK(B2,$B$1:$B$17))
I want to to choose the range from A1:A17 ... i'll use the formula too many times with different ranges for example A1:A21 and i want to know a way to insteed to change it manually each time to make the formula consider the value i insert in a different cell, so i can only change the value in this cell.
for example:
=INDEX($A$1:$A$D1,RANK(B2,$B$1:$B$D1)) so it can consider the value in cell D1
View 7 Replies
View Related
Oct 7, 2006
I have the following code which will determine if a user has selected a entire column.
If Selection.Rows.Count = 65536 Then
MsgBox "User has selected an entire column."
End If
Although it worked, but is there a better way to do it? I was thinking if future version of Excel goes beyond 65536 rows, then this code is definitely unusable!!
View 4 Replies
View Related
Nov 30, 2006
how to enable a button only when a full row has been selected
View 9 Replies
View Related
Apr 3, 2014
I have a macro which sorts data within a cell. This is working perfectly fine but the only problem with this is that it works only for a single which is selected. How can I apply this macro to range of cells I select using a mouse.
View 3 Replies
View Related
Mar 19, 2013
A have three columns with "Kilograms range" and corresponding value.
A========== B========== C
KG From====-To======= Predefined Value
what formula will fill up the cell with the predefined value automatically based on the range, after a kilograms are entered in a different cell as explained in the attachment.
View 2 Replies
View Related
Oct 7, 2007
i have found this code on the net but can only get the control sheet to work when i run macro "PrintSelectedSheets" i get a run time error 1004 application defined or object error i have put the code in the workbook object but having problems, it seems to be because the sheets that i am trying to print are hidden can this code be edited
Sub PrintSelectedSheets()
Dim i As Integer
i = 2
Do Until Sheets("Control Sheet"). Cells(i, 1).Value = ""
If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select
'ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
i = i + 1
Loop
End Sub
This code seems to work ok
Sub CreateControlSheet()
Dim i As Integer
On Error Resume Next 'Delete this sheet if it already exists
Sheets("Control Sheet").Delete
On Error Goto 0...........................................................
View 2 Replies
View Related
Jan 20, 2006
I have a worksheet with ever expanding data - rows at the bottom of the data
are continually added. I have a simple macro that sorts all of the data
according to preset parameters and selects the next blank cell in column A,
ready for more data:
Sub Macro5()
Range("SortRange").Select
Selection.Sort Key1:=Range("SortRange"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom
Do Until ActiveCell.Value = IsEmpty(True)
ActiveCell.Offset(1, 0).Activate
Loop
End Sub
When running the Macro, this leaves all of the cells in the range 'selected'
(ie; coloured-over). What do I need to add to the Macro to just select the
cell in Column A and remove the highlighting from all the other cells?
View 9 Replies
View Related
Apr 26, 2013
I have a spread sheet with large number of data, problem is all are in various currency so rather than typing =#####.##/a1 in every cell to get the GBP amount (a1 where my exchange rate is linked) I thought if there is macro can do this job for rme.
So what i need is macro which once run enter the formula after the numbers already in the cell in selected or given range.
View 1 Replies
View Related
Apr 23, 2008
I am copying and pasting from two different " timesheet" spreadsheets into a list. One of the timesheets has blank rows. I am attempting to sort the blank rows to the bottom after I paste the data, but every time I do, it either replaces the top row with "true" or deletes the headers,
Sub SortBlankRows()
Dim rngCurrent As Range
Dim c As Range
Dim inUsedRow As Integer
Set rngCurrent = Workbooks("Payroll Summary.xls").Worksheets(1).Range("A1:J1")
inUsedRow = Workbooks("Payroll Summary.xls").Worksheets(1).Range("D65536").End(xlUp).Row
rngCurrent = rngCurrent.Resize(inUsedRow)
rngCurrent.Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Key2:=Range("F1") _
, Order2:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
:=xlSortNormal
End Sub
View 3 Replies
View Related
