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]

Hi @outis_ , and welcome. I too got hooked on VBA years ago and am STILL learning. It's great! Note that VBA's built-in Object Browser (F2) and Microsoft's Online Office VBA Reference library are wonderful places to learn. When getting started it's best to lean proper nomenclature and "Best Practices". Again, I am no expert, and there are many on this forum who know more than I and will hopefully correct me if I point you in the wrong direction.
First - Nomenclature: When you create, or open, an Excel Spreadsheet file it is call a Workbook. Each "Tab" in that Workbook are called "Sheets". Currently, there are two types of Sheets: Worksheets and Charts [Difference between Sheets and Worksheets in VBA - Excel Off The Grid].
Second - Best Practices: I may get some argument here from the people on the Forum (I hope so) but I usually Proper Case my Sheet names. i.e. - "Gift aid" becomes "Gift Aid", or if you want your code to look "fancy" then you could do something like "Gift_Aid".
Now, I'm sure that you've already figured out that to get to the VBA Project Model you can hit Alt+F11 when your Workbook is open. Left click on your Workbook and Insert a Module. The code I submit should be pasted there. Please remember to test all code on a copy of your Workbook because VBA commands can't be undone. Here is a little bit of code to see how debugging works. Once you've pasted it to your Module, put your cursor anywhere within the Sub and End Sub and hit F8. This will go through each line of code every time you hit F8. Make sure that you have your Locals window open (File Menu > View > Locals Window) so that you can see all of the variables. It will also help to have the Properties window open as well. If you are past this point, let me know and I'll skip ahead.

Option Explicit
'Make Sub name something that kina defines the operation
Sub pCaseShtName()
' Declare you Variables
Dim wb As Workbook, sht As Worksheet
' Set your Variables
Set wb = Workbooks("VBA test.xlsm")
' Perform an action. In this case I'm going to change all of the Sheet names to Proper Case
For Each sht In wb.Sheets
    sht.Name = StrConv(sht.Name, vbProperCase)
Next sht
End Sub

 

[outis_]

Hello @Skyybot ! Thank you for getting back to me. I cannot even begin to express my excitement over VBA, it is exactly the kind of logical thinking that my busy brain likes to chew over.
I managed to find the Locals Window, and I've followed your instructions - it worked!
I didn't know about VBA's Object Browser - thanks for pointing that out to me. I think I'm still too new to this though, so I'm not really sure what I'm looking at, but I will explore Microsoft's VBA Reference Library.
Thank you for helping me get the basics right, and for your time of course, I really appreciate it.
I eagerly await next instructions!

 

[Skyybot]

@outis_ , play around with the MsgBox funtion of VBA. Also, if you want to display information without a MsgBox, in the VBA Project Model click to view the "Immediate Window". Use Debug.Print method to see the results. See if you can write me a simple Macro that tells me the number of sheets in your Workbook. As an example, here is some code that will tell you the name of the first Sheet in the Workbook. The first code does the MsgBox. The second code make use of the Immediate Window. Don't copy both macros into the same Module as you cannot have the same Sub name twice in one module.

First Code:

Sub shtName()
Dim wb As Workbook
Set wb = Workbooks("VBA test.xlsm")
MsgBox "The name of the first Sheet in this Workbook is " & wb.Sheets(1).Name & ".", vbOKOnly + vbInformation, "Sheet Name"
End Sub

Second Code:

Sub shtName()
Dim wb As Workbook
Set wb = Workbooks("VBA test.xlsm")
Debug.Print "The name of the first Sheet in this Workbook is " & wb.Sheets(1).Name
End Sub

 

[outis_]

@Skyybot VBA's Object Browser is starting to make sense now. And I see the importance of the word 'Sheets' as opposed to tabs. Thanks

Sub shtCount()
Dim wb As Workbook
Set wb = Workbooks("VBA test.xlsm")
MsgBox "Hi Skyybot, the number of Sheets in this Workbook is " & wb.Sheets.Count & ".", vbOKOnly + vbInformation, "Sheet Count"
End Sub

Sub shtCount()
Dim wb As Workbook
Set wb = Workbooks("VBA test.xlsm")
Debug.Print "The number of Sheets in this Workbook is " & wb.Sheets.Count & "."; " Thanks Skyybot!"
Debug.Print "Where will we go next?"
End Sub

 

[Skyybot]

@outis_ , Yeah, I wanted you to get a feel for the Object browser because the placement of your code is important. Plus, there are some built-in methods and properties in ThisWorkbook, and individual Sheet codes that you can make use of. Also, keywords can change. As an example, insert a Module and put this code in it. You will get an error. Then move the code to ThisWorkbook. You will get a different error. Then finally, move it to one of the Sheet modules.

Sub highlightCells()
Me.Cells.Interior.Color = vbYellow
End Sub

See if you can edit the code to work in the Module that you inserted instead of the Sheet code module.

 

[outis_]

Hello @Skyybot , I did manage to find a solution. Here it is below. (Feel free to ignore the green. Thinking through what you are showing me).

'Me works in Sheets because it refers to itself - the Prep Sheet to which the code will be applied?
'In Module and ThisWorksheet Me does not work because the X to which Me refers to is unspecified?
'Need to specify Worksheet...?


Sub highlightCells()

Worksheets("Gift Aid").Cells.Interior.Color = vbYellow

End Sub

I wasn't very satisfied with this solution. It didn't look like the way you wrote your codes.

I found another one.

Sub highlightCells()
Dim Wb As Workbook
Set Wb = Workbooks("VBA test.xlsm")

Wb.Sheets("Gift Aid").Cells.Interior.Color = RGB(255, 153, 204)

MsgBox "Actually Skyybot, I prefer the colour Green..."
End Sub

But now I'm puzzled. Both solutions work - for me, at least. I realise I don't fully understand the importance of 'Dim...As' and 'Set...=' yet. (Feeling stupid. So glad I can hide behind a screen).
And thank you, I am learning so much.

Sub highlightRange()

Dim Wb As Workbook
Set Wb = ThisWorkbook

Wb.Sheets("Data Entry").Cells.Interior.Color = RGB(255, 153, 204)

Wb.Sheets("Data Entry").Range("C5:C15,D9:D10,E5:E15,G5:G7,G9:G15").Interior.Color = RGB(0, 128, 0)

End Sub


'Just for fun.

(Also confused as to why 'Set Wb = ThisWorkbook("VBA test.xlsm")' did not work)

 

[Skyybot]

DIM declares your variable (and data type if you know it). You cannot set a value to a variable that hasn't been declared. Set sets the value to the variable that you declared. Not to add confusion at this point but you don't have to use SET on all of your Variables. Only Objects. A Workbook is an Object so, you have to declare a Variable (

Dim wb as Workbook 'wb is the variable

), then set a value to that variable (

Set wb = Workbooks ("Book1.xls")

). But if you are working with, say a String, declare the variable (

Dim x as String

) and then assign the variable a value (

x = wb.Sheets("Sheet1").Range("A1").Value

). Notice you don't have to use the Set keyword.

 

[Skyybot]

Also confused as to why 'Set Wb = ThisWorkbook("VBA test.xlsm")' did not work

ThisWorkbook is the CodeName of the Workbook and a VBA constant. So, ThisWorkbook and Workbooks("VBA test.xlsm") are the same (in this case). ThisWorkbook("VBA test.xlsm") isn't a thing. Will cause an error.

 

[Skyybot]

I wasn't very satisfied with this solution. It didn't look like the way you wrote your codes.

It's not how it looks. It's if it works.