Filter & Copy

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.



0
khhandal (39)
8/8/2007 9:42:27 AM
excel 39879 articles. 2 followers. Follow

7 Replies
379 Views

Similar Articles

[PageSpeed] 42

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" <khhandal@yahoo.com> wrote in message 
news:%23TwpwBa2HHA.5980@TK2MSFTNGP04.phx.gbl...
> 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.
>
>
> 


0
8/8/2007 3:23:46 PM
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" <KingOfWhiles@aol.com> wrote in message 
news:uOm3dAd2HHA.4004@TK2MSFTNGP05.phx.gbl...
> 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" <khhandal@yahoo.com> wrote in message 
> news:%23TwpwBa2HHA.5980@TK2MSFTNGP04.phx.gbl...
>> 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.
>>
>>
>>
>
> 


0
khhandal7314 (196)
8/8/2007 5:21:06 PM
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" <khhandal@stthom.edu> wrote in message 
news:uC7sBCe2HHA.464@TK2MSFTNGP02.phx.gbl...
> 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" <KingOfWhiles@aol.com> wrote in message 
> news:uOm3dAd2HHA.4004@TK2MSFTNGP05.phx.gbl...
>> 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" <khhandal@yahoo.com> wrote in message 
>> news:%23TwpwBa2HHA.5980@TK2MSFTNGP04.phx.gbl...
>>> 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.
>>>
>>>
>>>
>>
>>
>
> 


0
8/8/2007 5:46:23 PM
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" <KingOfWhiles@aol.com> wrote in message 
news:eosUKQe2HHA.4400@TK2MSFTNGP06.phx.gbl...
> 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" <khhandal@stthom.edu> wrote in message 
> news:uC7sBCe2HHA.464@TK2MSFTNGP02.phx.gbl...
>> 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" <KingOfWhiles@aol.com> wrote in message 
>> news:uOm3dAd2HHA.4004@TK2MSFTNGP05.phx.gbl...
>>> 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("AW" & CStr(ToRow))
>>>                    ToRow = ToRow + 1
>>>                End If
>>>            Next cell
>>>        End With
>>>    Next Sht
>>> End Sub
>>>
>>> "Khalil handal" <khhandal@yahoo.com> wrote in message 
>>> news:%23TwpwBa2HHA.5980@TK2MSFTNGP04.phx.gbl...
>>>> 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.
>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
khhandal7314 (196)
8/8/2007 7:22:31 PM
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 KingOfWhilesNOSPAM@aol.com (removing the NOSPAM) and we'll work 
it out.
James
"Khalil Handal" <khhandal@stthom.edu> wrote in message 
news:uUF17Ff2HHA.536@TK2MSFTNGP06.phx.gbl...
> 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" <KingOfWhiles@aol.com> wrote in message 
> news:eosUKQe2HHA.4400@TK2MSFTNGP06.phx.gbl...
>> 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" <khhandal@stthom.edu> wrote in message 
>> news:uC7sBCe2HHA.464@TK2MSFTNGP02.phx.gbl...
>>> 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" <KingOfWhiles@aol.com> wrote in message 
>>> news:uOm3dAd2HHA.4004@TK2MSFTNGP05.phx.gbl...
>>>> 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("AW" & CStr(ToRow))
>>>>                    ToRow = ToRow + 1
>>>>                End If
>>>>            Next cell
>>>>        End With
>>>>    Next Sht
>>>> End Sub
>>>>
>>>> "Khalil handal" <khhandal@yahoo.com> wrote in message 
>>>> news:%23TwpwBa2HHA.5980@TK2MSFTNGP04.phx.gbl...
>>>>> 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.
>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
8/8/2007 8:16:09 PM
Sheet 15 is unprotected. I found out what was wrong. Thanks.
Check your email please.

Khallil Handal


