Moving Rows To Another Worksheet Based On Text In A Cell
Nov 30, 2012
I would like to make a macro that will move rows from one worksheet to another worksheet.
I have an excel spreadsheet with multiple columns and rows, within one of the columns there is some text that refers to SOS or County, I need to move everything that has SOS to another worksheet title UCC SOS and everything that has county to be moved to another worksheet title UCC County. Here is a sampling of the worksheet
Account
Primary Borrower
Corp
Status
[Code]....
View 7 Replies
ADVERTISEMENT
Feb 11, 2009
I have an excel workbook with 5 sheets, the first called 'data drop sheet' & the others called 'Louise', 'Thor', 'Peter' & 'Steve'. In the data drop sheet, the first row is header data and underneath (A2 onwards) are rows of data that I have cut & pasted in. The first column (A) of this data will be the person's name, e.g. Louise Wilson.
I want to be able to move the rows for each person to their respective sheets, starting from row 2 as the first is a header. So, for example, if 'data drop sheet' cell A2 says Steve Brady, I want it to copy the whole line to sheet 'Steve' row 2 etc.
View 5 Replies
View Related
Jan 28, 2014
I need to build a macro which copies 3 rows every day and pastes the row data into an identical sheet. The three rows will have column "D" as =today(). As the days progress the three rows will change accordingly ( tag to the today's date)
e.g. 28/1/2014
28/1/2014
28/1/2014
I need the macro to recognize the date when pressed and copy the corresponding rows of data and paste them into an identical sheet with the same date. The second sheet is an archive sheet. The date will tick over as per the calendar.
View 9 Replies
View Related
Nov 20, 2008
I have data as follows:
id | name
0 | axe
1 | sword
3 | pencil
4 | fist
5 | hammer
7 | gun
9 | rifle
Is it possible to move the rows to the row# shown in the ID?
- e.g. pencil gets moved from row#2 to row#3
View 9 Replies
View Related
Oct 3, 2012
I am using Excel 2003.
I have 2 worksheets.
Worksheet 1 is called "Master List Data". Every cell within this worksheet contains a formula so that it matches the cell value contained in a worksheet held within another workbook.
The formula for reference is as follows:
=IF('[CCL Breakdown.xls]MASTER LIST - Active Customers '!A1="","",'[WFX CCL Breakdown.xls]MASTER LIST - Active Customers '!A1) .
e.g.
If Cell A1 on Master List-Active Customer contains no data, Cell A1 in Master List Data would be blank.
If Cell A1 on Master List-Active Customer equals John Smith, Cell A1 in Master List Data would display John Smith.
There are currently non-blank values contained in cells A4:A750. But next week there may be non-blank values is cells A4:A790 (i.e. it will grow each week)
Worksheet 2 is called "Master List Flat. At the moment, I am manually copying and pasting the rows which have non-blank values in cells from Column A from Worksheet 1 into this report (e.g. A4:IV4).
I would like to automate this process and I have created a Macro, but I do not know how to word it so that it will only copy cells with actual data in.
The Macro I have written is below:
Sub IMPORTANDFLATTENDATA()
'
' IMPORTANDFLATTENDATA Macro
' Macro recorded 01/10/2012 by walesb
'
'
Application.ScreenUpdating = False
Sheets("Master List Flat").Select
Rows("4:759").Select
[Code]....
View 1 Replies
View Related
Jun 17, 2014
I am a production manager for a homebuilder and I am trying to create an Excel spreadsheet that will allow me to input a date in a column and have a second column dependant on the date and move as I change the date.
Since no work is done on weekends, I'd like the output column to skip weekends and move the input to the next available weekday. I have conditionally formatted the date to be a red fill as a visual cue but I'm not sure where to go from here.
View 7 Replies
View Related
Jan 30, 2013
I was wondering if i can cut & paste rows to a different worksheet ("sheet2") based on a criteria my current loop is as follow need filling in the code below for if then ..
Sub moving()
Dim x As Integer
x = 0
Do While x < 12
x = x + 1
Cells(x, 4) = ""
Do While Cells(x, 4) = "absent"
If Cells(x, 4) = "absent" Then .....
View 9 Replies
View Related
Sep 23, 2013
I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.
View 5 Replies
View Related
Aug 10, 2014
I am making a workbook for our 4H horse shows. I want to be able to list the kids on the first page and check off (x in the cell) which classes they will be entering and then have the program move their info to each of the specific class worksheets where we will record the results. I'd like to move rows A thru E to each applicable class sheet. I've attached the workbook.
View 6 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
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
Oct 28, 2009
The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.
Problem:
What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.
If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.
I have found the following
Sub
If Target.Value = 1 Then
Rows(Target.Row).RowHeight=0
Else
End If
End Sub
Which I have changed to:
Sub
If F4.Value = Green Then
Rows("5,6,7,8,9,10.Row).RowHeight=0
Else
End If
End Sub
So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?
View 9 Replies
View Related
Nov 21, 2011
the following situation:
I have several worksheets in a file with worksheet 1 containing all the raw data I am working with.
In worksheet 3, I have hundreds of rows containing different texts in column C. Let's call this texttolook4.
I want search for texttolook4 (all rows) in worksheet 1 (column D) and copy the worksheet 1 row if found in a new worksheet.
View 1 Replies
View Related
Aug 6, 2014
I have a spreadsheet where if Cell A2 says "Deposit", Rows A2-E2 would be formatted with a Blue background.
I've tried conditional formatting, but I can't built it into a Macro (I can't make it run), and it only highlights the cell that has that value. I also tried having the spreadsheet filter down to only the values I want highlighted, then highlighting all cells and un-filtering the column- this didn't work either.
It would basically say :
If (any cell in Row A) has text = "Deposit" (it needs to be exact, it can't be "containing"), set cell with the word "Deposit" and 4 cells to the right as Blue (I'm not picky about the color).
View 10 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
Jul 2, 2014
I'm trying to compare the values of columns B and G and if any of the cells are G < B, I would like to move the whole row into the second worksheet. And only if possible, it would be awesome if the macro could check column A for duplicates before the value comparison and sum up the column B if there are duplicates. In my example row 6 and 7 on column A have the same suppliers code and the amount totals to 2.
In this case for example, the value of the third row of G is lower than B, and therefore I would like the whole row 3 to be cut out from worksheet 1 and moved to worksheet 2.
Original view of sheet 1:
SUPPLIERSCODE
ORDERAMOUNT
PRODUCTNAME
BARCODE
SUPPLIERSCODE
WAREHOUSE STOCK
And then cut out the rows in which G
View 9 Replies
View Related
Jun 16, 2008
Will excel allow text to be permanently be positioned in a specific cell even if rows or columns are added?
View 6 Replies
View Related
Apr 27, 2009
Can I have text moving in a cell similar to a news flash.
View 3 Replies
View Related
Jan 14, 2013
I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.
Sub Share_Sales3()
Dim Prompt As String
Dim Caption As String
[Code].....
View 3 Replies
View Related
Oct 14, 2008
I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.
View 2 Replies
View Related
Mar 7, 2014
I have a cell that has text added to the cell using a function. I need to move the cell to a different location, but without the text.
View 1 Replies
View Related
Apr 9, 2014
Each month I get a report that is formatted by the customers and this sheet never changes. The problem is that the master sheet my job uses is not formatted this way. The master workbook has several sheets that breaks down the data from the monthly sheet. Instead of having to enter the data manually I'm looking for a way to export the data from the monthly sheets into the master workbook. As I stated the sheets are all the same but come from different providers and all contain the same amount/style of data.
View 1 Replies
View Related
Apr 16, 2014
removing duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
View 5 Replies
View Related
May 3, 2013
coding a VBA macro for one of my workbooks, in which I need to be able to hide/unhide various rows in one worksheet depending on the value of a cell in a worksheet elsewhere in the workbook. The rows start off hidden by default.
As a simplified example:
Worksheet1 has a cell that has option "Set 1," and "Set 2." Worksheet2 has two sets of rows (say, 20:30 and 40:50) that need to be hidden/unhidden depending on the cell in Worksheet 1. These are hidden to start with!
So if Worksheet1's target cell says "Set 1", then on Worksheet2, rows 20:30 would stay hidden and rows 40:50 would be revealed, and then if the target cell says "Set 2," then on Worksheet 2, rows 40:50 would then be hidden, but rows 20:30 would then be revealed.
I was thinking of using something like this:
Rows("20:30,40:50").EntireRow.Hidden = True
If Target.Address="'Worksheet1'!A1" Then
If Target.Value = "Set 1" Then
Rows("40:50").EntireRow.Hidden = False
Else
Rows("20:30").EntireRow.Hidden = False
End If
End If
I think this might work, but every time I try to run this I get various errors, like not referencing my target cell correctly.
View 5 Replies
View Related
Jan 28, 2010
I'm trying to create a macro to merge multiple rows into one cell and display in a new worksheet.
This seems really simple but I've tried to re-work some other examples I've found online but none seem to do exactly what I need. I'm also pretty new to VBA , so it's highly possible i've missed something.
I need to display each set of Notes for each DonorNo in one row - with each note separated by a space.
I've attached a sample of the data and what I need for the output. In the actually file I have around 70,000 records so the prospect manually merging the rows is horrifying.
View 12 Replies
View Related
Jun 10, 2013
I'm trying to create a .cvs file from an exported excel report, but before I can import it to a database I'd like to have one row per record.
I've already omitted out the headers and footers blank spaces and unnecessary columns from the excel file and I needed to just combine multiple rows into one.
It currently looks like this:
ROW#(1), ID#(1), NAME(1)
ADDRESS(1), ZIP(1)
DOB(1), SOURCE(1)
ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2)
ADDRESS(2), ZIP(2)
DOB(2), SOURCE(2)
ITEM(2), CITY(2)
and so on..
I'd like to make it like
ROW#(1), ID#(1), NAME(1), ADDRESS(1), ZIP(1), DOB(1), SOURCE(1), ITEM(1), CITY(1)
ROW#(2), ID#(2), NAME(2), ADDRESS(2), ZIP(2), DOB(2), SOURCE(2), ITEM(2), CITY(2)
and so on...
I can't figure out a macro or a way to do this automatically.
View 8 Replies
View Related
Sep 18, 2009
I have created a worksheet which contains seven columns of data and many rows. I need to copy entire rows into nine separate worksheets depending on the data in column G.
I know nothing about VBA but have read enough to realize this is the way to go with this. Would someone point me in the right direction on how to set this up? I've attached the file so you can better see what I'm talking about.
View 5 Replies
View Related
Apr 30, 2012
I have a worksheet "Feature Segments" that has a value in cell B40 and C40, if the value in these is "Off (Default)" then I need rows 22 and 23 on Worksheet "Summary" to hide.
I have put this code on the features segment tab but it is not working.
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.ScreenUpdating = False
If Target.Address = "B40" Then
[Code].....
View 3 Replies
View Related
Apr 26, 2006
Is it possible to move a worksheet from a workbook to an add-in.. just can't seem to do it for some reason. As a last-ditch attempt, I tried to convert the add-in to a workbook, but I can't seem to do that either
View 4 Replies
View Related
Mar 7, 2014
Say I have two worksheets, "Sheet1" and "Sheet2". Let's also say A1 in Sheet1 could have one of four values: cat, dog, rabbit, mouse. Now, based on which value cell A1 on Sheet1 is, I need different rows hidden in Sheet 2. For argument sake let's say if cat is chosen rows 1-5 are hidden, if dog, 6-10 and so on.
View 5 Replies
View Related
