Selecting Dynamic Range in Excel

[KiwiGrue]

I am developing a macro to select financial data from a monthly P&L account which may have differing rows of relevant information each month - see attached example. I need to copy paste to another workbook (see example Financial Data sheet). I am struggling to develop code to identify the information - for example compare Trading Income for the two months with different Income fields.

Any assistance would be appreciated.

Cheers

Form. Limited Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Profit and Loss			Profit and Loss
2	Form Limited			Form Limited
3	For the month ended 31 August 2021			For the month ended 30 September 2021		Information for each month is always in columns A and B
4
5	Account	Aug 2021		Account	Sep 2021
6
7	Trading Income			Trading Income
8	ACC Income	4,575.00		ACC Income	5,250.00
9	Interest Income	250.00		Other Revenue	175.00
10	Other Revenue	3,000.00		Physiotherapy Income	8,775.00
11	Physiotherapy Income	7,750.00		Total Trading Income	14,200.00
12	Total Trading Income	15,575.00
13				Cost of Sales
14	Cost of Sales			Purchases	75.00
15	Purchases	150.00		Subcontractors	7,650.00
16	Subcontractors	8,750.00		Total Cost of Sales	7,725.00
17	Total Cost of Sales	8,900.00
18				Gross Profit	6,475.00
19	Gross Profit	6,675.00
20				Other Income
21	Operating Expenses			Covid-19 Subsidy	6,500.00
22	Administration Costs	100.00		Total Other Income	6,500.00
23	Advertising	125.00
24	Bank Fees	52.00		Operating Expenses
25	Cleaning	125.00		Administration Costs	100.00
26	Computer & Software Expenses	150.00		Advertising	125.00
27	Consulting & Accounting	235.00		Bank Fees	52.00
28	Eftpos Expenses	50.00		Cleaning	125.00
29	Entertainment - Non deductible	12.00		Computer & Software Expenses	150.00
30	Office Expenses	35.00		Consulting & Accounting	235.00
31	Rent	1,750.00		Eftpos Expenses	50.00
32	Salaries - Shareholder (PAYE deducted)	2,500.00		General Expenses	85.00
33	Staff Welfare	35.00		Office Expenses	35.00
34	Subscriptions	125.00		Rent	1,750.00
35	Training	35.00		Salaries - Shareholder (PAYE deducted)	2,500.00
36	Uniforms	125.00		Subscriptions	35.00
37	Total Operating Expenses	5,454.00		Training	125.00
38				Uniforms	35.00
39	Net Profit	1,221.00		Total Operating Expenses	5,402.00
40
41				Net Profit	7,573.00
42
43
Profit and Loss

Cell Formulas
Range	Formula
E11	E11	=SUM(E8:E10)
B12	B12	=SUM(B8:B11)
E16,B17	E16	=SUM(E14:E15)
E18,B19	E18	=(E11 - E16)
E22	E22	=E21
B37,E39	B37	=SUM(B22:B36)
B39	B39	=((B19 + 0) - B37)
E41	E41	=((E18 + E22) - E39)

 

[KiwiGrue]

I have managed to write a couple of VBA routines that can identify the variable cell range (see one below) but I have not been able to use the cell addresses to establish the range to copy and paste (to another worksheet).

Sub UseLookAt()

Dim cell1 As Range
Dim cell2 As Range

Set cell1 = Range("A1:A38").Find("Trading Income", lookat:=xlWhole).Offset(1, 0)
Set cell2 = Range("A1:A38").Find("Total Trading Income", lookat:=xlWhole).Offset(-1, 0)

'Debug.Print cell1.Address

'Debug.Print cell2.Address

End Sub

This sub gives me $A$8 and $A$11 in this example - how do access these then use them to copy A8:B11 for pasting?

Appreciate any assistance

Cheers

 

[Alex Blakenburg]

Try this

 Range(cell1, cell2.Offset(0, 1)).Copy Destination:=Range(Output_Location)

 

[KiwiGrue]

Try this

 Range(cell1, cell2.Offset(0, 1)).Copy Destination:=Range(Output_Location)

 

[KiwiGrue]

Great it works thanks for your assistance Alex!

Cheers
Alistair

 

[Alex Blakenburg]

Thanks for letting me know. Glad I could help.