473,700 Members | 2,699 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

copy row based on critiera

88 New Member
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.Use dRange.Rows.Cou nt

'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("She et1").Rows(X).C opy
'Go to sheet2. Activate it. We want the data here
Worksheets("She et2").Activate
'Find the first empty row in sheet2
erow = Sheet2.Cells(Ro ws.Count, 15).End(xlUp).O ffset(1, 0).Row
'Paste the data here
ActiveSheet.Pas te Destination:=Wo rksheets("Sheet 2").Rows(ero w)
End If
'go to sheet1 again and actvate it
Worksheets("She et1").Activate
'Loop through the other rows with data
X = X + 1

Loop
End Sub

I have this code(above)whic h 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 1616
Killer42
8,435 Recognized Expert Expert
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

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

Similar topics

42
5783
by: Edward Diener | last post by:
Coming from the C++ world I can not understand the reason why copy constructors are not used in the .NET framework. A copy constructor creates an object from a copy of another object of the same kind. It sounds simple but evidently .NET has difficulty with this concept for some reason. I do understand that .NET objects are created on the GC heap but that doesn't mean that they couldn't be copied from another object of the same kind when...
3
8360
by: Tlm | last post by:
Hello All, I have a form (FrmA) with a subform (SubFrmB) embedded in it. SubFrmB also has a subform embedded in it (SubFrmC) The form's recordsource is based on a table (TblA). SubFrmB's recordsource is also based on a table (TblB). SubFrmC's recordsource is also based on a table (TblC). There is a one-to-many relationship between TblA (one) and TblB (many).
4
17190
by: Peter | last post by:
I want to copy a parent class instance's all datas to a child's. It's actually a C++'s copy constructor. But why the following code does not work - there is a compile error! How it should look like? (The background is I don't know (I don't care indeed) all members in DataGrid, so I don't want to copy all members in DataGrid one by one.) public class GridEx : DataGrid { public GridEx()
1
1493
by: Julia | last post by:
Hi, I am looking for a way with which I can selectively copy properties of an object. in some circumstances I need to copy all properties and in the other I need to copy only some of them(always the same) I am thinking to use attribute if the property should be copy
0
4696
by: Peter | last post by:
I am having a problem reading an Excel file that is XML based. The directory I am reading contains Excel files that can be of two types. Either generic Microsoft based or XML based. I am reading the Microsoft based files with an OleDbDataAdapter. Then filling the contents of the first worksheet into a dataset. However when I try to add the XML based file to my dataset using an XmlTextReader I can never seem to get it to save to a...
2
14691
by: michael sorens | last post by:
I tried to do a simple operation on a Windows Form in VS2005 inside a key_down handler: if (e.Control && e.Shift && e.KeyCode == Keys.V) { int selectedRowIndex = dataGridView.SelectedCells.RowIndex; dataGridView.Rows.AddCopy(selectedRowIndex); } So when the user presses Ctrl-Shft-V, a copy of the first row of a user's
2
3754
by: -Lost | last post by:
Alright, am I missing something? I create a 2D array like so: var blah = ; blah = ; blah = ; Then I *attempt* to create an independent copy based on all of the pages I have read that said it was as so:
7
1528
by: jangchoe | last post by:
I've heard that JavaScript is a prototype-based language instead of a class based one. I'm interested in learning the prototype-based paradigm of OO programming, but I am unsure of the best way to use JavaScript as a prototype-based language. I'm stuck between two ways and both methods might be right (or wrong). Method 1: /* create an Animal "object" */ function Animal() {} ;
6
3929
by: Peng Yu | last post by:
Hi, I'm wondering if the following assignment is lazy copy or not? Thanks, Peng std::vector<intv. v.push_back(1); v.push_back(2);
2
2832
by: banderson | last post by:
Hello Bytes, I am having trouble copying a value from an open record in a recordset into a form. I have a form frmOutreachReferral that is filled in after a Site Visit has been performed and it is determined that the site needs a follow up visit. A Site Visit is entered into frmSV and is based on tblOutreachAdmin with its primary key (autonumber) field called OutreachID. If a referral is needed, you click on a button to open...
0
8722
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8643
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9069
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
8968
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7805
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5900
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4401
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4654
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2025
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.