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."
I have four sheets with the same size tables and just need rows to be able to be moved back and forth based on input from a certain cell and just added to the next available blank cell in the desired table. Adding the code from the webpage below works very well but only puts into the worksheet and not into the actual table. Is there any way making it actually input the row into the table itself?
Creating Macro that automatically moves row to another spreadsheet?
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
If UCase(Target.Value) = "COMPLETE" Then
Target.EntireRow.Copy Destination:=Sheets("Archive"). _
Range("A" & Rows.Count).End(xlUp).Offset(1)
Target.EntireRow.Delete
End If
End If
End Sub
I can export inventory data from my Point of Sale system that looks like this:
I need to manipulate it to look like this:
To do so I think I need a macro(?) to:
Copy and insert as many rows as the quantity in Column C. In case of >1 the row is deleted.
This would allow me to print labels for every item in my inventory.
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.
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 RelatedI 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
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.
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.
I'm trying to put together a dashboard that is referencing a table. Now the dash board I would like to have it so I can change a value and the details will update based off of my selection. This is all drived off of named ranges and have that working. The part I don't have working is dynamically changing what part of the table is being referenced to.
i.e. Say i have modeling quality I have a formula that looks like this
=SMALL(IF(Table1[[#All],[Modeling Quality]]=$D$12,ROW(Table1[[#All],[Modeling Quality]])),ROW($M1))
Which works perfectly. However if I change modeling quality to say modeling experience which has different criteria i need the formula to look like this
=SMALL(IF(Table1[[#All],[Modeling Experience]]=$D$12,ROW(Table1[[#All],[Modeling Experience]])),ROW($M1))
Is there a way I can drive the table reference from a cell selection? I've tried referencing them by manually typing in the cell address to no avail.
(refer to attached workbook)
For what I'd like to do, the attached workbook actually represents 2 or more workbooks. The 'Project-PO Report' tab is a sample of what will be several workbooks/reports that will be generated from the data in 'POTASK'.
The 'CMD_BUTTON' tab represents how I plan to use a command button " IMPORT DATA" to import the data into this workbook, then use "RUN PROJECT-PO-REPORT" to call the MACRO to achieve my objective,as stated below. (Originally,this workbook was about 8mb, so to get this under the size for attachment, I just copied the bare minimum of what I believe would be needed to assist me, and so there's no buttons or macros or dynamic name ranges, as there will be...)
Here's what I want to do: ...
"Is there a way to make values in a cell that are comma delimited (ex. cat, dog, fish) became separate labels in a pivot table instead of getting labels like 'cat, dog', 'fish, cat', 'cat, dog, fish' which represents the exact value in the cells.
The only real option I can think of would be to make an extra rows with the same data for each pet type. For example if the pets were 'cat, dog', there would be two almost identical rows except one would have 'cat' and the other 'dog'."
I have made a vba setup which can solve the problem. But it is pretty complicated and unstable. I need a solution which is not vba based. ~Or a least a very simple code!
Cell a1:a3000 contains a lot of trackingnumbers. Cell b1:b3000 I have a "IF" formulas in place to write "ok" if part of the data in "column A" matches my criteria. What i need is a macro that will cut all the rows showing "OK" in column B from "sheet1" to the next availible row in "sheet2"
I have tried converting copy macros to Cut macros, but thye have not worked. I am rather hopeless for the coding part and mostly make macros based on the record feature. Also looked at the ones that have been showed here, but my skills do not make me able to change them to fit my excel sheet. About 8000 rows with data is added into this sheet everyday and the criteria is not always present. That means on some days there will be no "OK" showing in column B
Is there any way to hide a row if columns 6 and 7 both show either a "-" or a number lower than 0.5? Cells in both rows contain a VLOOKUP formula.
View 9 Replies View RelatedI need to hide rows on a worksheet based on entries from DATA VALIDATION dropdown lists on another. Due to the fact that all the entries are on a different page, and there is no active cells or entries made on the "second" worksheet, the WORKSHEET_CHANGE events will NOT work. Trust me, I have spent two days searching the net for a possible answer and trying every trick I could find. Not to seem redundant or a pain, but right from the git-go, FORGET the WORKSHEET_CHANGE event as an option. I need a way to fire the macros from a formula change in a linked cell, and it won't be an active cell, nor will there be any kind of entry made on that page at all. ALl the entries (choices) will be made on another sheet, but the result needs to be hiding the rows not required based on the choices I make. There are over 300 lines and when I am done there will be only approximately 35-40 left. Right now the people using this are actually making copies of the workbook, and then deleting the worksheets they don't need (I am hiding those with an array) and then deleting the lines they don't need on the worksheet remaining. (Stupid and slow I know, but I didn't develop this workbook, I'm just trying to bring it out of the stone age) Here is some sample code with what I want to do, except it is of a WORKSHEET_CHANGE nature and will not work.
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Range("INPUT!M6"), Target) Is Nothing Then
Rows("11:50").Hidden = True
If LCase(Range("INPUT!M6").Value) = "100" Then Rows("31:50").Hidden = True
If LCase(Range("INPUT!M6").Value) = "100" Then Rows("11:30").Hidden = False
If LCase(Range("INPUT!M6").Value) = "225" Then Rows("11:30").Hidden = True
If LCase(Range("INPUT!M6").Value) = "225" Then Rows("31:50").Hidden = False
If LCase(Range("INPUT!M6").Value) = "0" Then Rows("11:50").Hidden = False
End If
End Sub
I have a number of rows that I want to have duplicated X number of times (and altered) where X is found by looking at certain cells within each row.
There are four numbers in each row, and I want to split them up into multiple rows each with three zeros and one one.
I would like to convert data from this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 3 0 1 0
Mike 0 1 1 2
etc.
...to this:
Name W X Y Z
John 1 0 0 0
Doug 0 0 1 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 1 0 0 0
Karl 0 0 1 0
Mike 0 1 0 0
Mike 0 0 1 0
Mike 0 0 0 1
Mike 0 0 0 1
etc.
You can see that the W, X, Y, and Z columns from the four new Mike rows sum to equal the values in the original Mike row (0, 1, 1, 2), but everything has been split so that each row just has a single one in it and three zeros.
Does anyone have an idea of how to do this? Thanks Auto Merged Post Until 24 Hrs Passes;I thought of another way of putting it that may be easier to understand.
Given an input row of "George, 4, 7, 3, 2", I would like the output to contain 4 rows of "George, 1, 0, 0, 0", 7 rows of "George, 0, 1, 0, 0", 3 rows of "George, 0, 0, 1, 0", and 2 rows of "George, 0, 0, 0, 1".
I want to do is copy all rows from the worksheet DATA based on column G (Date) and copy it to a new sheet based on the date (all 2003 on the 2003 sheet and all 2004 on 2004 sheet.....).
I have already created the new sheets including headers minus data. I would like to have all the data moved except the last to Columns AW & AX. I have two hidden sheets in this workbook. Would it be possible to have it auto-populate future entries from the "DATA" worksheet to autofill onto the new sheets?
Just noticed that I titled the Thread with Move but what I am asking for is copy. I cannot change the Thread title.
At the moment I have 1 spreadsheet with 12 worksheets that I have to enter data into. I would rather enter all the data onto one sheet then have a macro move the data into the correct worksheet based on the contents of columns A and B.
The raw data will be entered into the worksheet "Data". Once complete, I would like the user to press a button and the macro to then identify from column A the suppliers name. If it is not one of the recognised suppliers, then it would move the row into the worksheet "one off". If it does recognise the supplier then it checks column B to see which of the two supplier's two worksheets it needs to copy it to, with the data entered into the correct column based on the column title (ie only the white columns).
I have a sheet with 45,000 rows. Let's say each row has 4 columns: Create_timestamp, Update_timestamp, email_address, and o_flag
Many rows have duplicate email addresses. I would like to remove all the duplicate rows, EXCEPT for the row with the most recent Update_timestamp.
And actually, if I could just "hide" all those rows, that would be even better, but I'd be happy just figuring out how to delete all the "old" rows, so I just have a list of unique email addresses, with their create/update timestamps and o_flag column. Seems like this is such a basic use case for "Remove Duplicates,",.
I've been trying to put together some code to hide a range of rows on one page based on cell values on a different page. So basically Sheet1 cell A1 value is 10, A2 value is 20. When macro is run via button rows on Sheet2 from 10-20 would be hidden. The issue is the values in the cells on Sheet1 will vary. I thought something like the following would work but it doesn't.
[Code] ....
Column B contains geographical Areas. Column C contains a list of business departments.
North Env
North Env
North Ops
North Sales
North Sales
North Sales
South Env
South Maint
South Ops
South Sales
South Sales
South Sales
Etc.
The values and number of these departments will vary. I want to insert lines to sum the totals at the bottom of each geographic area based on the number of different departments. So, for this example, for the North three lines would be inserted. For the South, 4 lines would be inserted.
I am trying to hide rows based on values in a cell in that row. I have formulas in column xea that yields a 0 or 1 and I want to hide all rows with 0.
The code below works but I have to run the macro. I want it to happen automatically when the cell values change and I have not been able to figure it out.
Sub HideRows()
Application.ScreenUpdating = False
Application.Calculation = xlManual
For Each c In Range("xea1:xea350")
[Code] ........
I have a list of 130 names in column A. I have a number value between 0-10 in column B (next to the name). I need to insert the number of rows defined by the value in column B, below each row that I already have (if the value is 0, then the row needs to be deleted). The inserted rows have to be filled with the name value from the row above.
For example - before macro:
Joe Bloggs 2
Adam Wilson 10
Peter Andrews 0
Claire Burrows 6
After macro:
Joe Bloggs
Joe Bloggs
Adam Wilson
Adam Wilson
Adam Wilson
Adam Wilson....................................
I'm trying to hone a quoting tool to generate an amount based on a given range and minimum charges for specified subsects of the range. If you reference the attachment I'm trying to calculate a charge in cell K15 based on values in B9 and C10 referencing the table I8:R9. What I cant figure out is how to create a minimum charge of 50 for J8:P9 (points A-G) and a charge of 95 for Q8:R9 (points H-I).
View 2 Replies View RelatedI am trying to have rows moved from (Schedule) to (Complete) in the attached workbook. The criteria to have the row moved would be the user-selected "Status" list changing to "COMPLETE" in Column G.
I would like to have the row cut and deleted from Sheet4 (Schedule) once "COMPLETE" is selected from the list in Column G with rows below moving up. The row would then be inserted into Sheet2 (Complete) at the top of the list (Row 7) and rows would move down below the new entry. I would also like to have the formatting remain uniform (every other row fill)
I need to create extra rows of duplicate data. The number of duplicate rows depends on the number of semi-colons in the 15th column (column O).
Sheet 1 is the original working file. Sheet 2 is the result of how the executed macro for the first 2 lines of data. The first line in sheet 1 has 7 semi-colons, so there should be 7 rows of duplicate data created in sheet 2. The second line in sheet 1 has 6 semi-colons, so 6 duplicate lines, etc.
I have a excel file which contains dublicate rows. The duplicate rows can be identified based on few cell/column values. I need a macro to delete the duplicate rows when the below condition is satisfied: let us consider row 5 and row 6:
If column 7,12,13,16,17,18,19,23,24,27,28,29,30 in row 5 = row 6 then row 6 has to be deleted. This condition has to be followed for all other rows in the excel used range. Have attached the sample workbook.
I have a set of data in column R, with an unknown number of rows that looks like this
Days Late
-28
150
3
16
41
.
.
.
and I have written a script to add an adjacent column "S". I want to fill column S with conditional values based on the value of column R, sorted into categories such as "On Time", "Less than One Month Late", etc. Here is what I have so far, it doesn't work:
a macro to insert rows based on certain cell values in column A.
I have uto 300 rows of data. Below is an example of column A.
R1
1
2
3
4
5
6
7
[Code]...
If (above the R) is an 8, I need to insert 2 rows above that R and directly below the 8.
If (above the R) is a 9, I need to insert 1 row above that R below, directly below the 9.
(Below the R there is always a minimum of 8 digits with the 9 and 10 being random).
I have excel 2007
I got the following format of data which I need to copy the cell values to the next row if it's found.
Columnl1
Row 1 00185
Row 2
Row 4
Row 5 00123
Row 6
what i want is to copy from row1 to row2 & row5 to row6.
How am I going to code it in macro ?
One of the worksheets contains all of the data and the rest are empty. I need to do is move entire rows of from the main worksheet in to worksheets named the same as the value in column C of the row.
for example one row may have TEST in column C so i want to move it to the worksheet called TEST. The next row might have TESTING in column C so that would go to the worksheet called TESTING. and so on.