472,129 Members | 1,862 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

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

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 '
'Go to sheet2. Activate it. We want the data here
'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
'Loop through the other rows with data
X = X + 1

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....
Dec 6 '11 #1
1 1555
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

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.