Automatically transfer data from one sheet to another in the next available row with VBA code

[atari]

I need help!! I am awesome at formulas but I am still not familiar with VBA language. I need some help building a code that will automatically move certain data to another tab once something is added into a specific cell.

In my sheet, data will be entered in columns B and C in several sections on the Budget tab. I would like to be able to have people mark "Yes" into column D to "Add to 5-Yr Budget?", then have the data in B-C automatically move over to the next available row in the corresponding section on the "5-Year" tab. Please let me know if you need to see the actual Excel sheet.

I cannot seem to be able to upload an image of the tabs I am working in. It keeps telling me the file is too big even when I save as JEPG or PNG. I wouldn't mind sending via private message if that's allowed. I would love to show the example of what I am trying to do.

Any help would be so much greatly appreciated!

 

[atari]

Ok, I think I was able to post the mini-sheet into the test section, but it did not look right on my end. I will try it here as well to see if you can see it.

Budget Test.xlsm
	I
28
Budget

 

[Joe4]

Nope. Looks like you only posted one cell (I28).

That is what the Test Forum is for - to test it out and make sure that you have it working correctly before testing the "good" image to the real thread.

Note this line of the instructions:

Open your workbook and select the range that you want to include in your question, then click the Mini Sheet button in the Capture Range group.

So make sure you select that whole range you want to include before clicking the button.

 

[atari]

I added this in to have someone help with the coding. I copied and pasted the code that Joe4 provided me, but I know I did not do something right. Even the mini-sheet doesn't look right to me.

I need the data on the Budget tab to in cells B:C to transfer over to the 5-Year tab in the next available row within the corresponding sections only if something is entered in column D on the Budget tab.

Budget Test.xlsm
	A	B	C	D	E
1
2		Code / Description	Budget	Add to 5 Yr Budget?
3	Category1
4		Item 1	$ 25,000	Yes
5		Item 2	$ 5,000
6
7
8
9
10	Category2
11		Item 1	$ 15,000
12		Item 2	$ 2,500
13
14
15
16
17	Category3
18		Item 1	$ 1,000
19		Item 2	$ 5,000
20		Item 3	$ 1,200
21
22
23
24
25
Budget

Budget Test.xlsm
	A	B	C	D
1
2		Code / Description	Budget
3	Category1
4
5
6
7
8
9
10	Category2
11
12
13
14
15
16
17	Category3
18
19
20
21
22
23
5-Year

 

[atari]

Got it. Duh... Ok, I added it to the Test thread called Budget Sheet Test if you wouldn't mind taking a look.

 

[Joe4]

Got it. Duh... Ok, I added it to the Test thread called Budget Sheet Test if you wouldn't mind taking a look.

Sorry, that is not quite what I meant. The "Test Here" is simply a playground for you to test out the tools and your posting skills before posting to the actual thread, so your real thread is not jumbled up with a bunch of bad posts/attempts. Once you have it figured out there, then you can use those techniques and post them here. I went ahead and moved your post here, as posts in the "Test Here" forum are only temporary and deleted after 7 days.

OK, that gives a better indication of what you are trying to do. However, the code that we will have to come up with has to be VERY SPECIFIC to your actual structure.
We need to make sure that this is actually your EXACT structure, and not some oversimplified example (otherwise the code I come up with will probably work for your sample data, but not your real data).
So we need to confirm the following things:

1. Are there only/exactly 3 categories, as shown?

2. Do where the rows/categories are found on each sheet match exactly (i.e. "Category1" found in cell A3 on BOTH sheets, "Category2" found in cell A10 on BOTH sheets, etc.)

3. Are there lines for up to EXACTLY 5 lines items under each category (no more/no less)?

 

[atari]

Sorry, that is not quite what I meant. The "Test Here" is simply a playground for you to test out the tools and your posting skills before posting to the actual thread, so your real thread is not jumbled up with a bunch of bad posts/attempts. Once you have it figured out there, then you can use those techniques and post them here. I went ahead and moved your post here, as posts in the "Test Here" forum are only temporary and deleted after 7 days.

OK, that gives a better indication of what you are trying to do. However, the code that we will have to come up with has to be VERY SPECIFIC to your actual structure.
We need to make sure that this is actually your EXACT structure, and not some oversimplified example (otherwise the code I come up with will probably work for your sample data, but not your real data).
So we need to confirm the following things:

1. Are there only/exactly 3 categories, as shown?

2. Do where the rows/categories are found on each sheet match exactly (i.e. "Category1" found in cell A3 on BOTH sheets, "Category2" found in cell A10 on BOTH sheets, etc.)

3. Are there lines for up to EXACTLY 5 lines items under each category (no more/no less)?

