VBA - Filter columns, cut/paste rows new sheet, pivot table

[outis_]

Hi, newbie here. I can record a macro and use it, but don't understand the jargon to edit, and started looking into VBA only last week - totally clueless yet totally hooked.
Sorry if similar questions have been asked before. I learn best when I learn by doing, so I hope someone can help me with this.

Below is a sample dataset that needs to be manipulated, and the steps to be taken.
Would it be possible to streamline this, accommodating dynamic datasets?

Steps to sort data:

• In ‘Prep’ tab, Filter column D ‘DonationType’ for ‘blanks’
• Copy and paste rows into ‘Gift aid’ tab and delete visible rows in ‘Prep’ tab
• In ‘Gift aid’ tab, create a pivot table with GiftAidAmount by Gift date
• In ‘Prep’ tab, filter column D ‘DonationType’ for ‘Account Donation’, ‘Account Voucher’ or ‘Other Matched Giving’
• Copy and paste rows into ‘Data entry’ tab and delete visible rows in ‘Prep’ tab
• In ‘Prep’ tab, filter Column A ‘DonorName’ for text that contains ‘S/O ANON’ or ‘SO ANON’, copy and paste into ‘Data entry’ tab and delete visible rows in ‘Prep’ tab
• Sort ‘Prep’ tab by ‘Surname’ (Column B)

In case it affects the choice of coding, the ordering does not matter, as long as the Prep tab is sorted by surname with relevant rows deleted, and the pivot table is created from the Gift aid tab.

I hope I have managed to explain everything well, but please let me know if I am missing anything.
Any help would be greatly appreciated, thanks.

VBA test.xlsx
	A	B	C	D	E	F
1	DonorName	Surname	DonationAmount	DonationType	GiftAidAmount	Gift date
2	LEAHY	LEAHY	100	S/O	0	01/07/2020
3	Foundation		500	Account Voucher	0	03/07/2020
4	CHAPMAN	CHAPMAN	10	D/D	0	03/07/2020
5	BROWN	BROWN	5	D/D	0	03/07/2020
6	ROBINSON	ROBINSON	25	D/D	0	03/07/2020
7	DAVIES	DAVIES	5	D/D	0	03/07/2020
8	Frank	Frank	25	Account Regular Donation	0	04/07/2020
9	HAIGH	Haigh	5	Account Regular Donation	0	04/07/2020
10	Smith	Smith	10	Account Regular Donation	0	04/07/2020
11	M J AY		5000	Account Voucher	0	04/07/2020
12	MOONEY	MOONEY	0		3	04/07/2020
13	Henderson	Henderson	10	Account Regular Donation	0	07/07/2020
14	S/O ANON - xxx	S/O ANON - xxx	50	S/O	0	07/07/2020
15	GARDNER	GARDNER	10	S/O	0	07/07/2020
16	S/O ANON - yyy	S/O ANON - yyy	15	S/O	0	07/07/2020
17	SO ANON - J C G	SO ANON - J C G	15	S/O	0	07/07/2020
18	STEWART	STEWART	10	S/O	0	09/07/2020
19	OWEN	OWEN	5	S/O	0	15/07/2020
20	Colley	Colley	5	Account Regular Donation	0	16/07/2020
21	GRU	GRUNDY	0		2.5	17/07/2020
22	PELL	PELL	0		1.25	17/07/2020
23	DAVIES	DAVIES	0		4	17/07/2020
24	Purser	Purser	0		1.25	17/07/2020
25	ELLIS	ELLIS	0		0.5	17/07/2020
26	Talbot	Talbot	0		3	18/07/2020
27	Davey	Davey	0		1.25	18/07/2020
28	MOONEY	MOONEY	2	S/O	0	21/07/2020
29	BALDWIN	BALDWIN	3	D/D	0	25/07/2020
30	PELL	PELL	5	D/D	0	25/07/2020
31	DAVIES	DAVIES	5	D/D	0	25/07/2020
32	Gilson C	Gilson	60	Account Regular Donation	0	27/07/2020
33	SO ANON R	SO ANON R	10	S/O	0	28/07/2020
34	Jackson	Jackson	20	Other Matched Giving	0	31/07/2020
Prep

VBA test.xlsx
	A	B	C	D	E	F
1	DonorName	Surname	DonationAmount	DonationType	GiftAidAmount	Gift date
2
3
4
Data entry

