Copy cells from one sheet to another based on one criteria

[Shamimo]

Hi everyone,

I am not a very experience Excel user and not a programmer... so VBA codes are foreign languages to me.. but i would need some help in doing something.

Lets say i have a workbook with 2 sheets : SI and Members
In Sheet SI :
In cell B3 i have the criteria selection based on which i want excel to copy data. The data must be copied in columns A10.

In sheet Members, i have a list of members and based on the criteria selected at line B3, i want excel to copy all members code (Sheet Members Column A) that mach the criteria into sheet SI.
Based on the members code copied in the sheet SI, i will then have a Vlookup function to retrieve the members name and payment amount from the sheet Members and copy it in the cell next to member code in the sheet SI.

Can someone help me on that please ? i dont know if did explain the issue properly...

This excel file will be sent by email to users and they will manualy update regularly the new members in the sheet members but the sheet SI should work the same.

Thanks a lot for you help... if possible.

 

[Pediez]

Hi Maybe you can modify this code to your needs wasnt sure what you actually wanted...

[/FONT]
[FONT=Courier New]Sub Test()
    Dim lr As Long
    Dim CustomCritiria As String
    CustomCritiria = Sheets("SI").Range("B3").Value
    lr = Range("A" & Rows.Count).End(xlUp).Row
    Sheets("Members").AutoFilterMode = False
    Sheets("Members").Range("A1:E" & lr).AutoFilter Field:=4, Criteria1:=CustomCritiria
    Sheets("Members").Range("A2:E" & lr).SpecialCells(xlCellTypeVisible).Copy Sheets("SI").Range("C1")
End Sub

 

[Shamimo]

Thanks for the macro.. however, i might not be able to use it as the file will be distributed to other persons with different versions of excel.. it can range from xls97 to xls2010...