473,372 Members | 894 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

excel...Copy row based on word in cell

Hello and goodMorning,

Can anyone please help me with this. I have a database in excel that has 2 sheets full of data. I would like if i press a button to update then macro searches in Column O if the words " Req. Feed Set Up" then it get move over to sheet 2 in the workbook.

Sub Feed_set_up()

Dim intLastRow As Long
intLastRow = ActiveSheet.UsedRange.Rows.Count

'Let's start at row 2. Row 1 has headers
X = 4

'Start the loop
Do While X <= intLastRow
'Look for data with 'Feed set up'
If Cells(X, 15) = "Req. Feed Set Up" Then
'copy the row if it contains '
Worksheets("Sheet1").Rows(X).Copy
'Go to sheet2. Activate it. We want the data here
Worksheets("Sheet2").Activate
'Find the first empty row in sheet2
erow = Sheet2.Cells(Rows.Count, 15).End(xlUp).Offset(1, 0).Row
'Paste the data here
ActiveSheet.Paste Destination:=Worksheets("Sheet2").Rows(erow)
End If
'go to sheet1 again and actvate it
Worksheets("Sheet1").Activate
'Loop through the other rows with data
X = X + 1

Loop
End Sub

I have this code(above)which works great but Everytime i press the update button it copy and paste the rows with the words in Column O overwrites what is in sheet 2. In other words i doesnt not find the fist blank row to paste the data it always starts at row 2 can i get some help with changing this around....
Thanks
Dec 8 '11 #1
7 2951
Guido Geurs
767 Expert 512MB
Sandy,
You want to filter the rows with the text "Req. Feed Set Up" in col "O" (15) and transfer these records (rows) to sheet 2 ?

If so, this is the code:
Expand|Select|Wrap|Line Numbers
  1. Sub Feed_set_up()
  2. Dim LASTROW As Long
  3. Dim ROWidx As Integer
  4.     LASTROW = Range("A2").End(xlDown).Row
  5.     ' Start the loop
  6.     For ROWidx = 2 To LASTROW
  7.         ' Look for data with "Req. Feed Set Up"
  8.         Range("O" & ROWidx).Activate
  9.         If Range("O" & ROWidx).Value = "Req. Feed Set Up" Then
  10.             ' copy the row if it contains
  11.             Range("A" & ROWidx).Resize(, Range("A" & ROWidx).End(xlToRight).Column).Copy
  12.             Worksheets("Sheet2").Activate
  13.             If Range("A1") = "" Then
  14.                 Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  15.                         False, Transpose:=False
  16.             ElseIf Range("A2") = "" Then
  17.                 Range("A2").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  18.                         False, Transpose:=False
  19.             Else
  20.                 Range("A" & Range("A1").End(xlDown).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
  21.                         False, Transpose:=False
  22.             End If
  23.         End If
  24.         ' go to sheet1 again and actvate it
  25.         Worksheets("Sheet1").Activate
  26.     Next
  27. End Sub
PS: if it's to slow (to manny rows) use array's:
- Set sheet1 in an array and put the results in an other array.
- Dump the array with results in sheet2.
Attached Files
File Type: zip excel...copy row based on word in cell_v1.zip (10.1 KB, 64 views)
Dec 9 '11 #2
Thank Guido,
Thanks for the help on this Macro, This is what i was looking for but I would like to cut the Entire Row and paste it into sheet 2.

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Sub MoveYesToCompleted()
  3.     Dim ShPendingNextRow As Long
  4.     Dim ShPendingLastRow As Long
  5.     Dim ShCompletedBlankRow As Long
  6.  
  7.     ShPendingLastRow = Worksheets("Pending").Cells(Rows.Count, "A").End(xlUp).Row
  8.  
  9.     For ShPendingNextRow = ShPendingLastRow To 6 Step -1
  10.         If Worksheets("Pending").Cells(ShPendingNextRow, "G").Value = "Req. Feed Set Up" Then
  11.             ShCompletedBlankRow = Worksheets("Completed").Cells(Rows.Count, "A").End(xlUp).Row + 1
  12.             With Worksheets("Pending")
  13.                 .Rows(ShPendingNextRow).Cut Destination:=Worksheets("Completed").Range("A" & ShCompletedBlankRow)
  14.                 .Rows(ShPendingNextRow).EntireRow.Delete
  15.             End With
  16.         End If
  17.     Next
  18. End Sub
I found this code on another fourm and it works great on a dummy file but then when i try to move it over to my real file it does not work.
I think its because the other file is to large I might need to change it into array and filter then dump results into sheet2 (just like you said) and i would like if this is possible to search on 2 sheet in my really file called sheet1 and sheet2 the dump results in in "Active" worksheet. Thanks Guido!!!! your the best...
Dec 9 '11 #3
Hey Guido,
I got it to work on my real file but for some reason it dosent grab all of them??? i dont know why
Dec 9 '11 #4
Guido Geurs
767 Expert 512MB
Attached is a standard tool to search rows.
Attached Files
File Type: zip EXCEL...copy row based on word in cell_1.11.zip (208.0 KB, 96 views)
Dec 14 '11 #5
Wow Guido thanks for this it looks like so much I’m a little intimidated to use this. How do I transfer the tool into the worksheet that I going to use this in. what I do is that is just copy and paste everything into the correct workbook, I just would like to know if there is an easier way to do it.
Thanks for your help...
Dec 16 '11 #6
Guido Geurs
767 Expert 512MB
No need to copy each time the code to the workbooks!
Just open the tool workbook with the form and open also the workbook in which you want to use the form.
Run in the second workbook the macro from the tool workbook with =
Start the User Form with the macro=
'UF Copy Rows.xls'!Start_Copy_Rows.
Dec 16 '11 #7
Ohh okay thanks. I guess i was doing it the really long and dum way thanks guido...
Dec 16 '11 #8

Sign in to post your reply or Sign up for a free account.

Similar topics

0
by: MrMike | last post by:
I have an asp.net datagrid hyperlink column that displays the word "Picture". Is it possible to NOT show the word "Picture" if the data in the column is NULL? To read the column data I'm currently...
4
by: Lisa | last post by:
Hi - I'm able to open excel workbooks and word documents, but I can't seem to copy excel charts, named ranges, etc. to a word document. Anyone know of good reference material in this area? What...
0
by: Kay | last post by:
Hi all, I want to copy an entire row from a worksheet to another worksheet, when I set the excel app = visible and step thru the code I can actally see a row is appended to another worksheet,...
4
by: stocki | last post by:
Hi Everybody, one of my C# apps copies charts from an Excel workbook into a table in a word document. ((Excel.ChartObject) myGraphicsWorksheet.ChartObjects(1)).Copy(); myvalue =...
4
by: Jono | last post by:
Hello, I have 40 option boxes in a list, beside information in cells (in excel). I want to be able to select a control (ie Control27) based on information in the cell beside it (would be cell N27...
0
by: pbenetedwin | last post by:
I want to display the data in Excel in Ms Word .
0
by: AkaShade | last post by:
I have an Excel 2007 worksheet that I enter customer data into and from that a portion of the data is entered into a contract in Word 2007. Currently I have linked data from the Excel worksheet to...
5
by: dave816 | last post by:
Sorry for the Excel question in an Access forum...................I don't see an Excel forum and there's probably a reason for that but figured I'd give this a shot anyway. Again sorry, delete if...
0
by: qwerty9988 | last post by:
I have a datagrid that lists documents: Document Name, User Name, Date. When a user clicks on the row of the document it opens in word, excel, etc. The datagrid also has a delete command column. ...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.