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!

 

[Joe4]

What is the exact error message?
Are you sure that the name of the sheet is EXACTLY "5-Year"? Something as simple as an extra space at the beginning or end will cause an error.

 

[atari]

Oh my gosh!!!! I think it may have needed time to refresh or something. It's working now! YOU ARE AMAZING! Thank you so much for being so patient with me and so helpful. This is HUGE!

 

[Joe4]

You are welcome.
Glad we got it all sorted out.

 

[atari]

Hi again! I was just doing some testing on my sheet and something seems to be going on that wasn't happening last week. When I add in items to more than one category and choose "Yes" to have the item moved to the corresponding section on the 5-Year tab, it seems to be messing up the formatting and removing some of my other formulas in there. You'll see on this mini-sheet, the item in the 1st category moved over perfectly, but the items in the 2nd and 3rd category got messed up. Can you take a look and see what may have happened?

Template_CapEx Budget Sheet_6_05.25.22.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
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	80000-1100 Sal Cap Exp-Const Superintendent	$ 5,000	10				$ 5,000	$ -	$ -	$ -	$ -
18
19
20
21
47	Sub-Total	$ 5,000					$ 5,000	$ -	$ -	$ -	$ -
48
49	Cap Exp- A/C-Compressors/New
50	80000-1600 A/C-Compressor Casings	$ 2,500
51	80000-1600 A/C-Compressors	$ 1,500	N/A				$ 1,500	$ -	$ -	$ -	$ -
52
53
54
55
81	Sub-Total	$ 1,500
82	80000-2300 Business Center	$ 2,000
83	Cap Exp- Clubhouse Redecorating
84	80000-2200 Cap Exp-Chimney Sweep	$ 1,500
85
86
87
88
89
125	Sub-Total	$ -
5-Year

Cell Formulas
Range	Formula
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
C84	Expression	=AND($B84<>"",$C84="")	text	NO
C82	Expression	=AND($B82<>"",$C82="")	text	NO
C51	Expression	=AND($B51<>"",$C51="")	text	NO
C50	Expression	=AND($B50<>"",$C50="")	text	NO
C17	Expression	=AND($B17<>"",$C17="")	text	NO
C18	Expression	=AND($B18<>"",$C18="")	text	NO
C19:C46,C52: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,C878:C906	Expression	=AND($B19<>"",$C19="")	text	NO

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

 

[Joe4]

I am not seeing what the issue is.
It might be helpful to post before and after images of BOTH sheets, so I can see exactly which rows are supposed to be updated and what is happening on the other sheet.

 

[atari]

So, in the first section for “Cap Exp Fees” the data came over perfectly and all the formatting and formulas remained intact.



In the 2nd section for “Cap Exp- A/C-Compressors/New”, the data came over in cells B50:C51 but the formatting in cells D50:M50 got removed with some of my formulas.



When I tried adding 2 items to the 3rd section, the first item I chose “Business Center, showed up above the 3rd section itself (Clubhouse Redecorating), then the 2nd item did the same thing as the 2nd section where it cleared out the formatting/formulas in cells D84:M84.



Here is a mini-sheet of what it looks like when I manually enter everything:

Template_CapEx Budget Sheet_6_05.25.22.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M
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	80000-1100 Sal Cap Exp-Const Superintendent	$ 5,000	10				$ 5,000	$ -	$ -	$ -	$ -
18
19
20
21
47	Sub-Total	$ 5,000					$ 5,000	$ -	$ -	$ -	$ -
48
49	Cap Exp- A/C-Compressors/New
50	80000-1500 to 80000-1600
51	80000-1600 A/C-Compressor Casings	$ 2,500	N/A				$ 2,500	$ -	$ -	$ -	$ -
52	80000-1600 A/C-Compressors	$ 1,500	N/A				$ 1,500	$ -	$ -	$ -	$ -
53
54
55
81	Sub-Total	$ 4,000
82
83	Cap Exp- Clubhouse Redecorating
84	80000-1700 to 80000-2400
85	80000-2300 Business Center	$ 2,000	N/A				$ 2,000	$ -	$ -	$ -	$ -
86	80000-2200 Cap Exp-Chimney Sweep	$ 1,500	N/A				$ 1,500	$ -	$ -	$ -	$ -
87
88
89
125	Sub-Total	$ 3,500
5-Year

Cell Formulas
Range	Formula
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
C17	Expression	=AND($B17<>"",$C17="")	text	NO
C51	Expression	=AND($B51<>"",$C51="")	text	NO
C18	Expression	=AND($B18<>"",$C18="")	text	NO
C19:C46,C52: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,C878:C906	Expression	=AND($B19<>"",$C19="")	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

 

[Joe4]

In the 2nd section for “Cap Exp- A/C-Compressors/New”, the data came over in cells B50:C51 but the formatting in cells D50:M50 got removed with some of my formulas.



When I tried adding 2 items to the 3rd section, the first item I chose “Business Center, showed up above the 3rd section itself (Clubhouse Redecorating), then the 2nd item did the same thing as the 2nd section where it cleared out the formatting/formulas in cells D84:M84.

I don't see how that is possible from the code I provided. The code I provided is only copying over columns B and C.
So unless you changed my code, I suspect you have something else going on, maybe some other code updating things.
Do you have any code in the "Add to 5-Yr Budget" sheet module?

 

[atari]

Nope. As I'm sure you could tell, I am new to code so nothing has been added to any other tab but the main "Budget" tab. I just went and double checked to make sure. The only thing I adjusted in your code was the name of the tab from "Add to 5-Yr" as I think you wrote it to "5-Year" which is the specific name of the tab. This is the code right now:


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("5-Year").Cells(r1 - 1, "B") <> "" Then
r2 = r1
Else
r2 = Sheets("5-Year").Cells(r1, "B").End(xlUp).Row + 1
End If
' Copy columns B and C to next available row on "5-Year" sheet
Range(Cells(r1, "B"), Cells(r1, "C")).Copy Sheets("5-Year").Cells(r2, "B")
End If
Next cell

End Sub

 

[Joe4]

So, you are absolutely sure that there is no code in VBA if you go to the "5-Year" sheet module in the VB Editor?

If so, then I think I would have to have access to your file to try to figure out what is going on.
If you could upload a copy to a file sharing site and provide a link, I can try to analyze it.
Just be sure to remove any sensitive data first.

 

[atari]

Absolutely. Where should I send the link?