Separating Table Rows Into Separate Tables Based On Cell Value

[ackalbo]

Hello everyone.


I've only recently started to get back into learning VBA after a little absence and with my first project I fear I may have bitten off more than I can chew.


I have multiple managers each responsible for multiple employees. The company pay the employees licensing requirements and once working, the company then deducts a certain amount from salaries until the licensing requirement has been paid back.


Weekly, the company manually provides a basic weekly breakdown to all Managers so they are aware of the payment history and outstanding balances of their respective employees. This is currently being done manually by exporting the report from Quicken to Excel Compatible Text file then opening the file into Excel 2010 and manually resizing columns, deleting irrelevant columns, sorting the employees by ID number into their respective manager (in ID order then date) and then sub-totalling each employee.


For Example:

I would like to go from this


Excel 2010

	A	B	C	D	E	F	G	H
1	Cash Advances
2
3	11/26/2013 through 02/28/2015
4
5		Date	Account	Num	Description	Memo	Category	Amount
6
7		Gea						-1,650.00
8		2/5/2015	Cash	Cris	Gea	License	Cash Advance	-1,650.00
9		Gemma						-680
10		2/9/2015	LPOP Cash	6002	Gemma	License	Cash Advance	-1,200.00
11		2/19/2015	LPOP Cash	6002	Gemma	License Balance P680	Cash Advance	520
12		Jocelyn						-1,120.00
13		2/9/2015	Cash	6100	Jocelyn	License	Cash Advance	-1,250.00
14		2/19/2015	LPOP Cash	6100	Jocelyn	License Balance P1120	Cash Advance	130
15		Laila						-1,220.00
16		2/12/2015	Cash	6005	Laila	License	Cash Advance	-1,220.00
17		Marettie						-1,250.00
18		2/9/2015	Cash	6003	Marettie	License	Cash Advance	-1,250.00
19		Marilyn						-1,000.00
20		2/9/2015	LPOP Cash	6001	Marilyn	License	Cash Advance	-1,650.00
21		2/19/2015	LPOP Cash	6001	Marilyn	License Balance P1000	Cash Advance	650
22		Monica						-1,620.00
23		2/10/2015	Cash	2067	Monica	License	Cash Advance	-1,620.00
24		Pamela						-1,420.00
25		1/17/2015	Cash	3071	Pamela	License	Cash Advance	-1,650.00
26		2/19/2015	LPOP Cash	3071	Pamela	License Balance P1420	Cash Advance	230
27		Renalyn						-1,280.00
28		2/8/2015	Cash	1036	Renalyn	License	Cash Advance	-1,100.00
29		2/14/2015	LPOP Cash	1036	Renalyn	NSO	Cash Advance	-400
30		2/19/2015	LPOP Cash	1036	Renalyn	License Balance P1280	Cash Advance	220
31		Rose Ann						-1,650.00
32		2/5/2015	Cash	Cris	Rose Ann	License	Cash Advance	-1,650.00
33		Roshel						-1,400.00
34		2/9/2015	Cash	6004	Roshel	License	Cash Advance	-1,650.00
35		2/19/2015	LPOP Cash	6004	Roshel	License Balance P1400	Cash Advance	250
36		OVERALL TOTAL						########

<tbody>
</tbody>

Cash Advances 19th February 201​

To This:


Excel 2010

	B	C	D	E	F
