copying rows, w/o knowing row number

thexfactor44

New Member
Joined
Feb 21, 2002
Messages
26
I am using FIND in a macro to find any occurance of "name" in Column C of Sheet1. If FIND locates the given "name", I want to copy that row and paste it in the first available row of the neighboring sheet, in this case Sheet2. In this application, the first available row is Row 5 on Sheet2.

This is the main function of the macro. the rest could be called repetition, as I would like to be able to FIND all occurances of "name" on sheet1 and copy the entire row into Sheet2, but the next line to be pasted would obviously have to be Row 6 of Sheet2 as to not overwrite.

I have tried, and I do not know how to select the row N, where N is the number in Column C that "name" was found, and I am also having issues getting XL to paste starting with Row 5 on Sheet 2 and incrimenting. Also, FIND only seems to find it once, how do I get it to repeat throughout?

Thanks to all on this one, I know it is long, trying to be descriptive.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
You can use

Set Rng = Cells.Find("Name", etc., etc.)

Rng.EntireRow.Copy Sheet2.Range("A65536").End(xlUp).Offset(1).EntireRow
 

thexfactor44

New Member
Joined
Feb 21, 2002
Messages
26
While that seems to work for finding one occurance, it doesn't seem to find all occurances. Also, how can I incriment the offset each time a match is found so that it pastes every match?

Thanks for the help, think we're almost there.
 

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
The .Find example in the VB Help is very good, you can use that as the "loop" guide you need. And complement that example with the code I gave you.
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232

ADVERTISEMENT

How about:

Sub Test()
For Count = 1 To 100 'Assuming 100 rows of data
Count = Count + 1
Range("C" & Count).Select 'Assumes data to check is in ColumnC
'USE YOUR CODE FOR SEARCHING AND PASTING HERE
Next Count
End Sub

You can adjust the column you're searching and the number of rows to check accordingly. This example looks at selects cell C1 and then does the search/copy/paste you want, then goes onto C2 and does the same, then C3, then C4... up to 100.
 

thexfactor44

New Member
Joined
Feb 21, 2002
Messages
26
Okay, so I have tried to do this so far:
Sub Test()
For Count = 1 To 100 'Assuming 100 rows of data
Count = Count + 1
Range("C" & Count).Select 'Assumes data to check is in ColumnC

'inserting from pablo's post:
Set Rng = Cells.Find("Name")
Rng.EntireRow.Copy Sheet2.Range("A65536").End(xlUp).Offset(1).EntireRow

Next Count
End Sub

I have rally tried to do this on my own using a while loop and FindNext, and I have been using the help. If somehow we can get this to Find the Next occurance of "Name" that would be helpful for I really dont know how to use FindNext, I have tried, but I get lost when they talk about Ranges and Objects..I really have no clue.

I can incriment the offset with like:
n = n + 1 and do ti that way, so that problem is fixed. Just moving to the next occurance seems to be a problem.
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232

ADVERTISEMENT

I was just reviewing this... why even use the "find" function at all?

Since the code from my previous post looks at each cell, why not use the IF function:

Sub Test()
For Count = 1 To 100 'Assuming 100 rows of data
Count = Count + 1
Range("C" & Count).Select 'Assumes data to check is in ColumnC
If ActiveCell.R1C1="name" Then
'Add Your Copy Row and Paste Row on 2nd Sheet Here
End If
Next Count
End Sub

So this will look at Cell C1 and if it equals "name" then it will copy the row and paste to the other sheet, then will look at Cell C2 and do the same, etc, etc, etc, until it gets to 100. If the cell contains "name" then it will copy/paste, if it doesn't contain "name" then it does nothing and goes to the next cell.

Let me know how that works out, if it doesn't, post the code you do have and i'll try to work it
This message was edited by thereuare on 2002-09-05 16:29
 

thexfactor44

New Member
Joined
Feb 21, 2002
Messages
26
Okay, I appreciate your help, and I have 1 more issue:

I am getting a runtime error '438' saying:
"Object doesn't support this property or method"
The line in the code that is higlighted during debug is:
If ActiveCell.R1C1 = "pablo" Then

(I used "pablo" in place of name in my trial)

The code I have currently is as follows:
Sub Test()


For Count = 1 To 100 'Assuming 100 rows of data
Count = Count + 1
Range("C" & Count).Select 'Assumes data to check is in ColumnC
If ActiveCell.R1C1 = "pablo" Then
Rng.EntireRow.Copy Sheet2.Range("A65536").End(xlUp).Offset(1).EntireRow
End If
Next Count

End SubSub Test()


For Count = 1 To 100 'Assuming 100 rows of data
Count = Count + 1
Range("C" & Count).Select 'Assumes data to check is in ColumnC
If ActiveCell.R1C1 = "pablo" Then
Rng.EntireRow.Copy Sheet2.Range("A65536").End(xlUp).Offset(1).EntireRow
End If
Next Count

End Sub

Any ideas?

Thanks. BTW, your code is quite smaller than mine...Mine kept blowing up and I would have to kill XL (a few times) due to unending While loops.

I really suck at this. so thanks.
 

thereuare

Board Regular
Joined
May 28, 2002
Messages
232
Glad to see you're active and paying attention (meaning that you're on the board and immediately pick up posts that pertain to your question). Since my last post, i figured i'd make it easy and went off to try and do this on my own. Here is code that works for your purpose, as i understand it:

Sub Test()
Count = 0
PasteRow = 0
For Count = 1 To 100 'Assuming 100 rows of data
Range("C" & Count).Select 'Assumes data to check is in ColumnC
If Range("C" & Count) = "name" Then
PasteRow = PasteRow + 1
Row = ActiveCell.Row
Rows(Row).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A" & PasteRow).Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End If
Next Count
End Sub

There was a mistake or two i noticed in the previous code i sent you, but i'll point it out in another post, as well as address the question you asked above in another post, but i figured i'd post the above code first so you can get started on it.

Let me know how if it works as u need it to.
This message was edited by thereuare on 2002-09-05 17:13
 

Forum statistics

Threads
1,143,702
Messages
5,720,375
Members
422,281
Latest member
jantoooh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top