Thank you for your response. I'm sorry I have not been as clear as I could have been and didn't quite understand everything you were asking for. Yes, that was a simplified version of my sheet. I was hoping that you could build the structure of the code, then I could go in and manipulate it to meet my needs, but you're right. Let's just get it right.

There are 28 category sections. Each section has 30 rows for potential data aside from the 3rd section called Cap Exp-Clubhouse Redecorating, which has 40 rows. Section rows:
- Section 1 - 17:46
- Section 2 - 50:79
- Section 3 - 83:122
- Section 4 - 126:155
- Section 5 - 159:188
- Section 6 - 192:221
- Section 7 - 225:254
- Section 8 - 258:287
- Section 9 - 291:320
- Section 10 - 324:353
- Section 11 - 357:386
- Section 12 - 390:419
- Section 13 - 424:453
- Section 14 - 457:486
- Section 15 - 490:519
- Section 16 - 523:552
- Section 17 - 556:585
- Section 18 - 589:618
- Section 19 - 623:652
- Section 20 - 656:685
- Section 21 - 689:718
- Section 22 - 722:751
- Section 23 - 755:784
- Section 24 - 788:817
- Section 25 - 821:850
- Section 26 - 854:883
- Section 27 - 887:916
- Section 28 - 919:948

The 5-Year tab was a duplication of the Budget tab with additional columns added in so, yes the sections are identical on each tab. Again, I need the data within Columns B and D moved into the first available rows within the given section, from the Budget tab to the 5-Year tab IF there is something entered in column D on the Budget tab.

I would prefer that column D could have anything entered, like an "X" or a "Yes" but if it makes it easier we can just make it "Yes" and I will add a drop-down so that is the only potential option for data in that column. Here is a mini-sheet shot of the actual sheet:

Template_CapEx Budget Sheet_6_05.25.22.xlsm
	A	B	C	D	E	F	G	H
1
2		*Drop-Down Menu for Cost Codes and Budget Mo.
3		*Each category has extra rows - Right click and 'Un-Hide' if needed
4
5		CAPITAL IMPROVEMENTS
6
7		Total Units:			(PROPERTY NAME)
8		Total CapEx:	$ 1,800
9		Total p/Unit:	$ -			Budget Year:	2023
10
11		Code / Description	Budget	Add to 5 Year Budget?	Comments	Budget Mo.	Pictures?
12
13
14		Non-Recurring Capital Expenses
15		Cap Exp- Fees
16		80000-1100 to 80000-1450
17	1	80000-1300 Sal Cap Exp-General Labor	$ 1,800	Yes
18
19
20
21
47		Sub-Total	$ 1,800
48		Cap Exp- A/C-Compressors/New
49		80000-1500 to 80000-1600
50	2
51
52
53
54
80		Sub-Total	$ -
81		Cap Exp- Clubhouse Redecorating
82		80000-1700 to 80000-2400
83	3
84
85
86
87
123		Sub-Total	$ -
124		Cap Exp- Equipment/Major
125		80000-2500 to 80000-3100
126	4
127
Budget

Cell Formulas
Range	Formula
C8	C8	=C951
C9	C9	=IFERROR(C8/C7,0)
C47,C80	C47	=SUM(C17:C46)
C123	C123	=SUM(C83:C122)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
G9	Expression	=G9=""	text	NO
C7	Expression	=C7=""	text	NO
F126:F155	Expression	=AND($B126<>"",$F126="")	text	NO
C126:D155	Expression	=AND($B126<>"",$C126="")	text	NO
F83:F122	Expression	=AND($B83<>"",$F83="")	text	NO
C83:D122	Expression	=AND($B83<>"",$C83="")	text	NO
F50:F79	Expression	=AND($B50<>"",$F50="")	text	NO
C50:D79	Expression	=AND($B50<>"",$C50="")	text	NO
F17:F46	Expression	=AND($B17<>"",$F17="")	text	NO
C17:D46	Expression	=AND($B17<>"",$C17="")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B17:B46	List	=_Fees
B50:B79	List	=Codes!$D$11:$D$15
B83:B122	List	=Codes!$D$17:$D$28
B126:B127	List	=Codes!$D$30:$D$57
F17:F46	List	=Codes!$O$2:$O$14
F126:F127	List	=Codes!$O$2:$O$14
F83:F122	List	=Codes!$O$2:$O$14
F50:F79	List	=Codes!$O$2:$O$14

