• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code for filter

Veeru106

Member
Hi,


Can I have a code which filter “Yes” in col. D and copy all the data in Col. A


Then create & paste it in separate sheet named “FTEs”.


thanks
 

Attachments

  • ADDT.xlsx
    9.6 KB · Views: 8
Code:
Option Explicit

Sub veeru1()
    Dim lr As Long
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Range("A1:D1").AutoFilter
    ActiveSheet.Range("A1:D" & lr).AutoFilter Field:=4, Criteria1:="Yes"
    Range("A1").CurrentRegion.Copy Sheets("FTEs").Range("A1")

End Sub
 
Thanks for the code Alan but it is giving me script out of range error message for “Range("A1").CurrentRegion.Copy Sheets("FTEs").Range("A1")”


Please suggest
 
Actually i want FTEs sheet added each time but that's ok...thanks for the code it is working....but i need one modification where we have number of data according to the weeks.


In Summary sheet we want “Yes” numbers for each week in Col. D3…I tried to automate it by adding countif formula…..but numbers should change if we select week from drop down…right now it showing week 18 numbers of “Yes”in Col. D of Data sheet…so need help here…


Another assistance required in code itself……can we have a code which gives me details of “Yes” according to the week we select in drop down….so actually when we select week 18 it should give me number by formula we add in col. D


And with the help of code it would give me details of these person in FTEs sheet.

Adding another sheet for the same

Thanks for looking at it
 

Attachments

  • ADDT.xlsm
    19.4 KB · Views: 6
Actually i want FTEs sheet added each time
According to initial attachment, a demonstration creating worksheet if needed,
paste this code (v2) to the Data worksheet module :​
Code:
Sub Demo1()
    If Not [ISREF(FTEs!A1)] Then Worksheets.Add(, Me).Name = "FTEs"
    [K1:K2].Value = [{"Formula";"Yes"}]
    [A1].CurrentRegion.AdvancedFilter xlFilterCopy, [K1:K2], [FTEs!A1:D1]
    Worksheets("FTEs").UsedRange.Columns("A:B").AutoFit
End Sub

Do you like it ? So thanks to click on bottom right Like !
 
Hi Marc,

Thanks for the code,,,but that is not working...did you try on the worksheet i have attached....i tried to run but it gives back error " invalid keword me".

Please suggest..
 
I am ok if we add another sheet permanently FTEs....but another issues like it should give me FTEs numbers "Yes" according to week i select in Summay sheet..Thanks
 
did you try on the worksheet i have attached...
Yes as I yet wrote :
According to initial attachment,
a demonstration creating worksheet if needed


i tried to run but it gives back error " invalid keword me".
As you can take a glance to Me in VBA inner help …
I suggest you must well read and apply the direction I wrote, again :
paste this code to the Data worksheet module


I am ok if we add another sheet permanently FTEs....but another issues like it should give me FTEs numbers "Yes" according to week i select in Summay sheet..Thanks
First, the worksheet don't need to be permanent
with my « demonstration creating worksheet if needed » ! …

Second, don't you see the light with this beginner level demonstration ?!
As you can already achieve this without any code
just using an Excel basics feature : an advanced filter !
Enter in E4 cell of Summary worksheet the criteria (avoid any typo)
and update the code for the criteria range of AdvancedFilter method
as you can read in VBA inner help.

Third, as a reminder : the initial post must be crystal clear, accurate &
complete with an attachment reflecting exactly the real workbook.
As the better initial post & attachment, the better & quicker solution …

You changed the need during the thread, maybe it's the reason why
Alan didn't answer anymore.
And on my side, I always first answer to initial post when I have time …
 
Hi Marc,


I am really sorry for the multiple attachment but as I was working on this data, requirement changed....first I was planning to use different sheets for every week data but then thought that will not be necessary, as we can paste data in one sheet only.


As you can see in new attachment we can have multiple week data in one sheet , that’s why I insist on displaying specific week data “Yes” in FTEs sheet and that week we will select in Summary tab.


For example…if I select week 20 in summary tab then it should display only week 20 data in FTEs tab.


Secondly I tried to run your code on first attachment but again I got same “me”error message, as I am beginner in VBA coding so can you please take another look on second attachment.


I am playing with huge data, hence need this help from your side.


Thank you
 
Wait until you understand your need before to create any thread …

As long as my first demo does not work on your initial attachment
I won't waste time for a second beginner level demo (even for 3 codelines)
as it will work again on my side only !

If you read VBA inner help, you can replace the statement
with any worksheet reference but
your error comes from you did not paste the code to the right place
so just paste the code to the Data worksheet module
 
Sorry for the confusion and multiple questions…


Now I have pasted this to Data sheet module and it is working exactly what we need as per first question pasted in thread


Can you please make modification in it so that it take command from summary sheet and show data according to the week we select there.
 
Sorry Marc..i was in little hurry as i need to present this to my manger on Monday....i really appreciate your time and efforts on this....i got my solution now....next time i will not crosspost anywhere...
 
Back
Top