Macro for protected sheet

[kishore579]

Dear all,

I wanted the following from a data dump.

1. If the Data contain "Set" in 11th column it should copy entire row into "Match" sheet.

2. From Match sheet, Macro should look for word "GG22" , "SS22" , "UU44" , "LL22", "GG2X", "BI22" and "FM22" in 6th column and for each match should copy entire row and paste into respective sheets (Sheet names being "GG22" , "SS22" , "UU44" , "LL22", "GG2X", "BI22" and "FM22")

3. From each spread sheet ("GG22" , "SS22" , "UU44" , "LL22", "GG2X", "BI22" and "FM22")

I need to derive some value using if conditions into different sheets
("GG22 B" , "SS22 B" , "UU44 B" , "LL22 B", "GG2X B", "BI22 B" and "FM22 B")
Ranga of data is A2:A4999to P2:P4999

Example of my "IF" formula

=IF('GG22'!$X2='GB22 B'!A$1,'GB22'!Y$2,0)


My Macro Reads as below.


Sub Macro2()

Dim ws As Worksheet
Application.ScreenUpdating = False
For Each ws In Worksheets
If ws.Name <> "Data" Then ws.UsedRange.Offset(2).ClearContents
Next ws
With Sheets("Data")
.Range("A2").AutoFilter field:=11, Criteria1:="Set"
.AutoFilter.Range.Offset(1).Copy Sheets("Match").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With
With Sheets("Match")
.Range("A2").AutoFilter field:=6, Criteria1:="GG22"
.AutoFilter.Range.Offset(1).Copy Sheets("GG22").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With
With Sheets("Match")
.Range("A2").AutoFilter field:=6, Criteria1:="SS22"
.AutoFilter.Range.Offset(1).Copy Sheets("SS22").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With
With Sheets("Match")
.Range("A2").AutoFilter field:=6, Criteria1:="UU44"
.AutoFilter.Range.Offset(1).Copy Sheets("UU44").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With
With Sheets("Match")
.Range("A2").AutoFilter field:=6, Criteria1:="LL44"
.AutoFilter.Range.Offset(1).Copy Sheets("LL44").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With
With Sheets("Match")
.Range("A2").AutoFilter field:=6, Criteria1:="GG2X"
.AutoFilter.Range.Offset(1).Copy Sheets("GG2X").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With

With Sheets("Match")
.Range("A2").AutoFilter field:=6, Criteria1:="BI22"
.AutoFilter.Range.Offset(1).Copy Sheets("BI22").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With
With Sheets("GG22")
.Range("A2").AutoFilter field:=6, Criteria1:="FM22"
.AutoFilter.Range.Offset(1).Copy Sheets("FM22").Range("A" & Rows.Count).End(xlUp)(2)
.ShowAllData

End With

End Sub


----------------

Problem 1 :

Each time i run the Macro all the Formulas in
("GG22 B" , "SS22 B" , "UU44 B" , "LL22 B", "GG2X B", "BI22 B" and "FM22 B")


Looking for 1:

Can i include some code in my macros that will not disturb my formulas in
"GG22 B" , "SS22 B" , "UU44 B" , "LL22 B", "GG2X B", "BI22 B" and "FM22 B" sheets

Looking for 1:
Can i protect the sbove sheets and run the macro and how to unprotect the above sheet in my existing macro.