VBA to keep track of units sold per year

[Rdhostetler]

hello
i am working on a project where i have an order entry system and i need vba code to look at the qty of the order on sheet1 range a1 for example and then go over to sheet2 (which is my sheet that keeps track of my totals) and find the current year in column A and then use that row and offset 1 cell to the right (column B) and increase the current value of that cell by the amount of the value of Cell A1 in sheet1. if it does not find the current year in column A in Sheet2 then it needs to add the current year in the first empty cell in column A.

my biggest problem that i cant figure out is how to get it to lookup the current year and use the correct row for the data or create a new row with the current year...

hope this all makes sense!

is there anyone that has any ideas on how to do this?

i really appreciate any help you can give!

thanks

 

[My Aswer Is This]

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


This script runs when you modify a value in Sheet(1).Range("A1") manually
This script will not run if the value in Range("A1") is modified as a result of a formula.

Put this script in Sheet(1)

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/15/2019  7:48:16 PM  EST
If Target.Address = "$A$1" Then
    Dim SearchString As String
    Dim SearchRange As Range
    SearchString = Year(Date)
    Dim lastrow As Long
    lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRange = Sheets(2).Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        Select Case True
            Case SearchRange Is Nothing
                Sheets(2).Cells(lastrow + 1, 1).Value = Year(Date)
                Sheets(2).Cells(lastrow + 1, 2).Value = Target.Value
            Case Else
                SearchRange.Offset(, 1).Value = SearchRange.Offset(, 1).Value + Target.Value
        End Select
End If
End Sub

 

[Rdhostetler]

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


This script runs when you modify a value in Sheet(1).Range("A1") manually
This script will not run if the value in Range("A1") is modified as a result of a formula.

Put this script in Sheet(1)

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  2/15/2019  7:48:16 PM  EST
If Target.Address = "$A$1" Then
    Dim SearchString As String
    Dim SearchRange As Range
    SearchString = Year(Date)
    Dim lastrow As Long
    lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRange = Sheets(2).Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        Select Case True
            Case SearchRange Is Nothing
                Sheets(2).Cells(lastrow + 1, 1).Value = Year(Date)
                Sheets(2).Cells(lastrow + 1, 2).Value = Target.Value
            Case Else
                SearchRange.Offset(, 1).Value = SearchRange.Offset(, 1).Value + Target.Value
        End Select
End If
End Sub

hello
thanks for the quick response! i thought i could adapt your code for my needs but i am struggling with it... it is mostly my fault as i probably didnt include everything i am trying to do... and i used hypothetical ranges assuming that i could adapt it to fit my needs...

first of all this code will be run when a command button is pressed - cmd_close_click() - not when the cell changes and when the command is clicked it needs to loop through multiple sheets and

with sheet 1.range ("T1").value needs to be added to the current value of sheet18 column B in the row that matches the current year
with sheet 6.range ("T1").value needs to beadded to the current value of sheet18 column C in the row that matches the current year
with sheet 7.range ("T1").value needs to be added to the current value of sheet18 column D in the row that matches the current year
with sheet 2.range ("T1").value needs to be added to the current value of sheet18 column E in the row that matches the current year
with sheet 8.range ("T1").value needs to be added to the current value of sheet18 column F in the row that matches the current year
with sheet 15.range ("T1").value needs to be added to the current value of sheet18 column G in the row that matches the current year

as before if the current year is not in the list in column A on sheet18 then it needs to start a new row with the current year...

once again - sorry i couldnt adapt the code! i hope that i explained it well enough this time to where you have a better picture of what is happening...

thanks for the response and i look forward to hearing back from you!

Rodney

 

[My Aswer Is This]

Yes you left out quite a bit.

I for one never like using terms like sheet1 and sheet 12 and sheet14

There are two many ways to refer to sheet names.

Like:

Sheet index 3
Sheet named Sheet("Sheets3")
Or Sheet (3)

I think you should always use terms like Sheet named "George"
Or sheet named "Bob"

And I do not understand the logic of your first post saying A1 but now your saying T1

And another question.

Do you plan to have just one button to run this same script on all sheets or a separate button on each sheet to just run the script on that sheet.

See to help you we nee specific details like this.

 

[Rdhostetler]

Well my first intention was to get the basics of how the code would be for the hypothetical case I had in my first post and then I was hoping to take it and basically adapt it to my specifics instead of having someone write all the code specific to my situation... I was hoping to take up less of your time but for some reason I just wasn’t catching on to how it was structured and therefore couldn’t take it and adapt it which is when I went ahead and posted all the details... sorry about that!

This will be 1 command button to run all the sheets...

Also as far as the sheet naming, I don’t really have a preference either way - I was completely self taught in vba and I used sheet.1 format for most of everything else but if there are benefits one way or another then I am definitely very open to your input... do you need me to post the sheet names?

Thanks so much for your time!

 

[My Aswer Is This]

You said:

do you need me to post the sheet names
Answer is yes:

And need sheet name with button and Sheet18 name where all this data is being entered in.

Is Sheet18 Named Master?
And is button on sheet named master?

Please do not provide sheet names that are not correct and assume you can modify sheet names to meet you needs.
​

 

[My Aswer Is This]

See if you said sheet 1 to sheet 12 copy to sheet 18 that would be easier

But you said sheet

1 6 7 2 8 15

 

[Rdhostetler]

Ok I will get this information to you as soon as I get back in front of that computer...

Thanks

 

[My Aswer Is This]

Well if we are really dealing with sheet:
1 6 7 2 8 15 in that order

Sheet 1 is the first sheet in your workbook
Sheet 6 is the sixth sheet in your workbook and so on I do not need the sheet names

Thy this script

Sub My_Sub()
'Modified  2/17/2019  5:52:04 AM  EST
    Dim ans As Long
    Dim SearchString As String
    Dim SearchRange As Range
    SearchString = Year(Date)
    Dim lastrow As Long
    Dim Del As Variant
    Del = Array(1, 6, 7, 2, 8, 15)
    Dim i As Long
    
    lastrow = Sheets(18).Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRange = Sheets(18).Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        Select Case True
            Case SearchRange Is Nothing
                Sheets(18).Cells(lastrow + 1, 1).Value = Year(Date)
                ans = Sheets(18).Cells(lastrow + 1, 1).Row
                
            Case Else
               ans = SearchRange.Row
        End Select
For i = 0 To 5

With Sheets(18)
    .Cells(ans, i + 2).Value = Sheets(Del(i)).Cells(1, "T").Value + .Cells(ans, i + 2).Value
End With
Next
End Sub

 

[Rdhostetler]

Well if we are really dealing with sheet:
1 6 7 2 8 15 in that order

Sheet 1 is the first sheet in your workbook
Sheet 6 is the sixth sheet in your workbook and so on I do not need the sheet names

Thy this script

Sub My_Sub()
'Modified  2/17/2019  5:52:04 AM  EST
    Dim ans As Long
    Dim SearchString As String
    Dim SearchRange As Range
    SearchString = Year(Date)
    Dim lastrow As Long
    Dim Del As Variant
    Del = Array(1, 6, 7, 2, 8, 15)
    Dim i As Long
    
    lastrow = Sheets(18).Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRange = Sheets(18).Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        Select Case True
            Case SearchRange Is Nothing
                Sheets(18).Cells(lastrow + 1, 1).Value = Year(Date)
                ans = Sheets(18).Cells(lastrow + 1, 1).Row
                
            Case Else
               ans = SearchRange.Row
        End Select
For i = 0 To 5

With Sheets(18)
    .Cells(ans, i + 2).Value = Sheets(Del(i)).Cells(1, "T").Value + .Cells(ans, i + 2).Value
End With
Next
End Sub

HELLO
sorry about taking a while to reply but i spent quite a bit of time yesterday going through the code you wrote and trying to figure out exactly what steps you are taking in what sequence because it does not seem to be working right now... i figured some of it out but not all of it. i went ahead and set up a command button that is linked directly to this sub (i also tried running this sub through the debugger and by calling this sub from another sub neither of which produced any results) and the computer acts like it is running some kind of code but i am not seeing anything happen as far as results... just to reiterate, here is what it is supposed to be doing:

---

when the button is clicked and the sub starts it needs to look at:

sheet 1 (DH DNA) range ("T1") and whatever value that it finds in that cell then needs to be added to the current value of sheet18 (Unit Tracker) column B in the row that matches the current year
sheet 6 (HS DNA) range ("T1") and whatever value that it finds in that cell then needs to be added to the current value of sheet18 (Unit Tracker) column C in the row that matches the current year
sheet 7 (PW DNA) range ("T1") and whatever value that it finds in that cell then needs to be added to the current value of sheet18 (Unit Tracker) column D in the row that matches the current year
sheet 2 (BSMT DNA) range ("T1") and whatever value that it finds in that cell then needs to be added to the current value of sheet18 (Unit Tracker) column E in the row that matches the current year
sheet 8 (3 LITE HS DNA) range ("T1") and whatever value that it finds in that cell then needs to be added to the current value of sheet18 (Unit Tracker) column F in the row that matches the current year
sheet 15 (BM DNA) range ("T1") and whatever value that it finds in that cell then needs to be added to the current value of sheet18 (Unit Tracker) column G in the row that matches the current year

as before if the current year is not in the list in column A on sheet18 (Unit Tracker) then it needs to start a new row with the current year...

---

so for example:
lets say sheet 1 (DH DNA) cell T1 has a value of 5 and sheet 18 (Unit Tracker) cell A2 has a value of 2019 (current year) and sheet 18 (Unit Tracker) cell B2 has a current value of 5

the sub needs to take the value of sheet 1 (DH DNA) cell T1 and (which in this example is 5) and go over to sheet 18 (Unit Tracker) and it will first look at column A and try to find the current year. in this example the current year would be found in cell A2 and then it would stay in that row but shift over to Column B (per instructions above) and add 5 to the current value of that cell and so when it is done the cell in B2 should have a value of 10.

and so forth with each of the sheets listed above...

---

does this make sense? i tried to reword everything and tried to make it even clearer on what i am doing... or maybe this is exactly what the code was supposed to do and there is just a bug in the code? either way it doesnt seem to be working as it is written currently so feel free to let me know if you have any questions or need additional clarifications.

p.s. i used both sheet numbers and sheet names above for your reference

thanks for your time!