Template_CapEx Budget Sheet_6_05.25.22.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		*Drop-Down Menu for Cost Codes and Budget Mo.
3		*Each category has extra rows - Right click and 'Un-Hide' if needed
4
5		5 YEAR CAPEX PLAN
6
7		Total Units:	0							(PROPERTY NAME)
8		Total CapEx:	$ -
9		Total p/Unit:	$ -								5 Year Range:	2024 - 2028
10
11		Code / Description	Budget	Useful Life	Age	Preferred Year	Comments	Recommended Year to Complete					Pictures?
12								2024	2025	2026	2027	2028
13
14		Non-Recurring Capital Expenses
15		Cap Exp- Fees
16		80000-1100 to 80000-1450
17
18
19
20
21
47		Sub-Total	$ -					$ -	$ -	$ -	$ -	$ -
48
49		Cap Exp- A/C-Compressors/New
50		80000-1500 to 80000-1600
51
52
53
54
55
81		Sub-Total	$ -
82
83		Cap Exp- Clubhouse Redecorating
84		80000-1700 to 80000-2400
85
86
87
88
89
125		Sub-Total	$ -
126
127		Cap Exp- Equipment/Major
5-Year

Cell Formulas
Range	Formula
J7	J7	=Budget!E7
C7	C7	=Budget!C7
C8	C8	=C975
C9	C9	=IFERROR(C8/C7,0)
L9	L9	=IF(AA6=1,CONCATENATE(AA5," - ",AB5),CONCATENATE(AA6," - ",AB6))
H12	H12	=IF(AA6=1,AA5,AA6)
I12:L12	I12	=H12+1
H17:L21,H85:L89,H51:L55	H17	=IF($C17="","",IF($AB17=H$12,$C17,0))
H47:L47,C81,C47	H47	=SUM(H17:H46)
D17:D21,D85:D89,D51:D55	D17	=(IF($B17="","",IF(VLOOKUP($B17,Codes!$D$4:$F$474,3,0)="","N/A",VLOOKUP($B17,Codes!$D$4:$F$474,3,0))))
C125	C125	=SUM(C85:C124)

Named Ranges
Name	Refers To	Cells
_Fees	=Codes!$D$4:$D$9	D85:D89, D51:D55, D17:D21
'5-Year'!B_AC	='5-Year'!$B$51	D51
'5-Year'!B_Clubhouse	='5-Year'!$B$85	D85
'5-Year'!B_Fees	='5-Year'!$B$17	D17

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
C7	Expression	=C7=""	text	NO
C17:C46,C51:C80,C85:C124,C129:C158,C163:C192,C197:C226,C231:C260,C265:C294,C299:C328,C333:C362,C367:C396,C401:C430,C435:C464,C469:C498,C503:C532,C537:C566,C571:C600,C605:C634,C639:C668,C673:C702,C707:C736,C741:C770,C775:C804,C809:C838,C843:C872,C877:C906	Expression	=AND($B17<>"",$C17="")	text	NO

Cells with Data Validation
Cell	Allow	Criteria
B17:B46	List	=_Fees
B85:B124	List	=Codes!$D$17:$D$28
B51:B80	List	=Codes!$D$11:$D$15

 

[atari]

I don't know why it's showing the cells with Formulas, Conditional Formatting and Data Validation.... I'm sure you can ignore all that. I just wanted to show you a snippet of how my sheet actually looks.

 

[atari]

Sorry, just re-read what I wrote above and I made a typo. When I said "Again, I need the data within Columns B and D moved into the first available rows within the given section, from the Budget tab to the 5-Year tab IF there is something entered in column D on the Budget tab." I meant that I need the data in Columns B and C copied over, not B and D.

 

[Joe4]

OK, try this code:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim r1 As Long
    Dim r2 As Long
    
    Set rng = Intersect(Target, Range("D:D"))
    
'   Exit sub if no updates made in column D
    If rng Is Nothing Then Exit Sub
    
'   Check column D updated
    For Each cell In rng
'       Check to see if value set to Yes
        If UCase(cell) = "YES" Then
'           Get row to copy from
            r1 = cell.Row
'           Get row to copy to
            If Sheets("Add to 5-Yr Budget").Cells(r1 - 1, "B") <> "" Then
                r2 = r1
            Else
                r2 = Sheets("Add to 5-Yr Budget").Cells(r1, "B").End(xlUp).Row + 1
            End If
'           Copy columns B and C to next available row on "Add to 5-Yr Budget" sheet
            Range(Cells(r1, "B"), Cells(r1, "C")).Copy Sheets("Add to 5-Yr Budget").Cells(r2, "B")
        End If
    Next cell

End Sub

 

[atari]

On the Budget sheet, I have column D asking if they want to "Add to 5 Yr Budget" but the tab itself it just named "5-Year". I updated that in the code you posted and it's giving me the following error. Do I need to make this a module or something?: