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]

Which columns should the data from columns B and C on the Budget sheet be pasted to on the "Add to 5-Yr Budget" sheet?

 

[Joe4]

If you wanted to paste it to the same columns, then the code you need is below.
Note that this MUST be pasted to the "Budget" sheet module in VBA in order for it to work automatically.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
    Set rng = Intersect(Target, Range("D:D"))
    
'   Exit sub if no updated 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
'           Copy columns B and C to next available row on "Add to 5-Yr Budget" sheet
            Range(Cells(cell.Row, "B"), Cells(cell.Row, "C")).Copy _
                Sheets("Add to 5-Yr Budget").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
        End If
    Next cell

End Sub

If you are not pasting to columns B and C on the desintation sheet, but rather some other columns, just update the column reference in this line:

                Sheets("Add to 5-Yr Budget").Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)

to the left-most column you want to paste it to.

 

[atari]

I need to have several sections set up, like Category 1 within cells B5:D10 on the Budget sheet, allowing someone to add "Yes" in column D, then have it transfer what is in B:C to the "5-Year" tab in the next available row within the same section B5:C10. I will also need to set up multiple categories so I would need it to repeat say for example in Category 2 from B15:D15 (adding a "Yes" to column D) transferring over to the next available row on the 5-Year tab in cells B15:C15.

Hope that makes sense...

 

[atari]

I know I'm doing something wrong... I right clicked on the Budget tab and clicked "View Code". I pasted the code you sent and it's not working. Are there any more steps that I'm missing? As well as I know Excel, I am a little brain dead when it comes to code. Is there a way I can send you my sheet so you can take a look at what I'm doing?

 

[Joe4]

It can be a bit frustrating when people leave out important details from the initial post that make the question much more complex that initially presented. One should not try to oversimplify a question for the sake of posting it here. Otherwise, you get replies that perfectly answer the exact question you asked, but don't actually work for you because you didn't give all the necessary details in your original question.

Before I even take a run at this, I would like to see sample data on your Budget sheet, and what the "5-Year" sheet looks like initially, and what it looks like after the updates.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Also, are these there any other sheets involved here? Or are these the only two?

 

[Joe4]

I know I'm doing something wrong... I right clicked on the Budget tab and clicked "View Code". I pasted the code you sent and it's not working. Are there any more steps that I'm missing? As well as I know Excel, I am a little brain dead when it comes to code. Is there a way I can send you my sheet so you can take a look at what I'm doing?

How are you updating the value in column D to trigger the code to run?

The link in my previous post contains information regarding a tool you can use to post sample of your worksheet.
You do NOT need to include 100s of rows of data, usually a dozen or two should be sufficient.

 

[atari]

Column D would be manually entered as "Yes" with a drop-down to make sure no spaces are entered.

Is the 'Test Here' link you posted a place where I can post an example of the Excel sheet? Otherwise perhaps this helps...

"Budget" tab:

Category1 - Cell B5:D15. Manually enter "Yes" to column D and have whatever data from B5:D15 over to the next available row within B5:C15 on the "5-Year" tab.

Category2 - Cell B20:D30. Manually enter "Yes" to column D and have whatever data from B20:D30 over to the next available row within B20:C30 on the "5-Year" tab.

 

[atari]

Can you access this?

https://conammgmt-my.sharepoint.com...NOsx_YoeeEBYUBgeBWJdpz-hrookiK_9lZ0A?e=pTTz37

 

[Joe4]

Column D would be manually entered as "Yes" with a drop-down to make sure no spaces are entered.

So, after inserting the code, did you try changing one of the values in column D to "Yes"?
That is what would trigger it to run.

Is the 'Test Here' link you posted a place where I can post an example of the Excel sheet? Otherwise perhaps this helps...

The "Test Here" link is a place where you can test out posting images using that tool. Once you have it working right, then post it right to this thread.
Please read the instructions in the XL2BB link I provided. That has detailed information on how to use that tool.

Can you access this?

https://conammgmt-my.sharepoint.com...NOsx_YoeeEBYUBgeBWJdpz-hrookiK_9lZ0A?e=pTTz37

No, not from my current location. I cannot download files off of the internet on my work computer (security protocols).