Find And Move VBA Macro
Jun 1, 2006
find and move VBA macro - funky when WhatToFind in more than one row
Making a program that will find a value input by the user, and move the row of the cell that value is found in to the top of the list.
Works perfectly if there is only one row containing the search string. If more than one row contains the search string, it moves things to odd places.
here's the
Sub FindAndMoveToTop()
Dim FirstCell As Range
Dim NextCell As Range
Dim WhatToFind As Variant
'Window prompt allowing user to define WhatToFind
WhatToFind = Application.InputBox("What are you looking for?", "Search", , 100, 100, , , 2)
'If WhatToFind is a value and not blank, move on
If WhatToFind <> "" And Not WhatToFind = False Then
'Start with first worksheet
Worksheets("Sheet1").Activate
'Start at first cell
Range("A1").Select
'Find the first cell containing WhatToFind (specified by user)
Set FirstCell = Cells.Find(What:=WhatToFind, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
'If FirstCell exists, move on
If Not FirstCell Is Nothing Then
'Ok, First Cell is set
FirstCell.Activate
'Keep going...
View 4 Replies
ADVERTISEMENT
Aug 11, 2009
I regularly receive a file with a column called ‘Project Key’ which for some unknown reason can be located anywhere in the worksheet i.e. in any column from A: to BZ and beyond.
What I would like to have is a macro that finds the column ‘Project Key’ and moves it from it’s current position and moves it directly after a column call ‘XXX’ and delete the blank column where it orginally was. There would be no need to check if the columns exceed Excels maximum as my file never gets that big
View 3 Replies
View Related
May 9, 2006
way to find all the cells in range A1:A200 that partly contain the string "FindThisWord", and then cut & paste each of those cells 1 row above and 2 columns to the right?
View 8 Replies
View Related
Feb 17, 2014
I have a file with many values, distributed across multiple columns.
From sheet1 i want to find and move all the duplicate values in the sheet 2 and I need a macro,a code macro to work at the level of the sheets, Sheet1-Sheet2
I want to move cut/paste all values duplicates 2 times 3 times or how many times is found from sheet1, in sheet2,but in sheet1 to keep single values, in sheet 1 to remain single value, only the values themselves which didn't pair I mean if a value is 2 times to move the original value but and double found.
View 7 Replies
View Related
Apr 13, 2014
I'm attempting to FIND all occurrences of the word Godaddy embedded in strings in Col A, and if found, copy the contents of Col B to Col E.
1. The VBA is amending on the Loop While line, and
2. What syntax do I use to copy the contents
HTML Code:Â
Sub Macro3()
'
' Macro3 Macro
'
'
With Worksheets(1).Range("a1:a10")
[Code] .....
View 4 Replies
View Related
Feb 18, 2014
Code macro, is to move the duplicate values in sheet2,
I want to change that results from sheet 2 to be placed on columns A and B, not as it is now spread all over the sheet 2
This is the code....
View 3 Replies
View Related
Mar 19, 2008
I have two different lists that have some similar text and some irrelevant text. What I need to do is move the matching words from column B to the same row in column a and leave blank the rows that have no similar words. Column A will not move ...
View 7 Replies
View Related
Dec 30, 2011
I have a worksheet with data in it. The data could be in any column or row. I am looking for data which contains the numbers 01. If I find such data, I want to move all the data in that particular cell it to column A.
My code(which is not working) is below. I'm using Excel 2003.
Code:
Sub delete_oldads()
'the code to find 01
Dim cel As Range
[Code].....
View 2 Replies
View Related
Oct 21, 2013
Identical Data exists on Sheets1 and 2 (A3:V50)
A3:A50 = human names
B3:V50 = numeric values
I'm trying to VBA code:
If human names on Sheet2(A3:A5) match human names on Sheet1(A3:A5)
Move Sheet2 matching name row(s) to the bottom of the data range on Sheet2.
Sheet1
Sam 4 4 4 4 4 4 4 4 4 36 4 4 4 4 4 4 4 4 4 36 72
Bill 4 4 4 5 4 4 4 5 4 38 5 5 4 4 4 4 4 4 4 38 76
Bob 5 5 5 4 4 4 5 5 5 42 4 4 4 4 4 4 4 4 4 36 78
[Code] ......
View 3 Replies
View Related
May 29, 2014
I have attached sample files as reference. I am using excel 2010
I wish to move all duplicates from one sheet (Orders) to another(Duplicates).
Sheet0 is the original sheet(Orders)
Sheet1 is the orders sheet once the duplicates have been removed
Sheet2 is the duplicates sheet
Sheet3 I am also looking to achieve what is in sheet3 so that the quantity and stock code is all in one row as shown in the example sheet3
Basically I am looking for it to finish up like sheet1 and sheet3
Link to files: [URL] ........
View 3 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
Sep 27, 2007
I want to set range a entire column,the rows are dynamic and i want to do it on all the columns in the sheet, my problem is that after i set the range(i hope i did good) i want to do a average of the range and to put the result in a new sheet on a row. I'm sorry for the mess in the code its one of my first macros
Option Explicit
Sub Average()
Dim R As Integer
Dim help As String
Dim Range1 As Range
Dim E As Integer
Dim Avg As Integer
R = 1
'/////CREATE NEW SHEET AND COPY THE FIRST TWO ROWS TO THE NEW ONE
Sheets("UXP data").Select
'Sheets.Add
Sheets("UXP data").Select......................
View 2 Replies
View Related
Oct 3, 2012
I have a macro which refreshes a query when the spreadsheet is opened. This works fine when online.
However, if the user is not online, the query is unable to refresh and the macro just hangs.
Is there a code which will enable me to say " if unable to refresh then move on to the next line"?
here's the code below.
Private Sub Workbook_Open()
Sheets("Houselist").Activate
Selection.QueryTable.Refresh BackgroundQuery:=False
Sheets("Front").Select
Range("A1").Select
End Sub
View 2 Replies
View Related
Apr 4, 2014
I have a work book with 6 worksheets, sheet2 is called Maersk, sheet3 Mariana, and so on ....
I need a macro so when I type Mariana in sheet1 g20 and hit enter it go's to Mariana sheet .....
View 5 Replies
View Related
Mar 26, 2014
I have a macro that is auto opening a form to be completed, but it currently opens on the first row of data that has been inputted.
How do I get the macro to move to a new row immediately to stop people from over-typing the first row of data?
View 5 Replies
View Related
May 14, 2009
I need a macro that I can assign to a button so that it will jump the screen to a particular cell. I want it to select the cell in a chosen column in the same row as the cursor already was. So, if I'm in M10 and I hit the AK button, it'll jump to AK10. If I'm in F54 and I hit the AK button it'll jump to AK54.
View 2 Replies
View Related
Oct 14, 2009
I am trying to create a macro to do the following (Microsoft Excel 2002 SP3):
1 - Select the text from the active/selected cell (i.e. B1)
2 - Cut the text from B1
3 - Move to the cell directly above the active/selected cell (i.e. A1)
4 - Add a space to the end of whatever text is in A1
5 - Paste the contents that were cut from B1 after the text and space in A1
6 - Delete the row where the text was cut from (i.e. entire row B)
I have never written code for macro's before, only done recording, but just can't seem to get this sequence to work .
View 8 Replies
View Related
Feb 22, 2010
I've used the macro recorder to copy a cut and paste routine, but it won't play back correctly. Here's the macro I recorded:
View 7 Replies
View Related
Nov 2, 2011
I need to cut 2 rows when i put "V" in E column, and then to paste them below row that have in column A, "OLD".
Ex:
I have many rows with data like this:
.......A..........B.........C.......D......E
1...subject
2...notes
3...subject............................."V"
4...notes
5...subject
6...notes
...
25..."old" row...............................
I need to cut the row with "V" in column E and the below row, and paste them below row with "old" in A column, like this:
.......A..........B.........C.......D......E
1...subject
2...notes
3...subject
4...notes
...
23..."old" row...............................
24...subject............................."V"
25...notes
View 9 Replies
View Related
Dec 19, 2013
I've been using the record macro function to accomplish a lot of my formatting tasks for my work reports.
I'm running into a snag that I'm not sure how to fix. Essentially I want the program to recognized the term "Grand Total" and the cell to the right of it. Then I want those two cells to be moved to the far left.
The # of rows will continuously change but he column shouldn't change.
I've tried naming the cell selection to have it moved but I can't seem to make it work.
When I use the record macro function and do a CTRL F search for "grand total", excel doesn't seem to recognize that I want that cell and the cell to the right to be the ones to move. Instead, whatever row of cells I create the macro with, it wants to duplicate the move with the same exact cell.
View 2 Replies
View Related
May 14, 2008
I have a piece of my macro that creates a chart on the second sheet in my workbook.
However, it tends to appear way off to the right, and then I have to readjust it every time to be where I'm working (columns A-D approx.)
Can I improve the move to specify it to place the graph in a certain
All I'm currently doing is:
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet2"
View 9 Replies
View Related
Jul 23, 2008
I have about 80 sheets in a workbook, and I would like to put each sheet into it's own workbook, and have that new workbook called by the value in Column A.
View 9 Replies
View Related
Aug 6, 2008
I have a workbook with 52 worksheets.
6 are hidden, 46 are visible.
I am trying to move a worksheet to the end of the workbook and rename as the next sheet.
It works the first time I run it (it renames the worksheet to Client51, the new last worksheet) but after that it gives me a 1004 error 'cannot rename a sheet to the same name as another sheet...."
Here is the macro I am using:
Sub rename_client()
Dim NewName As String
ActiveSheet.Move Before:=Sheets(Sheets.Count)
NewName = "Client" & Sheets.Count - 2
ActiveSheet.Name = NewName
End Sub
View 9 Replies
View Related
Sep 16, 2008
I have a workbook with two worksheets in it. The first "TO DO" and the second "Completed". In "TO DO", I have rows of tasks starting in row 4 (row 3 is my header), going to 200. What I'd like to have happen is when I put a "C" in column C, it moves the entire row to the "Completed" worksheet, greys it out and removes it from the "TO DO" worksheet. Then if I remove the "C" from the "Completed" worksheet, it moves it back to the bottom of the list. I already have a macro to resort it based on "priority" in column E.
View 9 Replies
View Related
Mar 13, 2009
I started a project where I want to export data from Access (only 2 columns but # of rows vary every day). When the data that is copied to the first tab is a week old and is a Tuesday I want to copy and paste these values in the next tab.
View 9 Replies
View Related
Jul 24, 2014
Macro, which can cut/Paste from sheet "Pastin" to Sheet "Robot".
The input in sheet Pastin is veritical and the the result is in a row horiontsal.
There can be no references between the names for the columns or rows, only the cell to move.
I would like it to be so when I run the macro it example move it to sheet robot from row A3, and if I do again it make the next one from Row A4.
What I want to move can be seen in below.
Cell on the left is sheet "Pastein" and cells on the right is sheet "Robot"
Special is that one move have to be taken from on cell comma separated input, so it goes to sheet "Robot" for each text/number, between the comma.
I have attached a test sheet to work with also.
[Code] ...
Testmove.xls‎
View 6 Replies
View Related
Feb 25, 2014
I'm looking for a macro to select data from a larger data set, and then put it in another worksheet. Basically, I want to select any cell in column A that contains "SO5" and move those to the column A of a separate worksheet. Then, for each cell that contains "SO5" would like to move to the right 5 columns and down 3 rows and select from that cell all the way to the end of the table. There may or may not be data in all of the table cells, so the selection should be based off of the column headers which will always have values. I would like to take that data and put it with the corresponding "SO5" in the new sheet.
Here's a sample:
MacroSample.xlsm
This is a simplified version, but I'm trying to get the data from sheet1 into the format in sheet2. Keep in mind that the number of columns and rows may change so it needs to be as dynamic as possible.
View 1 Replies
View Related
Mar 4, 2009
I have been trying to do this for 3 days using "Record Macro", but something always seems to be messed up. The main problem is that the worksheets are protected and the macro asks for the password.
Here is a calendar sheet that I've been working on. It contains the calendar on sheet 1(not important) and a client roster on sheet 2. The client roster gets filled quickly, and obviously gets unorganized. What I'd like to do is create a macro so that when a date is entered into column AA (when a client is discharged), that entire row is moved onto sheet 3 (Discharged). At the same time, I'd like all of the names in column A alphabetized.
I even tried to create an "Organize" button so that the users could just click it and alphabetize. The problem I ran into with that was that the users are using Office 2000, and the names get alphabetized but all of their information no longer matches up.
View 3 Replies
View Related
Mar 30, 2009
Below is my the macro that I recorded. I want to copy and paste the same information to a new location everytime. The macro tells it to go to cell IV1, end left, then I want it to go right one cell regardles of the cell location. Here it tells it to got to ("K1"). It will do this everytime and I do not want it to go to K1, just move right one time and then paste the information.
View 3 Replies
View Related
Jun 2, 2009
I am just getting started and have over 1000 items in the list. I want to be able to run a macro that would take the root portion of the image link and then replace the 'imagename.jpg' with 'modelnumber.jpg'
So start with
Column 1 www.photobucket.com/a/aa/a/imagename.jpg
Column 2 LAT-NR460
and end up with
Column 1 www.photobucket.com/a/aa/a/LAT-NR460.jpg
Column 2 LAT-NR460
Column 1 could start empty or just with the root.
View 9 Replies
View Related