Search and paste a random amount of rows

[tony_d]

I get sent a large spreadsheet each week my problem is I only need a two bits of it. What ideally I would like is through VB be able to serach for the info and place this in sheet 2.

The added complication is that it may be three lines or up to 20 lines each section is named so I would search for 'Parcels' in column E then select the data in the following rows until the is a clear line then copy this into sheet 2. And do the same for my next piece of data named 'Changes' again in column E

Is this:-

A) Possible

B) Practical

Any hints or help would be welcome thanks.

 

[Peter_SSs]

tony_d

1. Is there information in Sheet1 that is NOT being copied to Sheet2?
2. For the 'Parcels' and 'Changes' in column E, is it only column E that is being copied to Sheet2? .. or is associated data in other columns (eg column F) also to be copied?
3. Where in Sheet2 is the 'Parcels' data to be pasted?
4. Where in Sheet2 is the 'Changes' data to be pasted?

 

[tony_d]

1) Yes there is a lot of information in sheet 1 I do not need.
2) It is not just column E in my last sheet the information I needed was Parcels A50 - V68 & Changes A224 - V235
3)A1
4) Underneath the other data

 

[Richard Schollar]

Hi Tony

Give the following a try:

Sub GetData()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim strArray As Variant, i As Integer, j As Long
Dim rng1 As Range, rng2 As Range, rng3 As Range

Set ws1 = ActiveWorkbook.Sheets("Sheet1")
Set ws2 = ActiveWorkbook.Sheets("Sheet2")

strArray = VBA.Array("Parcels", "Changes")
j = 1

For i = 0 To UBound(strArray)
    Set rng1 = ws1.Columns("E").Find(what:=strArray(i), LookIn:=xlValues)
    Set rng2 = rng1.End(xlDown)
    Set rng3 = ws1.Range("A" & rng1.Row & ":V" & rng2.Row)
    ws2.Cells(j, 1).Value = strarray(i) & " range occupied " & ws1.Name & "!" & rng3.Address
    rng3.Copy ws2.Cells(j + 1, 1)
    j = j + rng3.Rows.Count + 1
    Set rng1 = Nothing
    Set rng2 = Nothing
    Set rng3 = Nothing
Next i
End Sub

When run, it should copy over the contents of A:V for rows under Parcels and Changes in column E that contain values. It also places a message in sheet2 telling you which cells it has copied over from sheet1 so, hopefully, any errors will be immediately obvious.

There is one very obvious reason why this could go wrong - it will copy over all rows under Parcels for example where there has been no break in the data in column E. If a column E cell is empty, but there is still data in the A, B or C etc column that you wanted, this will be missed. You will have to post back if this is the case.

This can be placed in your personal macro workbook and then run once you are in the excel file that you get sent by going Tools>Macro>Macros and selecting from the list.

Please do post back if you need further help.

Best regards

Richard


PS Peter, apologies for diving in here - I felt inspired!

 

[tony_d]

It looks the dog's thanks ever so.

 

[Peter_SSs]

PS Peter, apologies for diving in here

No problem Richard. VBA is not my strong point anyway so it would have been a battle for me to come up with the rights sort of code. I really just asked the questions because I figured the answers would be needed by whoever was going to provide an answer.

- I felt inspired!

You seem to have been inspired a lot lately - firing on all cylinders