2	Date	Num	Name	Memo	Amount
3	2/8/2015	1036	Renalyn	License	-1,100.00
4	2/14/2015	1036	Renalyn	NSO	-400
5	2/19/2015	1036	Renalyn	License Balance P1280	220
6			Renalyn Total		-1280
7
8	2/9/2015	6002	Gemma	License	-1,200.00
9	2/19/2015	6002	Gemma	License Balance P680	520
10			Gemma Total		-680
11
12	2/9/2015	6100	Jocelyn	License	-1,250.00
13	2/19/2015	6100	Jocelyn	License Balance P1120	130
14			Jocelyn Total		-1120
15
16	2/12/2015	6005	Laila	License	-1,220.00
17			Laila Total		-1,220.00
18
19	2/9/2015	6003	Marettie	License	-1,250.00
20			Marettie Total		-1,250.00
21
22	2/9/2015	6001	Marilyn	License	-1,650.00
23	2/19/2015	6001	Marilyn	License Balance P1000	650
24			Marilyn Total		-1000
25
26	2/9/2015	6004	Roshel	License	-1,650.00
27	2/19/2015	6004	Roshel	License Balance P1400	250
28			Roshel Total		-1400
29
30			Grand Total		-7950
31
32
33
34	Date	Num	Name	Memo	Amount
35	2/5/2015	Cris	Rose Ann	License	-1,650.00
36			Rose Ann Total		-1650
37	2/5/2015	Cris	Gea	License	-1,650.00
38			Gea Total		-1650
39	1/17/2015	3071	Pamela	License	-1,650.00
40	2/19/2015	3071	Pamela	License Balance P1420	230
41			Pamela Total		-1420
42
43			Grand Total		-4720
44
45	Date	Num	Name	Memo	Amount
46	2/10/2015	2067	Monica	License	-1,620.00
47			Monica Total		-1620
48
49			Grand Total		-1620

<tbody>
</tbody>

Cash Advances 19th February 201​

Each manager's employees are distinguished by their own unique number. For instance - Manager A's employees start at 2001, Manager B's employees start at 3001 etc.


I have managed to write the following VBA code to open the original text file, delete unnecessary columns and rows and resize:

Option Explicit

Sub CashAdvReport()


Dim LastRow As Integer
Dim RowNum As Integer


Workbooks.OpenText Filename:="C:\Users\BackupWindows7Ult\Desktop\Cash Advance Test.TXT", DataType:=xlDelimited, Tab:=True


LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count


Columns(1).EntireColumn.Delete


For RowNum = 1 To LastRow
    If Cells(RowNum, 3).Value = "" Then
        Rows(RowNum).EntireRow.Delete
    End If
Next RowNum


Rows("1:2").EntireRow.Delete
Rows(2).EntireRow.Delete
Columns(2).EntireColumn.Delete
Columns(5).EntireColumn.Delete
Worksheets(1).Range("A:E").EntireColumn.AutoFit
Range("B1").Value = "ID"
Range("C1").Value = "Name"
Columns("A:E").Sort key1:=Range("B2"), order1:=xlAscending, Header:=xlYes


LastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count


Range("A1").Resize(, 5).Select
Selection.Font.Bold = True
Selection.Font.Italic = True
Selection.Interior.Color = RGB(156, 156, 156)
Range("A1:E1").HorizontalAlignment = xlCenter
Range("A1:E1").Copy Cells(LastRow + 3, 1)

End Sub

The above code gives me the following result


Excel 2010

	A	B	C	D	E
1	Date	ID	Name	Memo	Amount
2	2/8/2015	1036	Renalyn	License	-1,100.00
3	2/14/2015	1036	Renalyn	NSO	-400
4	2/19/2015	1036	Renalyn	License Balance P1280	220
5	2/10/2015	2067	Monica	License	-1,620.00
6	1/17/2015	3071	Pamela	License	-1,650.00
7	2/19/2015	3071	Pamela	License Balance P1420	230
8	2/9/2015	6001	Marilyn	License	-1,650.00
9	2/19/2015	6001	Marilyn	License Balance P1000	650
10	2/9/2015	6002	Gemma	License	-1,200.00
11	2/19/2015	6002	Gemma	License Balance P680	520
12	2/9/2015	6003	Marettie	License	-1,250.00
13	2/9/2015	6004	Roshel	License	-1,650.00
14	2/19/2015	6004	Roshel	License Balance P1400	250
15	2/12/2015	6005	Laila	License	-1,220.00
16	2/9/2015	6100	Jocelyn	License	-1,250.00
17	2/19/2015	6100	Jocelyn	License Balance P1120	130
18	2/5/2015	Cris	Gea	License	-1,650.00
19	2/5/2015	Cris	Rose Ann	License	-1,650.00
20
21
22	Date	ID	Name	Memo	Amount

<tbody>
</tbody>

Cash Advance Test​

Now I've hit a brick wall I'm afraid - I can't work out (It may be beyond my experience) how to go through the above list and separate out employees according to there ID range and move them to a separate table for each manager (let alone sub-totaling each employee).


Any pointers or advice would be greatly received and appreciated please guys & gals.