Autofil/Create a sub list from a master list

[ndnsumr]

I have a master list that I use to track all clients, potential and acquired, along with additional details. I want a sublist that is automatically updated/created based on the information added to master list that contains a list of clients based on a certain criteria found in a column in the master list.

The master list has 31 columns, but i am only concerned with 2 to pull my data.
Column A - Client Name
Column G - Effective Date

I want my sublist to list only the Client Name and the Effective Date - ONLY if the Effective date is between 1/1/2010 and 12/31/2010.

I'm IT saavy but i've never worked with macros before. HELP!

 

[hiker95]

ndnsumr,

Welcome to the MrExcel forum.

Please post a screenshot of your sheet(s), what you have and what you expect to achieve, with Excel Jeanie HTML 4 (contains graphic instructions).
http://www.excel-jeanie-html.de/html/hlp_schnell_en.php

Or, you can upload your workbook to www.box.net and provide us with a link to your workbook.

 

[ndnsumr]

Excel Workbook
	A	B	C	D	E	F	G
1	Client Name	Startup/ Takeover (S/T)	Proposal Author (Sam/Jenna)	Fees/Illus Provided (F/I - Date)		Date Svcs Approved	Effective Date of Client
2	Company A	T	Jenna	Fees	06/23/09
3	Company B	T	Jenna	Fees	02/16/10	03/02/10	01/01/10
4	Company C	T	Jenna	Fees	06/25/10
5	Company D	S	Jenna	Fees	02/24/10	02/24/10
6	Company E	T	Jenna	Fees	10/09/07	11/30/07	04/01/10
7	Company F	T	Missy	Fees	11/17/09
8	Company G	S	Missy	F/I	12/05/08	12/09/08	12/15/08
9	Company H	S	Sam/Jenna	Illus	08/20/09	09/08/09	09/30/09
10	Company I	S	Jenna	Illus	08/20/08	12/24/08	03/01/10
11	Company J	T	Jenna	Fees	05/15/08	05/19/08	06/01/09
New Plans

Excel Workbook
	A	B
3	Client Name	Effective Date of Client
4	Company B	01/01/10
5	Company E	04/01/10
6	Company I	03/01/10
2010

 

[ndnsumr]

Hello all, I still would really appreciate some help with this. I have yet to find a solution. Thanks!

 

[hiker95]

ndnsumr,


Sample worksheets before the macro:

Excel Workbook
	A	B	C	D	E	F	G
1	Client Name	Startup/ Takeover (S/T)	Proposal Author (Sam/Jenna)	Fees/Illus Provided * * * * *(F/I - Date)		Date Svcs Approved	Effective Date of Client
2	Company A	T	Jenna	Fees	06/23/09
3	Company B	T	Jenna	Fees	02/16/10	03/02/10	01/01/10
4	Company C	T	Jenna	Fees	06/25/10
5	Company D	S	Jenna	Fees	02/24/10	02/24/10
6	Company E	T	Jenna	Fees	10/09/07	11/30/07	04/01/10
7	Company F	T	Missy	Fees	11/17/09
8	Company G	S	Missy	F/I	12/05/08	12/09/08	12/15/08
9	Company H	S	Sam/Jenna	Illus	08/20/09	09/08/09	09/30/09
10	Company I	S	Jenna	Illus	08/20/08	12/24/08	03/01/10
11	Company J	T	Jenna	Fees	05/15/08	05/19/08	06/01/09
12
New Plans

Excel Workbook
	A	B
3	Client Name	Effective Date of Client
4
5
6
7
2010

After the macro:

Excel Workbook
	A	B
3	Client Name	Effective Date of Client
4	Company B	01/01/10
5	Company E	04/01/10
6	Company I	03/01/10
7
2010

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Option Explicit
Sub GetClients()
' hiker95, 10/12/2010, ME477573
Dim w1 As Worksheet, w2010 As Worksheet
Dim sDate As Date, eDate As Date
Dim LRN As Long, LR2010 As Long, AFCnt As Long
Application.ScreenUpdating = False
Set w1 = Worksheets("New Plans")
Set w2010 = Worksheets("2010")
sDate = "01/01/10"
eDate = "12/31/10"
w1.AutoFilterMode = False
LRN = w1.Cells(Rows.Count, 1).End(xlUp).Row
With w1.Range("G1:G" & LRN)
  .AutoFilter
  .AutoFilter Field:=1, Criteria1:=">=" & sDate, Operator:=xlAnd, Criteria2:="<=" & eDate
  AFCnt = Application.Subtotal(103, w1.Columns(7))
  If AFCnt > 1 Then
    LR2010 = w2010.Cells(Rows.Count, 1).End(xlUp).Row
    If LR2010 > 3 Then w2010.Range("A4:B" & LR2010).ClearContents
    w1.Range("A1").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy w2010.Range("A4")
    w1.Range("G1").Offset(1).Resize(.Rows.Count - 1).SpecialCells(12).Copy w2010.Range("B4")
    .AutoFilter
  Else
    .AutoFilter
    w1.Activate
    Application.ScreenUpdating = True
    MsgBox "There are no dates in column G that match your search criteria - macro terminated!"
    Exit Sub
  End If
End With
w2010.Activate
Application.ScreenUpdating = True
End Sub

Then run the GetClients macro.


Each time you run the macro, if there is any data on worksheet 2010 below row 3 in columns A and B, the data will be cleared.

If this is not acceptable, then I can adjust the macro.