Find and copy

I then need to offset 2 rows and then copy every row from the active row down until the first blank row.

So lets say  Issues-Rates is in B45

offset 2 to 47

first blank is 52

so I need to copy rows from 47 to 52


Appreciate an Expert providing the VBA please.
JagwarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
Range(ActiveCell.Offset(2, 0).Address & ":" & Range("B1048576").End(xlUp).Address).Copy
0
Martin LissOlder than dirtCommented:
That copies range B47:B51. Is that what you want or do you want to copy rows 47 to 51?
0
Martin LissOlder than dirtCommented:
This copies rows 47 to 51 to row 52

Rows(ActiveCell.Offset(2, 0).Row & ":" & Range("B1048576").End(xlUp).Row).Copy Destination:=Rows(Range("B1048576").End(xlUp).Row + 1 & ":1048576")
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

JagwarmanAuthor Commented:
My copy paste missed out the first row of my question so it should have said

I need to find Issues-Rates in the active sheet

I then need to offset 2 rows and then copy every row from the active row down until the first blank row.

 So lets say  Issues-Rates is in B45

 offset 2 to 47

 first blank is 52

 so I need to copy rows from 47 to 52

If  Issues-Rates is in B145

 offset 2 to 147

 first blank is 252

 so I need to copy rows from 147 to 252

sorry about that
0
Martin LissOlder than dirtCommented:
Is the word "Issues-Rates" on the sheet?
0
Martin LissOlder than dirtCommented:
Do you mean you want to copy rows 47: 51 to row 52? Or do you mean you want to copy rows 47:52 to someplace else? If the latter, where do you want them put?
0
JagwarmanAuthor Commented:
Issues-Rates is in the sheet somewhere could be anywhere when I find it I want to offset two rows and then copy entire row[s] from there to next blank row

Regards
0
Martin LissOlder than dirtCommented:
I'm not sure if you want a blank row before the copied data. This code does not leave a blank row.

Sub CopyRows()
Dim rngIR As Range
Dim lngLastRow As Long

With ActiveSheet
    Set rngIR = .UsedRange.Find(What:="Issues-Rates", LookIn:=xlValues)
    If Not rngIR Is Nothing Then
        lngLastRow = .Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        .Rows(rngIR.Offset(2, 0).Row & ":" & lngLastRow).Copy Destination:=.Rows(lngLastRow + 1)
    Else
        MsgBox "Couldn't find 'Issues-Rates'"
        Exit Sub
    End If
End With
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JagwarmanAuthor Commented:
hanks Martin that's what I was looking for.
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.