Looping and conditions - VBA

[Raist]

Hello!

I get some workbooks on a weekly basis from a supplier. The data is split across 4/5 worksheets and I pick up certain data from the last worksheet (anything that has a 0 as the 1st charachter in the string in column B) and add it to the prior sheet under the last cell in column B with a 0 as the 1st letter. I then repeat until all data is on the 1st sheet. I was wondering if anyone would be able to help me build it please?

I'm thinking I'll need to loop through the sheets in the workbook, use a for loop on each sheet with an if statement nested inside to test for the 1st charachter being 0. Something like this:

For Each Sheets In Workbook
For j = 2 To 50
If Left(Cells(j, 2), 1) = 0 Then Rows(j).Copy _

Next cell
Next Sheet

I think the bit I'm going to find most difficult is pasting to the prior sheet under the last cell with a 0 in it (there is text information in the cells above and below that data but I'm happy for that to be overwritten).

Thanks (and apologies, I'm very early in my macros studies!)

 

[My Aswer Is This]

Assuming you want to copy all the rows in all your sheets starting in Sheet (2) in your Workbook if Column B value begins with a zero.

This script will copy all those row into Sheet(1)

Run this script from Sheet(1)

Which is the sheet in the far left position on your tab Bar

Sub Copy_Row()
'Modified  9/11/2018  2:22:12 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim b As Long
Dim Lastrow As Long
Sheets(1).Activate
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = 2
    For b = 2 To Sheets.Count
        Lastrow = Sheets(b).Cells(Rows.Count, "B").End(xlUp).Row
            For i = 2 To Lastrow
                If Left(Sheets(b).Cells(i, 2), 1) = 0 Then
                    Sheets(b).Rows(i).Copy Sheets(1).Rows(Lastrowa)
                    Lastrowa = Lastrowa + 1
                End If
            Next
    Next
Application.ScreenUpdating = True
End Sub

 

[Raist]

Thanks so much for this, My Answer Is This. It's not doing quite what I'm looking for it to do (I don't think my description of what I wanted was the clearest) but it's a lot closer than I was - I think I'll work with that and see if I can get it to do from here - will be good practice for me - thanks again! So impressed with this.

 

[My Aswer Is This]

Glad I was able to help you. Glad your trying to sort it out by yourself. Not sure what it's doing wrong. If you need more help please explain what it's doing wrong. And maybe explain more what your wanting.

Thanks so much for this, My Answer Is This. It's not doing quite what I'm looking for it to do (I don't think my description of what I wanted was the clearest) but it's a lot closer than I was - I think I'll work with that and see if I can get it to do from here - will be good practice for me - thanks again! So impressed with this.