Filter & Copy

[Khalil handal]

Hi,
I have 15 sheet is one workbook.
12 of them represent months starting June 2006 until May 2007.
the other 3 sheets are reports.

For each month in the range AW201:AW249 either 1 or empty (nothing is
entered).
I need to copy all the lines that has the value 1 in the range AW201:AW249
from ALL the 12 month sheets to a new sheet in the same workbook.

How can this be done using VBA code or even a Macro!!!

Note:
The sheets are protected.
I am also using freeze pan option.

 

[Zone]

Khalil, I just added a new worksheet manually (worksheet 16).
I assumed that the monthly sheets are worksheets 1-12.
I also assumed that you just want the rows with a 1 in column AW to be added
to the new sheet one after the other.
Lastly, I assumed that row 1 of the new sheet contains column headings.
If this is right, just copy the code below, paste in a regular module, and
run the macro.
Also, note that the routine does not clear worksheet 16 for subsequent runs.
Hope this helps,
James

Sub PutOnSheet16()
Dim Sht As Integer, ToRow As Long, cell As Range
ToRow = 2
For Sht = 1 To 12
With Worksheets(Sht)
For Each cell In .Range("AW201:AW249")
If cell = 1 Then
.Rows(cell.Row).Copy
Destination:=Worksheets(16).Range("A" & CStr(ToRow))
ToRow = ToRow + 1
End If
Next cell
End With
Next Sht
End Sub

 

[Khalil Handal]

Hi,
The monthly sheets are named as followed:
June 2006, July 2006, ... , May 2007. and not 1 - 12 as you assumed. It
might be difficult to rename them because I am using the sheet name in some
cells. I think this will cause modifications in the For...Next loop of I am
not mistaken!!!

All the other assumptions are OK with me.

 

[Zone]

Khalil, It makes no difference what the names of the sheets are. It only
matters that the monthly sheets are the first 12 worksheets in the workbook.
If this is so, then the subroutine should work. If not, post back and we
can use an array. (Also note that the Copy statement should continue on
with a space and then the destination part. These are on one line, not 2.
This line was broken by the newsgroup.)
James

 

[Khalil Handal]

Hi,
First of all I have the message that the cells are protected so I unprotect
all the cells and still have the same message.
Then I changed the copy line and added "W" and it looks as follows:

.Rows(cell.Row).Copy
Destination:=Worksheets(16).Range("AW" & CStr(ToRow))

and had the message that it cannot paste because the copy area and the past
area are not the same size and shape.

I don't know if it is related to starting in line 201 for all the sheets and
in destination sheet which has the name "16" starts at line 1 putting into
consideration that sheet "16" is empty an nothing is inside it!!!

 

[Zone]

If you unprotect Sheet 16, you should have no problem with the original
code. Note that since you are copying/pasting the entire row, you will have
to refer to column A when pasting (not column AW). Also, note that Sheet 16
MUST BE the 16th sheet in the workbook. If you're still having trouble,
e-mail me at (e-mail address removed) (removing the NOSPAM) and we'll work
it out.
James

 

[Khalil Handal]

Sheet 15 is unprotected. I found out what was wrong. Thanks.
Check your email please.

Khallil Handal

 

[Zone]

Glad you got it working. By the way, I didn't get the e-mail. Regards,
James