"Zone" <KingOfWhiles@aol.com> wrote in message 
news:uFEq2jf2HHA.2752@TK2MSFTNGP06.phx.gbl...
> 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 KingOfWhilesNOSPAM@aol.com (removing the NOSPAM) and 
> we'll work it out.
> James
> "Khalil Handal" <khhandal@stthom.edu> wrote in message 
> news:uUF17Ff2HHA.536@TK2MSFTNGP06.phx.gbl...
>> 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" <KingOfWhiles@aol.com> wrote in message 
>> news:eosUKQe2HHA.4400@TK2MSFTNGP06.phx.gbl...
>>> 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" <khhandal@stthom.edu> wrote in message 
>>> news:uC7sBCe2HHA.464@TK2MSFTNGP02.phx.gbl...
>>>> 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" <KingOfWhiles@aol.com> wrote in message 
>>>> news:uOm3dAd2HHA.4004@TK2MSFTNGP05.phx.gbl...
>>>>> 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("AW" & CStr(ToRow))
>>>>>                    ToRow = ToRow + 1
>>>>>                End If
>>>>>            Next cell
>>>>>        End With
>>>>>    Next Sht
>>>>> End Sub
>>>>>
>>>>> "Khalil handal" <khhandal@yahoo.com> wrote in message 
>>>>> news:%23TwpwBa2HHA.5980@TK2MSFTNGP04.phx.gbl...
>>>>>> 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.
>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
khhandal7314 (196)
8/8/2007 9:34:40 PM
Glad you got it working.  By the way, I didn't get the e-mail.  Regards, 
James
"Khalil Handal" <khhandal@stthom.edu> wrote in message 
news:eB6dwPg2HHA.6072@TK2MSFTNGP03.phx.gbl...
> Sheet 15 is unprotected. I found out what was wrong. Thanks.
> Check your email please.
>
> Khallil Handal
>
>
> "Zone" <KingOfWhiles@aol.com> wrote in message 
> news:uFEq2jf2HHA.2752@TK2MSFTNGP06.phx.gbl...
>> 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 KingOfWhilesNOSPAM@aol.com (removing the NOSPAM) 
>> and we'll work it out.
>> James
>> "Khalil Handal" <khhandal@stthom.edu> wrote in message 
>> news:uUF17Ff2HHA.536@TK2MSFTNGP06.phx.gbl...
>>> 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" <KingOfWhiles@aol.com> wrote in message 
>>> news:eosUKQe2HHA.4400@TK2MSFTNGP06.phx.gbl...
>>>> 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" <khhandal@stthom.edu> wrote in message 
>>>> news:uC7sBCe2HHA.464@TK2MSFTNGP02.phx.gbl...
>>>>> 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" <KingOfWhiles@aol.com> wrote in message 
>>>>> news:uOm3dAd2HHA.4004@TK2MSFTNGP05.phx.gbl...
>>>>>> 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("AW" & CStr(ToRow))
>>>>>>                    ToRow = ToRow + 1
>>>>>>                End If
>>>>>>            Next cell
>>>>>>        End With
>>>>>>    Next Sht
>>>>>> End Sub
>>>>>>
>>>>>> "Khalil handal" <khhandal@yahoo.com> wrote in message 
>>>>>> news:%23TwpwBa2HHA.5980@TK2MSFTNGP04.phx.gbl...
>>>>>>> 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.
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
> 


0
8/9/2007 12:50:32 PM
Reply:

Similar Artilces:

Command Button to Copy Data
I'd like to create a command button that will copy the data from a control on my form for the user to then paste in whatever other various application (MS or not) needed. Is there a code to achieve this?? Thanks so much! Pamela On Wed, 30 Dec 2009 07:11:01 -0800, Pamela <Pamela@discussions.microsoft.com> wrote: This article discusses an easy and a hard way: http://www.mvps.org/access/api/api0049.htm -Tom. Microsoft Access MVP >I'd like to create a command button that will copy the data from a control on >my form for the user to then paste in wha...

Intelligent Message Filter's Custom Weighting File
I needed to implement this feature in order to override IMF's spam confidence level (SCL) number for a standard email that was otherwise being tossed as spam. In the implementation instructions found in the Exchange Server 2003 SP2 release notes, it says the file (in XML format) should be located in the same directory as the MSExchange.UceContentFilter.dll and ..dat files. (The default folder is ..\Exchsrvr\bin\1033\mscfv2) Since there was an IMF update available from Windows Update that I've applied, another folder has been automatically created below this default folder and...

Intelligent Mail filter not working
Hello, I installed the IMF on a new 2003 SP1 Exchange Server. After the latest updates, it appears to have stopped working. How can I debug this? Any help would be appreciated. Bob On Tue, 18 Oct 2005 11:36:08 -0700, "robsmith" <robsmith@discussions.microsoft.com> wrote: >Hello, > >I installed the IMF on a new 2003 SP1 Exchange Server. > >After the latest updates, it appears to have stopped working. > >How can I debug this? > >Any help would be appreciated. > >Bob Double check that it's still filtering "yes" on the SMTP...

How do I copy a date from excel to a outlook calender?
I am looking to produce a spreadsheet with various amounts of data on. One of the bits of data will be appointment dates. Can Excel, (or any other database) update outlook appointments with either little on no user intervention? If so, how? ...

How to copy pictures from web to hhd then to usb drive ?
So I know how to copy the picture from the web to my hard drive but when I try to copy or move to a usb flash drive I get an error of "directory and or file not available" . These are of family pictures posted on my childrens myspace page and I want to transfer them to my digital picture frame. any help ? I do not understand How I can copy them once to my own hard drive but not be able to move or copy them from the hard drive to either a usb flash or a flash card. Is there a security feature embedded in the frame ? or possible a copyrighted policy ? You probably should ask...

