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 am working on the attached price proposal and need my user to be able to enter a number into column E indicating that that row should be printed on the quote. The 'quote' can be new sheet or another area of current sheet that is formatted to look like letterhead.
I am in need of restructuring a spreadsheet of addresses for mailing purposes. (I tried to find something similar answered previously, but nothing seemed to work for me.)
I have attached a small example spreadsheet below, but our spreadsheets can be hundreds or thousands of rows in length.
I need a macro that will look for the "PO Box" addresses under the column headers named "ADDRESS2" and "ADDRESS3".
The PO Box addresses will need to be moved under the column header "ADDRESS1" within the same row.
It will need to overwrite the text that is already under "ADDRESS1" and delete the text from the "ADDRESS2" and "ADDRESS3" columns - UNLESS the text in 'ADDRESS2" is a PO Box AND "ADDRESS1" begins with "c/o".
If the data in "ADDRESS2" or "ADDRESS3" is anything other than a PO Box it will remain the same. As will "ADDRESS 1".
Basically if there is a PO Box it needs to be in the column named "ADDRESS1" and overwrite anything else that was there. The exception will be for PO Boxes that are in c/o someone else, the PO Box will then need to be listed in the column directly after the column that has c/o.
If the c/o exception will be too difficult the code could just highlight those scenarios and we could fix them manually. We usually do not have a lot of them, but enough that we need to be mindful of them.
The different scenarios are listed in my sample spreadsheet.Also, the code will have to use the column header names in row 1 because those headers are not always in the same column.
I have some specific procucts in coloum A in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
Sub SearchForString()
Dim LSearchRow As Integer Dim LCopyToRow As Integer
I have some specific procucts in coloum A(a2, a3, a4, till a50) in sheet (data), If that product was found in Sheets("allproducts"), copy all the rows and create a new sheet on the product name and save the excel.
I want to delete any/all rows from a worksheet (named UK) which have the word "VAN" in column P. I have tried using a 'For Each... Next statement' in a macro, but always get a debug box "Run Time Error '91' Object variable or With block variable not set". What am I doing wrong? my code follows
Sheets("UK").Select Dim cell As Range For Each cell In Worksheets("UK").Range("P10:P200").Cells Cells. Find(What:="VAN", _ After:=ActiveCell, _ LookIn:=xlFormulas, _ LookAt:=xlPart, _ SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, _ MatchCase:=False, _ SearchFormat:=False).Activate Selection.EntireRow.Delete Next
I have a macro that converts all my PDF Purchase Orders to a text file and inserts the data/text horizontally into another document. However because the PDF's or the text within the PDF can be fomatted differently (that is on different lines etc) it therefore imports the information and it looks mis-aligned.I have attached a simple spreadsheet showing some sample text as it is imported and then below this how it should look like, all in line.
The range where the highlighted text in red is, is variable (but say nothing more than a variant of 10 columns). The text can also be Uppercase or Lowercase.So, I was wondering if there is macro code to find the "text" on various rows/columns and align it all in another column?
I have a spreadsheet and I was wondering if there is a way to copy from a specific range in say A1:A5 in Sheet1 and paste into Sheet2 where the column matches cell A1 in Sheet1.
I am trying to do this in VBA and I was wondering if there is a way to do this.
I have a large document with a lot of names listed. I want to make up new sheets for each person listed. Is there a formula that I can enter that will move the entire row based on the person's name? See attached.
Currently i hav list of info that is long like this.
A 1 B 2 C 3 D 4 E 5 F 6 G 7 H 8 I 9 J 10
How to move row to column example below. Because i want the data to be printed on an a4 paper which can fit around 3 row to save space . If i manually copy paste page by page it is too much.
A 1 D 4 G 7 B 2 E 5 H 8 C 3 F 6 I 9
===
Currently i have macro code which roughly does this.
A 1 B2 C3 D4 E5 F6 G7 H8 I9 J10
Sub test() Dim rng As Range, m As Integer, c As Range Columns("c:E").Delete m = 2 Set rng = Range(Range("a1"), Range("a1").End(xlDown)) Set c = Range("a1") Do While c <> "" 'MsgBox c.Address
Range(c, c.Offset(m - 1, 0)).Copy Cells(Rows.Count, "c").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True Set c = c.Offset(m, 0) Loop End Sub
I would like to have a VBA macro that would look over my entire spreadsheet and hide the individual row if any cell in Column A contains the text "xyzzy". The "xyzzy" can be anywhere in the cell....beginning, middle or end.
How to do what I am about to ask takes time and time causing brain overloading. I have made screenshots but I can't upload them. I get red exclamation point with images sizing ~55KB
I get an excel sheet with W/C Employer: (whatever) entered below the patient it belongs to in column E. This information needs to move into its own column (H) in line with the above patient information so I can use it in a mail merge to word.
The same thing needs to happen with WC injury date listed in column G. It moves to column I. Then delete the row it was moved from so I don't have an empty row.
Sheet 1 is what I get. Sheet 2 is what I need to end up with.
Currently I am using the Kickbutt VBA Find Function of Aaron, but I would like to have something that works more efficiently. What I currently do is (assuming all possible values for Column J are A - F):
although I just want some code that says: delete all rows except those that have "F" as content in Column J. I already tried something like:
Range("1:65536").Select For Each cl In Range("J:J") If cl.Text = "A" Or cl.Text = "B" Or cl.Text = "C" Or cl.Text = "D" Or cl.Text = "E" Then Rows(cl.Row).Delete End If Next
but it also takes much to long. The major problem I think, is that the number of records is variable so I search the entire worksheet...
I have a set of data that has a store number in cell A1, the store name in B1 and then the store address in C1, C2, and C3. This pattern repeats for all ~300 stores. I am trying to get all of the store data on one row per store.
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
If the row contains a text like ABC in multiple columns in a sheet it has to copy the whole column from that sheet and paste special into another sheet.
I am trying to move a column of numbers based on the information in another column. I've been looking for about a week and find macros that are close but not quite.
In one column it reads Mobile, Home, or is blank. If the number is a Mobile (column R), the area code (column P) needs to move to column S and the phone (column Q) needs to move to column T and the primary phone (column R) needs to move to column U. Home and blank cells remain as they are.
area (P) phone (Q) primary phone (R) col S col T col U
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.
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.)
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 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.
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 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 want to create 3 command buttons (active X) on a worksheet to toggle between showing rows which only contain the below text in column L (range L9:L30) and showing all rows containg the options (However, I also have some blank rows in this range and i always want them to remain hidden.)
My text options are:
High Ť‚ Medium ’†“™ Low ’á
The text arrives in the cells via a VLOOKUP
Is the chinese text a problem? i can't type it into VB.
I've been using the following macro to hide and unhide rows with a command button in the same sheet:
Private Sub CommandButton1_Click() Toggle_Hide_Unhide End Sub
Sub Toggle_Hide_Unhide() Dim rngCell As Range Dim TakeAction As Boolean
If ActiveSheet.CommandButton1.Caption = "Hide" Then TakeAction = True ActiveSheet.CommandButton1.Caption = "UnHide" Else TakeAction = False ActiveSheet.CommandButton1.Caption = "Hide" End If
For Each rngCell In ActiveSheet.Range("I9:I30") With rngCell If .Value = 2 Then .EntireRow.Hidden = TakeAction End With Next rngCell End Sub
We have a client that provided us with a text file that we imported into Excel. Rather than have the entire record in one row, they have the record in 4 rows.
What I need a macro or something to do is move award 2 award 3 and award 4 into the same row and after doing this delete the other rows.
Below is an idea how this looks. 1JohnSmithstreetcitystateZIPaward 1 2JohnSmithstreetcitystateZIPaward 2 3JohnSmithstreetcitystateZIPaward 3 4JohnSmithstreetcitystateZIPaward 4 5BillJonesstreet2city2state2ZIP2award 1 6BillJonesstreet2city2state2ZIP2award 2 7BillJonesstreet2city2state2ZIP2award 3 8BillJonesstreet2city2state2ZIP2award 4
What we need for our data to work is: 1JohnSmithstreetcitystateZIPaward 1award 2award 3award 4 2BillJonesstreet2city2state2ZIP2award 1award 2award 3award 4
