469,295 Members | 1,978 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,295 developers. It's quick & easy.

copy row based on critiera

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 6 '11 #1
1 1517
Killer42
8,435 Expert 8TB
I've found one good way to find how to code something in an Excel macro is to tell Excel to record a new macro, then do the action yourself.

For example, in this case if you want to go to the first empty row, I'd suggest something along these lines...
  • Start recording
  • Click the icon to set addresses to relative (I think)
  • Hit Ctrl-Home to go to the very start of the sheet
  • Hit End then Down to jump to the last non-blank
  • Hit Down to move to the first (hopefully) empty row.
  • Stop recording, then go have a look at what was recorded.
Of course, this is based on some assumptions, such as that the leftmost column is always populated. But it should help to get you started.
Dec 18 '11 #2

Post your reply

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

Similar topics

42 posts views Thread by Edward Diener | last post: by
4 posts views Thread by Peter | last post: by
2 posts views Thread by michael sorens | last post: by
7 posts views Thread by jangchoe | last post: by
6 posts views Thread by Peng Yu | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.