VBA test.xlsx
	A	B	C	D	E	F
1	DonorName	Surname	DonationAmount	DonationType	GiftAidAmount	Gift date
2
3
4
Gift aid

 

[Skyybot]

Anyway, feel free to message me with any questions. This is very close to what you want I think. Place it in the Prep Sheet Code Module. One thing I still have to work on is not copying the first row a second time in Date Entry.

Option Explicit

Sub filterDonation()
Application.ScreenUpdating = False
Const ard As String = "Account Regular Donation"
Const av As String = "Account Voucher"
Const omg As String = "Other Matched Giving"
Dim rng As Range, r As Range, i As Integer, lRow As Integer
Dim dSht As Worksheet, gSht As Worksheet, delRows() As Range
Set dSht = ThisWorkbook.Sheets("Data Entry"): Set gSht = ThisWorkbook.Sheets("Gift Aid")
Set rng = Me.UsedRange
rng.AutoFilter 4, "="
i = 1
For Each r In rng.Rows
    If r.Hidden = False Then
        r.Copy gSht.Rows(i)
        ReDim Preserve delRows(i - 1)
        Set delRows(i - 1) = r
        i = i + 1
    End If
Next r
rng.AutoFilter
For i = UBound(delRows) To 1 Step -1
    delRows(i).Delete
Next i
i = 1
rng.AutoFilter 4, Array(ard, av, omg), xlFilterValues
For Each r In rng.Rows
    If r.Hidden = False Then
        r.Copy dSht.Rows(i)
        ReDim Preserve delRows(i - 1)
        Set delRows(i - 1) = r
        i = i + 1
    End If
Next r
rng.AutoFilter
For i = UBound(delRows) To 1 Step -1
    delRows(i).Delete
Next i
i = 1
rng.AutoFilter 1, "SO*", xlOr, "S/O*"
rng.Offset(1, 0).Sort Cells(1, 3), xlAscending
lRow = dSht.UsedRange.Rows.Count + 1
For Each r In rng.Rows
    If r.Hidden = False Then
        r.Copy dSht.Rows(lRow)
        ReDim Preserve delRows(i - 1)
        Set delRows(i - 1) = r
        i = i + 1: lRow = lRow + 1
    End If
Next r
rng.AutoFilter
For i = UBound(delRows) To 1 Step -1
    delRows(i).Delete
Next i
rng.Offset(1, 0).Sort (Cells(1, 2))
rng.AutoFilter
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub

 

[outis_]

Hello @Skyybot , thank you for your replies and for explaining DIM/SET etc. I sort of get it, but I guess understanding also comes with practice and familiarity.

I tried your code on the sample dataset I posted. It came up with an error for me - 'Invalid use of Me Keyword'.
So, I changed it to this, and it worked - not that I fully understand what I'm doing, mind you!

Set rng = ThisWorkbook.Sheets("Prep").UsedRange

To fully test it, I edited your code for the complete dataset and.... now I have more questions! (I wasn't sure if you meant message you privately - I did have a look on this forum to see if it is possible, but couldn't find out how, so I'll just continue this thread. But please feel free to stop replying at any point. You have already helped me enormously - I also really enjoyed the little tasks that you asked me to try, they were fun - thanks for that!)

Just to explain what I've done -
I've saved your code as a separate Macro (this is the second macro I am trying to run on this data)
When I work on my dataset, I run the first macro. Then when I try to run your one, I get 'Run-time error '9': Subscript out of range' and this is the line highlighted:

However! If, in the dataset I run the first macro, then open VBE, insert Module, and copy and paste the code, it works.
So, my question is: how can I make your code run as a macro without opening VBE and inserting Module? The coding is the same in both instances, so it seems to me that it has something to do with where I am applying the code from...

Hope this makes sense, but let me know if my question is not very clear. but again, please don't feel you need to keep replying. The code you posted above is already of great help, and I will use it as a model to learn from (and hopefully adapt it for other datasets I have). I'm actually going back to university on Wednesday, so that's why I've been trying to learn VBA in attempt to automate my job (as much as I can), because I really don't know how I am going to manage the workload!

Thanks again @Skyybot , and greetings from rainy England.

 

[Skyybot]

@outis_ , I imagine the keywords are throwing an error because of code location. If the code is in a standard module Me and ThisWorkbook won't work. You will have to declare explicitly.