Macro Formulae?

[midfieldgeneral11]

Can you help?

I have two worksheets in a Excel spreadsheet.

One is a Download of 15000 items of data from our database where we have Client ID, Name, Surname, Post Code and Date of Birth.

The Other is Summary sheet which is going to be used as a lookup table.

The summary sheet in A1 is where I want the user to place in a Date of Birth lets Say 12/07/2011.

I have created some formulae’s next to the download where it has marked in G1 to G15000 where the Date of Birth matches on the Download to the summary sheet with a simple M. So my download has now marked all entries with the date of birth 12/07/2011 with a M

I can now use filters to get any entries which are coming up with M and which matched the Date of Birth.

When I use a Macro to copy these entries into the summary sheet it reads that the range of where the entries are on the spreadsheet.

Range("B48:G9420").Select
Selection.Copy

What I want is a formulae to be placed in Macro to select the whole range of the download so therefore by the push of a button by the user they can put their date in and it goes into the download marks the entries it has found and copy into the summary sheet? I don’t want the macro to always read

Range("B48:G9420").Select
Selection.Copy

I hope this makes sense?

Thanks

Simon

 

[Desu Nota from Columbus]

I need the names of the Worksheets.

Worksheet 1 where the Date of Birth is entered into A1 is named _________?

Worksheet 2 where I presume all the data is to be filtered is named ________?

Which columns are Client ID, Name, Surname, Post Code and Date of Birth?

And where exactly do you want the data pasted? Worksheet name and Range?

 

[Desu Nota from Columbus]

Code will look something like this:

Sub copydesired()
Dim lrow as Long
Dim r as Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

lrow = Range("B" & rows.Count).End(xlUp).Row
For r = 1 to lrow  
   If Range("A1").Value = Cells(r, "COLUMN OF BIRTHDAYS").Value Then
          Rows(r).Copy 
          Sheets("SHEET WHERE DATA IS PASTED").Range("A" & r).Paste
   End if
Next r

With Sheets("SHEET WHERE DATA IS PASTED")
     For r = lrow to 1 Step -1
             If ISEMPTY(Range("A" & r).Value) = True Then 
                   .Rows(r).Delete
             End If
     Next r
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

 

[midfieldgeneral11]

A1 - is called Lookup

Download is called Download

The columns are the following: Client ID is B, Name is C, Surname is D, Post Code is E and Date of Birth is F. The formulae M is in Column G

In a summary table which range is dob1.

This is a table which has exactly the same fields as above in the same order and the range is B11 to F11 and goes down to B17 to F17

Hope that helps.

If the summary above could look into the Download sheet and see where is a MATCH on the Date of Birth and bring back all the entries that would be great. But I know Excel always only bring back the first one it finds or is therea formulae that brings back more than one item instead of this copying and pasting?

i hope you can help as i am stuck!!

Cheers

For help

Simon

 

[midfieldgeneral11]

Hope you can help me with information I have passed to you!!

I am not good at this kind of macro!

 

[Desu Nota from Columbus]

Sub copydesired()
Dim lrow as Long
Dim r as Long

Application.DisplayAlerts = False
Application.ScreenUpdating = False

With Sheets("Lookup")
lrow = Range("B" & rows.Count).End(xlUp).Row
For r = 1 to lrow  
   If Range("A1").Value = Cells(r, "F").Value Then
          .Rows(r).Copy 
          .Sheets("Download").Range("B" & r).Paste
   End if
Next r
End With

With Sheets("Download")
     For r = lrow to 1 Step -1
             If ISEMPTY(Range("B" & r).Value) = True Then 
                   .Rows(r).Delete
             End If
     Next r
End With

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

This is the best stab I could make from the information given to me.

You are still not very clear with your direction. Please answer my individual questions from my previous post.

I need the names of the Worksheets.

Worksheet where the Date of Birth is entered into A1 is named ?

------Answer: ___________________________

Worksheet where I the data is to be filtered is named?

------Answer: _____________________________

Worksheet where data is to be pasted? What is the first cell where data should be placed?

------Answer:_____________________________________________

Which columns are Client ID, Name, Surname, Post Code and Date of Birth? (solved)