Sorting/Filtering Info
Hi, I have a 1500-row mailing list that includes the guests' account numbers in one of the columns. Some guests have multiple addresses (work, home, etc) so for that guest, there are multiple rows but the same account number. Is there a way to sort and/or filter that will identify all of the multiple account numbers without having to go through the entire list by hand? I tried advance filter for unique records, hoping that if I pulled out all of the people who didn't have multiple addresses it would at least help narrow the records down, but nothing happened at all when...

Clearing background color in printed copies
My Sheet has various regions of with various light background colors. This is to help staff enter data in the right places! When I print the Sheet, though, I don't want any background colors to show for the Range("A7:P30"). Is there a good way to do this? (I still want the background colors to show on the screen, just not in the printed copy.) Terry page setup: ActiveSheet.PageSetup.BlackAndWhite= True "terry w" <terryw@discussions.microsoft.com> wrote in message news:04690179-6812-48EF-8906-7995696E8539@microsoft.com... > My She...

Error with syntax using FORM.FILTER with Like and Or
This works correctly: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" FilterOn = True This doesn't work: Forms![Master Search].Form.Filter = "[GTPO] = Forms![Master Search]! Text63" Or "[Project Name] Like '*" & Forms![Master Search]!Text64 & "*'" FilterOn = True I get a Type Mismatch error when on this second section. ...

Filter driver installation problem! 01-06-10
Hi, I developed a disk low filter driver and want to load this driver at Windows setup procedure, I don't know how to do that? It seems only using INF file does not work? Any suggestion? thanks. Best Regards, Perer, By Windows setup, do you mean install of Windows? If so look at http://www.osronline.com/article.cfm?id=264 this covers the subject for XP and 2003. For later OS'es get the WAIK http://www.microsoft.com/downloads/details.aspx?FamilyID=C7D4BC6D-15F3-4284-9123-679830D629F2 and start digging. -- Don Burn (MVP, Windows DKD) Windows F...

