Copy, Move And Delete Row, Based On Cell Value
Jun 21, 2009
I have a spreadsheet (Sheet 1) listing current Work Orders with each work order occuping a seperate row; Column E lists the status of the work order, with the status being chosen from a drop down list.
I would like to have a macro that will copy the entire row and paste into (Sheet 2) when the status is changed to CLOSED, and clear the contents of the cells on Sheet 1.
The aim of this being of course to have all open work orders on sheet 1 and all closed orders on sheet 2.
View 9 Replies
ADVERTISEMENT
Sep 19, 2012
i have code like this to copy certain word in cell and place it at new sheet:
VB:
Sub Foo()Dim i As Long, iMatches As Long
Dim aTokens() As String: aTokens = Split("Accommodation", ",")
For Each cell In Sheets("Sheet1").Range("C:C")
[Code].....
the problem is the cell source is not deleted. what line should i change in code above to move the result to new sheet and delete row source
View 7 Replies
View Related
Jul 11, 2014
I have a spreadsheet that I need to rearrange to improve readability. I have an example attache, which is easier to understand than describing it, but I'll try:
I want to start in a1, move to a2
copy a2 to b1
delete line 2 (the previous lrow 3 now becomes row 2
if a2 > 0.01, skip it and
move & select cell a3
... and repeat the above logic, on the next series of cells...
E.g.
copy a3 to b2
delete line 3 (the previous lrow 4 now becomes row 3
if a3 > 0.01, skip it and
move & select cell a4
...and repeat the sequence again.
View 2 Replies
View Related
May 23, 2006
I have a spreadsheet with a serial number is row 1 in column A with the rest of Row 1 empty. Column A is empty in Row 2, but has the data associated with the row above in columns B through L of Row 2. Then comes 2 blank rows and the pattern repeats with a new serial number in column A of row 5 and so on. I would like to move the serial number down one row, delete the now empty first row, delete the following 2 empty rows, and then loop to do the same thing again for all 9000 rows of the spreadsheet.
View 2 Replies
View Related
Aug 11, 2006
I have a list of names in sheet1, starting with cell A3. I would like to copy sheet3, insert it after sheet3 and rename the sheet to correspond to the names in the list.
If i have 30 names I would like 30 sheets. If i add a name, I would like to repeat the copy, insert and rename steps for the extra names as i add them.
View 7 Replies
View Related
Sep 28, 2007
I'm setting up a spreadsheet for work and i have a question. I'm not familiar with the formulas. If i want to set up a formula to where if i type "complete" in the cell it will move the entire row over to the next sheet.
View 2 Replies
View Related
Dec 5, 2012
I need write a VBA code that will move an entire row into a new sheet if the value of the last cell says "YES" If the value is blank then Id like it to stay in the current sheet. I would also like it if the cell wont allow any other value other than Yes to be typed.
I found this code here:
Copy, Move and Delete Row, based on Cell Value
THE VERY FIRST CODE REPLIED IN THAT THREAD.
I just cannot seem to get it to work. Simply copying and pasting that into the module doesn't work. I am not very good at VBA and just started taking a course on VBA. Id like to jump into this problem asap as I need it for work.
Basically:
Columns A:E will have values. In Column E I would like to type in Yes if completed. I would like the Macro to run through all of Column E and if the cell value is "YES" then I want it to cut the entire row and paste into a new sheet. In the new sheet I would like it to be pasted into the next available row.
Also can I create this with a ActiveX control button? I would like to have a button that I can click at the end of my work which will run the Macro in sheet 1.
View 6 Replies
View Related
Mar 2, 2013
I publish a green building mag, and we run an enquiry system for readers on our website. This is the link: [URL] .......
The idea is that readers tell us some info about themselves, including what types of products/services they'd like to receive information about, and we pass their details on to advertisers offering those products/services.
When someone fills out a form, we get data in Excel on their enquiry. It's all in one row - name, contact details, and then the products/services they've enquired about are included in one cell, separated by commas.
So for instance, suppose a listing currently came in as follows (column headings in brackets:
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation, solar panels, windows
Essentially I want it to run like this:
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) insulation
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) solar panels
(name) Joe Soap / (job title) Architect / (organisation) Joe Soap Architecture / (address) 123 Soap St, Soapville / (enquiries) windows
View 14 Replies
View Related
Feb 20, 2009
I would like a simple macro that would actually move a cell based on criteria. In my case it would be: In row A, if a cell starts with 'Agent Name' then that cell needs to move down one cell replacing the contents of that cell.
View 3 Replies
View Related
Sep 20, 2008
I have a simple projectlist on sheet1 formatted like this:
Column A=Projectname, B=Description, C=Owner, D=Deadline and E=Status.
On sheet2 I have the same columnheaders.
What I would like to do, is that if Status changes to "Done",
* the entire row should be copied to a free row on sheet2, or just inserted on sheet2.row2 without overwriting anything
* the now empty row on sheet1 should be deleted (so there're no empty rows)
* the data on sheet2, should be sorted with D as first key and C as second.
Then, if Status on sheet2 changes to "Reopened", the same procedure should happen but from sheet2 to sheet1.
My first choice was to create a checkbox on each row in the Status column,but I noticed it didn't seem to follow with cut/paste even though I changed the property Locked to false. Anyone knows if it's possible to change this behaviour?
I only know small bits of VBA but my idea is this: ...
View 4 Replies
View Related
Jul 16, 2005
I currently have a consolidated worksheet (thanks Bill!) called " Dashboard" that contains closed items that are marked by a validated column that can only contain "Closed, Open, or In-Progress." Is there a way to move the rows with a value of "Closed" to another worksheet called "Completed"? Also when this move is done, that row is no longer necessary in Dashboard and should be removed. So I'm guessing its a lot like a cut and paste and then a delete row/shift cells up?
here are some additional information:
The worksheet has a locked header that is 6 rows deep (the values for the "Status" column begins on row 7 and on.)
The "status" column is at column 11.
View 8 Replies
View Related
Jun 25, 2007
I would like to know if the following problem can be solved using only Excel without any operator intervention. Can a formula be created? If yes I would appreciate it very much
Problem:
If B5 = X
Move B6 thru K6 to B5 thru K5
And
Move B7 thru K7 to B6 thru K6
And than
Blank out B7 thru K7
View 3 Replies
View Related
Jan 15, 2008
I have a excel file to keep track of tasks or actions that need to be performed. What I am looking for, is an automated utility or code that will allow excel to automatically move entire rows (so an entire task) of completed tasks to another sheet called, "Completed Actions". In Actions sheet I have a column for " status" and here you have to select from a drop down menu, either "On-going", "Urgent" or "Done". What I would like, is that once you have selected "Done", the entire row or entry, will be automatically moved to the "Completed Actions" sheet.
View 7 Replies
View Related
Mar 8, 2007
i need to have 6 or so cells copied and pasted into another sheet. (when a button is pressed) However before the data is pasted it checks if there is anything in the cell(s) that are being pasted into, if there is something then move down a line and paste. Here is the code i have already:
Sub Order2Invoice()
Sheets("OrderDatabase").Select
Range("B65536").End(xlUp).Offset(1, 0).Select
With ActiveCell
.Value = Orderform!G5.Value
.Offset(0, 1) = Orderform!E10.Value
.Offset(0, 2) = Orderform!E11.Value
.Offset(0, 3) = Orderform!E12.Value
.Offset(0, 4) = Orderform!E13.Value
.Offset(0, 5) = Orderform!E15.Value
.Offset(0, 8) = Orderform!E15.Value
End With
Sheets("Invoice").Select
End Sub
View 4 Replies
View Related
Jun 9, 2014
Basically I want to copy and paste all data in a row if the value in a cell is equal to something
I am currently using the below formula
The Value in red = Set tgt = ThisWorkbook.Sheets(Criterion.Offset(, 1).Value 'Pick the cell next to the cell containing the criterion).
But my issue is now that i will need this to work based on 37 values and having this macro 37 times with the value changed and 37 buttons will be difficult.
So I am looking for a way i can add multiple values each going to separate sheets.
View 14 Replies
View Related
Jun 3, 2012
I have two sheets:
1. Not yet printed
2. Printed
My data is in sheet 1 (Not yet printed). I would like to move automatically entiry rows (sometimes more then one) to sheet 2 (Printed) based on one cell's value. Here is a screenshot:
For example when I enter into Sheet 2 '264450' then row 2 would have been moved to Sheet 2.
And also with multiple rows, if I enter '264461' then row 4 and row 6 would have been moved to sheet 2 as well. I wouldn't like to enter a value more than once.
View 1 Replies
View Related
Oct 30, 2013
Ok so Im working on Christmas functions and this is how my data base looks
Date
Name
Address
Seats
Price
Total
1/12/2013
James Fran
1 Bold street
4
£20
£80
[Code] ......
What I need to do is move all the dates onto their own worksheet, so every row containing 1/12/2013 would move to the worksheet 1.12.13, the 6/12/2013 would move to the worksheet 6.12.13 and so on.
Currently I have to copy and paste each row manually, which takes time. Any macro or formula which would do this automatically every time new data is entered. The main database, like above, will contain around 200 entries.
View 2 Replies
View Related
Jul 27, 2007
ABCDE
FGHIJK
If the above is in different cells, I want to be able to move the data in "G" to the cell beside "E" (up a row and over 4 columns) IF the cell to the left of "G" equals what I specify is in "F" (for example use "product"). I would want to apply this to an entire sheet, not just once. If the macro (not sure what its called) could also then delete the row that contained "G" after it moves it, that would be even better! The output in the example above would be:
ABCDEG
F HIJK
View 10 Replies
View Related
May 13, 2007
I am not so sure this can be done through a macro, but here is what I want.
I have a folder with say around 1000 excel workbooks. I need a macro to
1. Open each file in the directory specified
2. Read the contents of a particular cell, for ex, A1
3. Create a folder in the same directory with the value of A1
4. And, move the excel workbook itself to that folder.
5. Then it opens the next file
6. If the value of A1 is the same as that in the first workbook, it simply moves it to the already created folder, else it creates another folder with the new value of A1 and moves it there.
Hope I am not too confusing. Searching the forums does not give me an exact picture of how it can be done.
Note: I would also like an option where when I execute the macro, it should pop up with an option to select the folder in which the files are, since the files are not always saved in a static file path.
View 6 Replies
View Related
Nov 16, 2007
I'm trying to do is loop through a range of cells, and if the cell meets a condition I want to move it and 2 cells to the right of it left by one cell.
Sub MoveRangeOfCellsBasedOnCellCriteria()
Dim myrange, cell As Range
Set myrange = ActiveSheet.Range("H2", Range("H65536").End(xlUp))
For Each cell In myrange
If IsNumeric(Left(cell.Value, 1)) _
Or Left(cell.Value, 5) = "UNIT " _
Or Left(cell.Value, 4) = "THE " _
Or Left(cell.Value, 5) = "FLAT " Then
Else
cell.Range("A1:C1").Select
Selection.Cut
cell.Offset(0, -1).Range("A1").Select
ActiveSheet.Paste
cell.Offset(1, 1).Range("A1").Select
End If
Next cell
End Sub
View 7 Replies
View Related
Mar 16, 2008
I download our monthly bank transactions (.csv). I have a workbook with sheets named for each creditor or expense.
I would like move each row, based on the specific word in a cell row, to the specific worksheet.
Here are some typical transactions in the Description column:
Some are specific:
1/7/2008ACH WEB-SINGLE 9085863 VONAGE AMERICA VONAGE
“Moved to the Vonage worksheet.”
Others are not so obvious:
2/1/2008CHECK CARD PURCHASE XXXXX4636 BOSTON MARKET #0450 GREENSBURG PA
“Moved to the Eat Out worksheet.”
2/21/2008CHECK CARD PURCHASE XXXXX4636 ASPCAPS XXXXX0028 MD
“Moved to the ASPCAPS worksheet.”
2/19/2007POS PURCHASE POS54309901 0014264 PITTSBURGH ST GREENSBURG PA
“Moved to the Grocery worksheet.”
I was able to find the following while during a Search:
Move Cells Containing Specific Word In Column To New Sheet
The question was answered, in addition to Dave Hawley, by Bill Rockenbach who inserted the following code - “Sub FindWord()”
Option Explicit
Sub FindWord()
Dim Sentences
Dim Word As String
Dim i As Long
Dim iWordPos As Integer
Dim lRow As Long
Dim sWord As String
If this is a possibility for what I'm looking for, I’m not sure how to implement it into my situation.
View 6 Replies
View Related
May 1, 2008
I need to CUT & PASTE records according a cell value. For example:
Columns A2 to AC1000 there are some values. In each rows of AD2:AD1000 the values repeats with the result something like "YES", "NO", "N.A.", "LESS CHANCE". So, I need to shift (to another sheet) only the entire rows with values of "NO" and "N.A."
View 6 Replies
View Related
Dec 4, 2011
I have a row of dates in row 2 (all sequential, from Nov through to June next year)
I have a row of number entries in row 3 (to correspond with the date in row 2 it was entered on).
I want to be able to add a new entry (a number) into a text box, click a macro button, which makes the text box input be moved into the cell underneath todays date (so every day the cell will move one column along)
I've managed to make myself a textbox, and a macro button, but I'm stuck with how to make it all work.
View 5 Replies
View Related
Dec 8, 2011
did in [URL] but my issue is complicated by not knowing what cell I need to move the graph to.
I am using Excel 2010 and I am a realitive novice at writing VBA code.
In My spreadsheet, I have a list of properties and some related cost info. From week to week, the number of properties varies. Some days I will have 6 properties and others I could have 100. Because of this, I need to make all of my references relative to other cells. I am having trouble repositioning the graphs that I have created from the data to be 2 cells below the label I have created for the graph (which is a merged cell covering Columns B:I on a row 3 rows below the last property in the report.
So far, I have:
Dim r1 As Range, r2 As Range, GraphRange As Range
Cells(2, 1).Select
Selection.End(xlDown).Select
[Code]....
View 1 Replies
View Related
Mar 1, 2014
I am having rows of data, that i will be updating from time to time. I want excel to move the latest updated rows, in any column if updated, to move to the top, to easiy know that i updated those records. It should be that when i updated more rows than one, then the first updated cell would be in lower, in order, than the latest updated cells. I do not want any cumbersome vba. I want in formula or in conditional formatting. The row no may be total not limited to some rows.
Because, you naturally would have updated the 200 th record and would have saved. It saved as it is, so when you next opens it it is there, but how can i know that that is the last row of data i edited.
View 5 Replies
View Related
Jan 10, 2007
I have a sheet that holds my inventory and has 3 colums. The colums are serialnumber, techid and date. I also have a sheet for each techid. Is there a way to delete/move a row of data if the serialnumber is entered on another sheet.
example.
I have a serialnumber added to my first sheet. I go to a techid sheet and enter that same serial number. Is there a way to copy the entire row the same serialnumber is on and copy to the current sheet? Possibly a search function where I enter a serialnumber and it finds the same thing on the main sheet and copies the rows into the current sheet.
View 9 Replies
View Related
Sep 21, 2009
I am trying to create a macro the looks at column A and moves the entire row to another worksheet or deletes the entire row based off of the value in each cell. Right now I have the Macro replace the downloaded values to either AG, G, GI, ICP, IMG or delete. The idea be to move all of the values with AG in column A to the AG worksheet, move G to the G worksheet, etc.. and then delete all of those with delete in the cell.
View 14 Replies
View Related
Apr 22, 2006
I've included bits of my code and some debug output. Whenever I attempt to add a worksheet, either BEFORE or AFTER I get the following ERROR MESSAGE: Method 'Add' of object 'Sheets' Failed. if I use the .add without before and after a worksheet is added. The problem is that it is always added before the active sheet. I need to replace a single sheet in the correct position of possible 10 sheets. I know the names of the sheets and which one I want to replace, but I can't get EXCEL to move sheets, add sheets by position Number or name, without getting the ERROR.
Public xlApp As Excel.Application
Public xlBook As Excel.Workbook
Public xlSheet As Excel.Worksheet
Public xlRange As Excel.Range
.....
Set xlApp = CreateObject("Excel.Application")
Set xlBook = xlApp.Workbooks.Add
....
xlBook.Worksheets.Add After:="Accounts Receivable" *****
?xlsheet.Name
Accounts Receivable
?xlbook.Worksheets.Count
4
?xlbook.Worksheets(4).name
Accounts Receivable
View 2 Replies
View Related
Apr 12, 2009
I have a macro where I would like to delete the first three characters in cell D2 and then copy the remaining eigth characters down to cell D2000.
Column D Column D
55512315678 after macro 12315678
12315678
View 3 Replies
View Related
Dec 9, 2006
I have the attached spreadsheet set up using a vlookup funtion in D1. I need the macro to be able to find the actual row this number resides in and delete that row whenever E1 is equal to Delete.
View 5 Replies
View Related
