Find,Copy, Paste Multiple Rows

[see3po81]

I am new to VBA and have been trying to create a macro that will allow me to find where certain information is located in one worksheet, then paste other information to those corresponding rows in another worksheet.

So far I have been able to find where the information is located using .find and .findnext

I can copy the information I want from the second worksheet and select it to be pasted. I can not paste the multiple rows though. If I do it manually and type in which row I would like the info to go to it works, but I can't get it to work with the rest of my program on its own.

Below is the relevant code that I have been working on, all help is welcome. I don't know much so please explain if you have any other better ideas.

Dim rngthis As Range
Dim rngfind As Range
Dim firstaddress As String
Dim addSelection As String

'set range to all of Col A
Set rngthis = Sheet1.Columns("A")

With rngthis
'find all cells that have "item" in them
Set rngfind = Sheet1.Cells.Find(What:="Item", _ After:=[A1], SearchOrder:=xlByRows, _ SearchDirection:=xlPrevious)

'store in addselection
If Not rngfind Is Nothing Then
firstaddress = rngfind.Address
addSelection = addSelection & rngfind.Address & ","

Do
Set rngfind = .FindNext(rngfind)
addSelection = addSelection & rngfind.Address & ","

Loop While Not rngfind Is Nothing And_ rngfind.Address <> firstaddress

End If
End With

addSelection = Mid(addSelection, 1, Len(addSelection) - 1)
Sheet1.Range(addSelection).Rows.Select
Set rngthis = Nothing

'Call different worksheet and copy cells needed
Sheets("Yearly Capital Repl. Schd. ").Select
ActiveSheet.Range("A9:AE10").Select
Selection.Copy

'paste copied cells into found rows from above
ActiveSheet.Range(addSelection).Select
ActiveSheet.Paste

 

[shiguera]

see3po81,

what error(s) are you getting?
One thing; the addSelection assignment just before the Do Loop causes a value to be repeated. Isn't that a problem?

 

[see3po81]

Yes, one of the values is repeated like that.

I pieced together some code I found from examples online and wrote my own to get it to do what I wanted it too do.

The code works fine up until the last part. It finds the specific rows correctly, selects the correct rows to be copied but then it stops. I dont get any errors back. All that is the subroutine finshes, and excel still has the copied rows waiting to be pasted selected.

My guess is that because "addselection" is a string it is getting confused where to place the copied rows.

For example, when I run the subroutine, "addselection" comes back as "42,20,9,42".

Thnak you for replying shiguera

 

[shiguera]

To be clear, when you say: "42,20,9,42" you mean $A$42,$A$20, etc.? that's what I get.

But anyway, the addSelection comes back with a last comma, which will not be recognized in the Range(addSelection) statement. Also, you are copying a range of contiguous cells and trying to paste this into non-contiguous cells... that will not go well.

Hope this helps.

 

[see3po81]

Sorry for the confusion, but I was messing around with the code and changed .Address to .Row, that is why I got just the rows and not the cells address.

Anyway, I see what you mean about the comma at the end, I didn't notice that. Even when I override the code and just plug in :

'paste copied cells into found rows from above
ActiveSheet.Range("42","20","9").Select
ActiveSheet.Paste

It still won't work, I know that I am writing something wrong here, I just dont know enough about VBA to know what I am doing wrong.

Would it be better to separate the string "addselection" into separate variables and then paste each one individualy?

If so what is the best way to do that.

Any other thought of how to tackle this problem would help me out greatly.