Coping to multiple sheets

[Workathome]

Hey

I have an excel sheet with data on it that looks like this: All the data is in this order and opened from an exported csv.

A3 B3 C3 D3 E3 F3 g3 H3

Under the "company" I have 15 companies with different acronyms (ABC, DEF, GHI etc etc)

I have sheet tabs below with the company acronym labels (ABC, DEF, GHI etc etc) on the same workbook.

I need to have the data auto copied to the proper sheet and then sorted to this format below. As you can see the titles are somewhat in the same order.
Some are not however.

I created a module (see below) but it is not working and I can not figure out why.


Sub CopyOverbycompanyABC ()


Dim Company As Range
Dim Status As Range
Dim PasteCell As Range

Set Company = Sheet1.Range("A4:A20541")

For Each Status In Company

If Sheet2.Range("F2") = "" Then
Set PasteCell = Sheet2.Range("A2")
Else
Set PasteCell = Sheet2.Range("A1").End(xlDown).Offset(1, 0)
End If

If Status = "BWH" Then Status.Offset(0, -4).Resize(1, 5).Copy PasteCell

Next Status

End Sub


Any assistance would be greatly appriciated.

Thank you

Eric