Put data by matching cell value with sheet name

[K]

Hi all, I have 4 Sheets in workbook

Sheet 1 Name: Main Data
Sheet 2 Name: 1XX
Sheet 3 Name: 2XX
Sheet 4 Name: 3XX

In Sheet("Main Data") I have data like (see below)

A B C D------col
Non 1XX 123 Bud
Non 1XX 123 Bud
Non 2XX 123 Bud
Non 2XX 123 Bud
Non 2XX 123 Bud
Non 3XX 123 Bud
Non 3XX 123 Bud
Non 3XX 123 Bud

I want macro which should check column B cells value of Sheet("Main
Data") and if that value match with any Name of Sheet in the workbook
then macro should copy that value cell row from Range(A) and paste
it to in range(A2) of matching name Sheet and like this next cell and
next sheet. For example according to above data in Sheet("Main Data")
macro should check column B cells value and in first two cells value
is 1XX and there is Sheet which name is also 1XX so macro should copy
these two cells rows from Range(A) in that Sheet in Range(A2) and it
should continue untill there is no value in column B cells. I hope I
was able to explain my question. Please can any friend can help

 

[Per Jessen]

Assuming you have headings in "Main Data" sheet / row 1.

Sub aaa()
Dim MainSh As Worksheet
Dim TargetRange As Range
Dim ToCopy As Range

Set MainSh = Worksheets("Main Data")
Set TargetRange = MainSh.Range("B1", MainSh.Range _
("B" & Rows.Count).End(xlUp))
off = 0

For Each sh In ThisWorkbook.Worksheets
If sh.Name <> MainSh.Name Then
f = TargetRange.AutoFilter _
(Field:=1, Criteria1:=sh.Name)
If f = True Then
Set ToCopy = TargetRange.SpecialCells _
(xlCellTypeVisible)
For Each cell In ToCopy
If cell.Value <> "Heading" Then ' change to column heading
cell.Offset(0, -1).Resize(1, 4).Copy _
Destination:=sh.Range("A2").Offset(off, 0)
off = off + 1
End If
Next
End If
End If
off = 0
Next
TargetRange.AutoFilter
End Sub

Hopes this helps

 

[K]

Thanks lot per jessen you code works perfect