Search criterias and list related row information in different sheets

[Nevec90]

Hello forum,

I hope somebody can help me with this, because I will be definitely not able to realize that myself.

Excel Workbook:
http://ge.tt/api/1/files/5L5GIo52/0/blob?download
(Exceeded the 1 MB limit of the forum, therefore hosted like this)

I wrote a bit pseudocode for the problem (under it the description):

While nonblanc in (Input!A)
___for all (ShopName) in (Input!A)
______for all '30Days' in (Input!K)
_________for all 's' in (Input!B)
____________(ShopName!A6) (Whole content of current row)=Input!A (Whole content of current row)
____________(ShopName!A6)+1
____________else: for all 'b' in (Input!B)
_______________(ShopName!A6) (Whole content of current row)=Input!A (Whole content of current row)
_______________(ShopName!A6)+1
...
This continues till all Day ranges and all shops are done, which are in the input sheet.
____________________________________________________________________________________

Now in plain English:
In the Input sheet will be information of several shops pasted. These Shop names also appear as sheet names. It should take for every Shop name in the input sheet, every 'b' or 's' and the related date ranges (30,60,90,180) the related row information and paste it in the related sheet and the correct position within this sheet.

It is already partly working with a complicated formula, but it is not doing it properly.

An example:
Shop Name is "Anna Maria Island" in row A in the input sheet. It is searching for '30 Days' first in the last row, then for all 's' and paste all results in the "Anna Maria Island sheet, in the '30 Days' section. Then it continues with all the 'b' and then the same for '60 Days' and so on. After it is finished with this Shop, it takes the next...

Please tell me if you need more information on this.

[walruseggman]

Let me know how this works for you. If you run it more than once (like if you're going to be adding different data to the Input sheet, then running this again and wanting the new input added) then the formatting is going to be a bit off.

That's because it looked to me like you wouldn't want to add new rows into the sheets, and shifting data down in the sheet is a pain I didn't want to code unless you need it.

Sub Nevec90()
Application.Calculation = xlManual
Set ws1 = Worksheets("Input")
Set CheckRange = ws1.Range("A2:A" & ws1.Cells(Rows.Count, "A").End(xlUp).Row)

For Each Cell In CheckRange 'First for all the buyers
    If Cell.Offset(0, 1).Value = "b" Then
        Set ws2 = Worksheets(Cell.Value)
        PendRange = Cell.Offset(0, 10).Value2
        
        With ws2.Range("A1:A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
            Set c = .Find("Pending Time: " & PendRange)
            If Not c Is Nothing Then
                CopyRow = c.Row + 3
                Do Until ws2.Cells(CopyRow, "A") = ""
                CopyRow = CopyRow + 1
                Loop
                
            End If
        End With
        
        ws2.Range("A" & CopyRow & ":I" & CopyRow).Value = _
        ws1.Range("B" & Cell.Row & ":J" & Cell.Row).Value
    End If
Next

For Each Cell In CheckRange 'Now for all the sellers

If Cell.Offset(0, 1).Value = "s" Then
        Set ws2 = Worksheets(Cell.Value)
        PendRange = Cell.Offset(0, 10).Value2
        
        With ws2.Range("A1:A" & ws2.Cells(Rows.Count, "A").End(xlUp).Row)
            Set c = .Find("Pending Time: " & PendRange)
            If Not c Is Nothing Then
                CopyRow = c.Row + 3
                Do Until ws2.Cells(CopyRow, "A") = ""
                    CopyRow = CopyRow + 1
                    If Cells(CopyRow + 1, "A").Value = "s" Then CopyRow = CopyRow + 1
                Loop
                
            End If
        End With
        
        If ws2.Cells(CopyRow - 1, "A").Value = "b" Then CopyRow = CopyRow + 1
        
        ws2.Range("A" & CopyRow & ":I" & CopyRow).Value = _
        ws1.Range("B" & Cell.Row & ":J" & Cell.Row).Value
    End If
Next

Application.Calculation = xlCalculationAutomatic
End Sub

[Nevec90]

Hi,

Amazing. I should have asked for a Makro right away, before having so much headache with these nasty formulas
There was a formula hiding, so it showed up content because of this, my fault. Looks like it works perfectly, I will do more testing later today. Thank you alot!

[Nevec90]

Hi,

Looks great! Thank you already for that. Though it pastes wrong (more) data into 60 Days then there actually should be? Also it still creates an empty row between sellers and buyers, this was also with my formula, but one of the things I didn't want to happen.

PS: There will be hopefully just new shop sheets added, no new columns or rows.

[walruseggman]

Delete this line of code and it won't add the blank row between buyers and sellers (about 5 lines up from the bottom of the code):

If ws2.Cells(CopyRow - 1, "A").Value = "b" Then CopyRow = CopyRow + 1

From your example spreadsheet, I'm getting only rows 7 and 11 copied over to 60 days. Which I believe is as it should be. What other rows are you getting copied over?

[walruseggman]

Great! Don't forget to mark this thread as Solved, and any Reputation (the * in the lower left of each post) would be appreciated, too.