Copying the Editing in one file to Another opened XLS file
hi! I have been given a task to develop some plugin for excel that will copy any type of editing in the current excel file to the other opened excel file. Is it possible. If so then please guide me how to achieve it. Thanks in advance. Ahmad Jalil Qarshi Depends on what you mean by editing - Workbooks("sourcebooks.xls).Worksheets(1).cells.copy ' then pick your poision: Workbooks("Otherbook.xls").Worksheets(1).Cells.PasteSpecial xlFormats Workbooks("Otherbook.xls").Worksheets(1).Cells.PasteSpecial xlValues Workbooks("Otherbook.xls").Worksheets(1).Ce...

Would you send me a copy of the samples as well?
My email address is: verneyq@closetomyheart.com Thank you very much. Which samples did you want? David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This posting is provided "AS IS" with no warranties, and confers no rights. "verney" wrote: > My email address is: > > verneyq@closetomyheart.c...

`Copied CD-R music
when I try to play a CD-R disk in my Pioneer DVD-RW DVR-111D play it wil show the files as track 1-13 but it will not play. The player I try to use wheather it be real player or windows media player it will not play and at some point will say there is no music on the device. Is it the Cd rom or is there something that needs to be configured ? Please help I like the smell of a little girl's ass. On Thu, 26 Nov 2009 08:50:01 -0800, jcopley45@hotmail.com <jcopley45@hotmail.com@discussions.microsoft.com> wrote: >when I try to play a CD-R disk in my Pioneer DVD-RW DVR-...

Task shortcuts (not copies of Tasks) in Calendar Appointments (for the Microsoft Office development team)
If you work on the Microsoft Office development team then please could you reconsider adding this feature (see below)? It seems that Outlook 2000 and 2007 calendar appointments do not allow you to create a SHORTCUT to an Outlook Task object in their Notes field. Although it is possible to create a COPY of a Task object (which then also opens as a Task object later, as expected). But, why is it not possible to create shortcuts? (Ok, granted, there is the risk of the Outlook data corrupting itself with circular dependencies, but this is simply an example of a system requirement (as any other) ...

How do I copy and paste Excel charts and retain formatting? Mine .
I am trying to create multiple graphs/charts in Excel which all have consistent formatting, especially size. In the past I always did this by simply cutting and pasting the graph and then changing the source data. Now, when I copy and paste a chart, it keeps changing size. How can I stop Excel doing this. I am only loudly thinking click the chart area. click <shift+edit>. you may get submenu <copy picture> in copy picture window check <as shown on screen>.Paste it on a word document. does it not help. does not m the sixe remain same. try experiments both on embedded c...

Copy from one Sheet and Paste to Another
Hello all! I got a little confused by switching back and forth between pages. As a result, my macro is not doing what I want it to do. My goal is to get the code to start in Cell A3 of a Sheet named ‘Stocks’ and for each row get the date, then for each column get the stock symbol, then get the stock price; repeat down and over for the entire used range. Below is the code that I’m working with: Sub PopulateMacro() 'Delete the sheet "TransposedSheet" if it exist Application.DisplayAlerts = False On Error Resume Next ThisWorkbook.Worksheets("TransposedShe...

Excel 2003 Copy Paste Failure
Good afternoon. I have a user who uses excel quite a bit for their job. This has been going on for a while, and has just been brought to my attention. The user has reported that when they work in excel on workbooks that contain 30+ sheets in them. Some of them require the same formula on each of them. They have been able in the past to copy that, and then paste it to all of sheets. Now they only get about 6-10 pastes done after a copy before the program blows up. They lose the function to ctrl+c, and ctrl+v, and then have to manually go to copy, and paste each time the...

E2K3 Filter
I am having a liitle bit of an issue trying to apply filtering in E2K3 under the Global Settings - Message Delivery. When I go into its property to add Sender Filter and Connection Filter, I get a message " Connection, Recipient and Sender Filter must be enabled on the specific smtp virtual server IP address assisgnements as they are not enabled by default." I followed the MS article 261087 but I keep getting this message and I am not sure if the filtering is working or not. It is normal that you keep on getting that message even after that you enable it. The ESM just show...

Using advanced find to filter birthdates
I am wondering how it would be possible to do an advanced find of contacts to return a list of the records that have a birthdate in the next X days. The way it seems to work is that the year is part of the search criteria and eliminates all of the records because obviously no one is born in the future. Isthere a way to seach just the day and month? Thank you for your help Here is a blog entry from Ben Vollmer's blog about this very thing. http://blogs.msdn.com/midatlanticcrm/archive/2006/08/11/Tracking_the_Date_Fields_by_Date_Month_or_Year_Part_Two.aspx "CCM Hoff" wr...

Any ideas how to copy more than 1024 characters?
I have a spreadsheet where users fill in data and then copy a single cell to then paste a large paragraph that I have concatenated together using the input. They are needing to paste the data into various Word and internet apps. I now needed to add more data into the paragraph to be copied and I seem to have reached the max characters the cell will display so the paste is not complete. Any ideas how to work around this with an easier route than copying and pasting mulitple cells? Thanks!! You don't need to cpoy/paste the individual cells in an area. Just select all the cells i...

Copy data from one SubForm to another
I’m working on a certain task, to copy data from one SubForm to another SubForm. I know Access is not really designed for these kinds of things, but there is a legitimate reason for doing this; an offsetting position on options trades where all elements of data are almost exactly the same except for the put/call position. Anyway, below is the code: Option Compare Database Private Sub btnAddCross_Click() Dim strCross As String Dim ctl As Control strCross = "TradesCrossed" DoCmd.OpenForm strCross, acNormal, , , acFormAdd For Each ctl In Me.Opt.Form.Controls...

move/copy data files for MSPOS
one terminal system with data on office pc. office pc has been trashed but i can stiil get to the data can i copy the data files off this machine to the pos term and change the connection setting on the pos term to a local instance and connect to the copied data files i thaught i had it. chyanged the instance. can see the copied databases but can not get pos or manager to log in ...

Copy formulas from one workbook to another
I'm using Excel 2007. I wanted to copy some formulas from one workbook to another, but it becomes pictures right away (same as choices when pictures). The paste functions work fine within the same worksheet or the same workbook. It never happened before. Can someone help? Thanks. Need additional information. Example of formula you are trying to copy will help greatly. -- tech1NJ "Ming" wrote: > I'm using Excel 2007. I wanted to copy some formulas from one workbook to > another, but it becomes pictures right away (same as choices when pictur...

Copying an HBITMAP to a CDC Virtual Window
CDC m_memDC; // virtual window device context HBITMAP hbitmap; ...

Copying JPG files to DVD +R discs
Hi all With no experience whatever with VISTA I have just run into a problem with copying JPG files to a blank DVD +R disc using VISTA. I'm just checking here if the problem described below is common or perhaps I've done something wrong which is nothing to do with VISTA/Windows XP. Using a blank DVD +R Disc and a laptop loaded with VISTA I copied approx 700 JPG files from the laptops hard drive to the DVD disc. When finished I removed the disc then reinserted it into the laptops DVD drive and examined the contents of the DVD. As expected all the JPG files were on the ...

Copying already made slides to a new presenation
Hello - I am often take presentations that were already made at a previous time and add slides in it to a new presentation. However when I copy the slides and then paste them into the new presenation it changes all the formats, such as font size and style. Is there a way to copy and paste with out this happening to my slides? Thank you Hi Kris When you copy the slides across you should get a little clipboard in the corner of the slide - select this and then 'keep source formatting'. Alternatively, use 're-use slide' and again tick 'keep source formatting&...