Parsing data to several sheets

[strato]

Hello
I am using Excel 2010 and am trying to crunch data then send the data to several worksheets.
Currently, I am able to filter out unneeded data and send to one sheet.
But, I need to filter out particular data to send to each sheet. There are 5 total sheets to send to.
I have the worksheets named since the worksheet seems to change every time this is ran.

I'm just taking a small part of this at one time.
So far I have ...


Sub SendtoBShift()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim ViewMode As Long


With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False


Firstrow = .UsedRange.Cells(1).Row
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row


'We loop from Lastrow to Firstrow (bottom to top)
For Lrow = LastRow To Firstrow Step -1
With .Cells(Lrow, "G") ' used to be col D
If Not IsError(.Value) Then
'This will send each row with the Value "B" to sheet B Shift
If .Value = "B" Then Worksheets("B Shift").Activate ' this line is the problem
End If
End With
Next Lrow
End With
End Sub


... but this still just sends to the first sheet.

Any help is appreciated

[offthelip]

Firstly I think your title is misleading, your question is more about "passing" data to several sheet not "parsing" which is something completely different :

This should do what you have asked for:


Sub test()
Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim ViewMode As Long
Dim Blrow As Long
Dim Garr As Variant

With Worksheets("B shift")
Blrow = .Cells(Rows.Count, "A").End(xlUp).Row
End With


With ActiveSheet
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
' pick column G in variant array to spped things up
Garr = Range(.Cells(1, 7), .Cells(LastRow, 7))

For Lrow = LastRow To 1 Step -1
If Garr(Lrow, 1) = "B" Then
Rows(Lrow).Copy Destination:=Sheets("B shift").Range("A" & Blrow + 1)
Blrow = Blrow + 1
End If
Next Lrow
End With
End Sub

[strato]

Thank you that helps!
It sent it to the 2nd sheet as wanted but it also sent it to the first sheet.
I'll look through my code again

[snb]

Use advancedfilter.

[strato]

Hello
I'm trying to understand this procedure better. Can someone answer a few questions on this?
Correct me if I am wrong on some line descriptions


Dim Firstrow As Long
Dim LastRow As Long
Dim Lrow As Long
Dim ViewMode As Long
Dim Blrow As Long ' I understand first row and last row but what is Blrow?
Dim Garr As Variant

With Worksheets("B shift")
Blrow = .Cells(Rows.Count, "A").End(xlUp).Row ' Is A column A?
End With

With ActiveSheet
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
' pick column G in variant array to spped things up
Garr = Range(.Cells(1, 7), .Cells(LastRow, 7)) ' is Garr G array ... and is the 7 referring to col G since it's the 7th column

For Lrow = LastRow To 1 Step -1
If Garr(Lrow, 1) = "B" Then
Rows(Lrow).Copy Destination:=Sheets("B shift").Range("A" & Blrow + 1)
Blrow = Blrow + 1
End If
Next Lrow
End With


thanks for any help. I have a long way to go for understanding this

[offthelip]

Blrow is the last row of the B shift sheet, so this is where the rows are copied to:
Lastrow is the last row of the Active sheet so this is where the copy loop starts and run upwards copyiung each row as it finds them to successive rows on the B shift sheet.

[mdmackillop]

but what is Blrow?
Variants can be called anything other than reserved names of funtions and the like. In this case it is being used for the last row of column A on sheet B Shift


and is the 7 referring to col G since it's the 7th column

Correct.
You can add "check lines" to help understand/visualize while building your code eg
Range(.Cells(1, 7), .Cells(LastRow, 7)).interior.colorindex = 7