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 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.)
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.
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
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...)
"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
I 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.
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.
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
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 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
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).
I 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:
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.