Peter,
I have used below code, now error returning is stopped. But its returning the data of the 1st sheet to all the sheets where data is not available. With respect to below code, I dont have data available in my spread sheet for all FA-10XPort1 sheets, I have data available only for FA-10XPort0, but code is retuning FA-10APort0 values to all the sheets of FA-10Xport1. As the data is not availble for any FA-10XPort1, All the sheets of FA-10Xport1 should be blank. Please help.
PS: FA-10XPort1 refers to FA-10APort1, FA-10BPort1 etc
thanks in advance,
Lavan
Sub CopyRows_new()
Dim lr As Long
Application.ScreenUpdating = False
Sheets.Add.Name = "FA-10APort0"
Sheets.Add.Name = "FA-10APort1"
Sheets.Add.Name = "FA-10BPort0"
Sheets.Add.Name = "FA-10BPort1"
Sheets.Add.Name = "FA-10CPort0"
Sheets.Add.Name = "FA-10CPort1"
Sheets.Add.Name = "FA-10DPort0"
Sheets.Add.Name = "FA-10DPort1"
Rows(1).EntireRow.Insert
lr = Range("A" & Rows.Count).End(xlUp).Row
With Range("A1:A" & lr)
On Error Resume Next
.AutoFilter Field:=1, Criteria1:="FA-10APort0"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10APort0").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="FA-10APort1"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10APort1").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="FA-10BPort0"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10BPort0").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="FA-10BPort1"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10BPort1").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="FA-10CPort0"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10CPort0").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="FA-10CPort1"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10CPort1").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="FA-10DPort0"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10DPort0").Range("A1")
.AutoFilter
.AutoFilter Field:=1, Criteria1:="FA-10DPort1"
.Resize(.Rows.Count - 1, 10).Offset(1).Copy _
Destination:=Sheets("FA-10DPort1").Range("A1")
.AutoFilter
On Error GoTo 0
End With
Rows(1).EntireRow.Delete
Sheets("masking").Select
Application.ScreenUpdating = True
End Sub