• 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.

retrieve data via VBA

A

Afarag

Member
Dears,

i have a sheet that contain a large amount of data that i ask to match a specific criteria to look up its data

in the attached sheet i ask to lookup "ID" with a selected drop down list for "Month" to retrieve its data
 

Attachments

  • Book1.xlsx
    156.3 KB · Views: 18
Hello Karr

thanks a lot for your great answer it's really get what i'm looking for, but sorry for another query what if i want to hide some columns from source sheet
eg: what if i need to retrieve columns "A,C,D,G,K"
and can i set a schedule format for the retrieved data?

thanks a lot
 
Last edited:
You can also use a PivotTable to answer this, and avoid VBA if you want:
 

Attachments

  • Consolidate data_Karr.xlsm
    186.6 KB · Views: 11
Hi,

You may try this short code too.

Code: 
Sub get_data()
Dim r As Range, i As Long
Application.ScreenUpdating = False
i = 6
For Each r In Range("Table1[ID]")
    If LCase(r) = LCase([A2]) And r.Offset(, -3) = [c2] Then
        r.Offset(, -3).Resize(, 11).Copy Cells(i, 1)
    i = i + 1
    End If
Next
Set r = Nothing
Application.ScreenUpdating = True
End Sub
 
How i forgot this..

Code: 
Sub get_data_autofilter()
Range("Table1[ID]").AutoFilter 4, [A2]
Range("Table1[Month]").AutoFilter 1, [C2]
Sheet2.ListObjects("Table1").AutoFilter.Range.Offset(1).Copy [A6]
Range("Table1[Month]").AutoFilter
End Sub
 
@jeffreyweir,

thanks a lot for your solution, i know how to handle with this option.

@jeffreyweir, @Deepak, @Mr.Karr

sorry for confusion,
as i need no one can show the other users points.
the workbook contain 2 sheets called "Deduction & Rewards"
i need to set an interface sheet have an user form contain Username - Login ID - ID - Month and button for deduction and other button for Rewards
as the employee can fill his data "user, Login ID, ID, and Month", then when the employee click to Deduction or Rewards button an popup sheet with disabled ribbons show the matched data from the source for the inserted criteria
can that handled that when the source sheets will be very hidden
in brief; i need the sheet show only the interface userform, and the 2 source sheets will be very hidden

sorry the new attached sheet more than one M so that i uploaded it in my one drive
URL: http://1drv.ms/1x4MZ72

thanks,
 
I managed it to reduce size after altering the data. That's is uploaded here.
 

Attachments

  • Points_Resized.xlsx
    111.5 KB · Views: 9
Deepak

you are right about that, unfortunately i'm already uploaded sample of my main sheet, as this sheet is considered 10% from the source that i'll handle after that
 
in deduction sheet i have been hide a few columns that the shown only need to be popup
 

Attachments

  • Points_Resized.xlsx
    100 KB · Views: 3
Deepak,

if you mean the schema of userform, there is a prtsc to the requested form

0


thanks
 
i didn't ask to retrieve an image
the above image for the userform design that i need to insert this criteria to retrieve this data from the source sheet
 
Afarag said:
i didn't ask to retrieve an image
the above image for the userform design that i need to insert this criteria to retrieve this data from the source sheet
Click to expand...

As you said "above image"... same is not visible here.
So, instead of screen shot of a userform just upload the UF.
 
Sometimes ago i realised that i forget this to do this-one.

Pls loop to herewith enclosed where i did the same for Deduction & you might do the same for Rewarding after understanding the code.
 

Attachments

  • Points_Resized (1).xlsm
    148.1 KB · Views: 10
You must log in or register to reply here.
Back
Top