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]

Yes I understand all of this:
And my script does exactly that and I have tested it several times.

If you look at my script you will see:

Del = Array(1, 6, 7, 2, 8, 15)

This means the script looks at
Range("T1")

On sheets 1 6 7 2 8 and 15

Now you need to realize this;

If you look at your Tab bar where it shows all the sheet

The tab on the far left is sheet 1
The tab to the right in sheet 2

And so on.

The script is looking at the sheets as you said

And sheet 18 which is the 18th sheet to the right is sheet 18

So you tell me is sheet18 the 18th sheet on the tab bar

And this is the case even if you have hidden sheets.
Do you have hidden sheets?

That why I mention earlier

Sheet named can be miss leading

You may have a sheet name "Sheet1" but that may not be Sheet(1)

Sheets(1) means the sheet on the far Left side on the Tab bar

And they are numbered form left to right

Like

1 then 2 then 3 then 4

Assuming you do not live in a Country like China or Japan where I think things are done differently.

 

[Rdhostetler]

I am going to check some of those sheet names and numbers out in the morning but yes I have a lot of hidden sheets as this is a big project that I have been building and expanding over the last 3 years... I did not realize that you could reference sheets from left to right in the order that they are shown... when I gave you those sheet numbers I was using the sheet numbers that are shown in the vba screen (the actual sheet properties) so they can be referenced as sheet.1 or sheet.6 etc. when you had mentioned that the sheet numbers could be misleading I went back and double checked all the numbers I gave you to make sure I was right on those numbers but I did not catch that you were referring to them in the order that they are displayed so I’m pretty sure that will be where the problem lies... I will let you know in the morning for sure though...

Thank you very much for the clarification!!

 

[My Aswer Is This]

You said:

I did not realize that you could reference sheets from left to right in the order that they are shown.
I never said:

left to right in the order that they are shown.

I sad left to right​

It's the order.

Sheet(1) is always sheet(1) if it is hidden or not hidden

Sheet(16) is always sheet(16) if it is hidden or not hidden

So you need to change my script to the proper sheet number.

Including sheet 18





​

_{^{<strike>
</strike>}}​

 

[My Aswer Is This]

If your going to need to be hiding sheet then you will probable need to use sheet names like this.

In this example I used Master for sheet 18 name.

Modify the names. As seen here marked in Red

I do not have the patience to name them exactly the way you said.

Sub My_Sub()
'Modified  2/19/2019  9:41:33 PM  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("[COLOR=#ff0000]Dad[/COLOR]", "[COLOR=#ff0000]Mom", "Bob", "George", "Stanley", "Julia[/COLOR]")
    Dim i As Long
    
    lastrow = Sheets("[COLOR=#ff0000]Master[/COLOR]").Cells(Rows.Count, "A").End(xlUp).Row
    Set SearchRange = Sheets("[COLOR=#ff0000]Master[/COLOR]").Range("A1:A" & lastrow).Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
        Select Case True
            Case SearchRange Is Nothing
                Sheets("[COLOR=#ff0000]Master[/COLOR]").Cells(lastrow + 1, 1).Value = Year(Date)
                ans = Sheets("[COLOR=#ff0000]Master[/COLOR]").Cells(lastrow + 1, 1).Row
                
            Case Else
               ans = SearchRange.Row
        End Select
For i = 0 To 5

With Sheets("[COLOR=#ff0000]Master[/COLOR]")
    .Cells(ans, i + 2).Value = Sheets(Del(i)).Cells(1, "T").Value + .Cells(ans, i + 2).Value
End With
Next
End Sub

 

[My Aswer Is This]

If your going to use sheet names Like in my previous post see if all works well.
But it may put the data in the wrong column of sheet Master.
But see if it works and if it does we will sort out the proper column see using things like sheet 1 and sheet 2
I was able to have the script to use rational thinking. But if we are using sheet names I may have to modify the code.

A lot of times programmers try and use rational thinking. But why exactly you pick certain sheets and not others and why one sheets T1 results get put in column 4 of sheet 18 and another ones goes into column 6 is not something I see a relationship with.

So I think the way I lade out the sheet numbers and the columns on sheet Master may not work.

Or maybe you can see my logic and work it out

When I used Del("Dad",Mom","Bob","George","Stanley","Julia")

Dad being the first one it's T1 value would go into sheet Master Column B Then Mpm being the second one would go into column C

And on and on.

So if you could put them in that order.

You can use loops like this easily if there is a trend or some logic.
Otherwise you have to spell out each one one line of code at a time.
Not a big problem just takes 6 lines of code in the loop instead of 1

You will see My loop only requires one of code.

Programming can work using some logic or Specific code.

 

[Rdhostetler]

If your going to use sheet names Like in my previous post see if all works well.
But it may put the data in the wrong column of sheet Master.
But see if it works and if it does we will sort out the proper column see using things like sheet 1 and sheet 2
I was able to have the script to use rational thinking. But if we are using sheet names I may have to modify the code.

A lot of times programmers try and use rational thinking. But why exactly you pick certain sheets and not others and why one sheets T1 results get put in column 4 of sheet 18 and another ones goes into column 6 is not something I see a relationship with.

So I think the way I lade out the sheet numbers and the columns on sheet Master may not work.

Or maybe you can see my logic and work it out

When I used Del("Dad",Mom","Bob","George","Stanley","Julia")

Dad being the first one it's T1 value would go into sheet Master Column B Then Mpm being the second one would go into column C

And on and on.

So if you could put them in that order.

You can use loops like this easily if there is a trend or some logic.
Otherwise you have to spell out each one one line of code at a time.
Not a big problem just takes 6 lines of code in the loop instead of 1

You will see My loop only requires one of code.

Programming can work using some logic or Specific code.

Good Morning!
I used that code with the sheet names and bingo it worked perfectly and put everything in the correct order!

You said that you were not understanding why i only pick certain sheets with the T1 value and each sheet goes in a different column - basically this is an order entry system and so the hidden sheets have data that feeds the 6 main sheets that we are referencing with the T1 value - each sheet is for a different model of the product and so on the sheet 18 (Unit Tracker) i have different columns for each model of product and then a simple formula on the last column (column H) that figures the total for all models. (i wish i could get you screenshots - i think it would make a whole lot more sense)

thanks again for your patience! i really appreciate it!!

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Good Morning!
I used that code with the sheet names and bingo it worked perfectly and put everything in the correct order!

You said that you were not understanding why i only pick certain sheets with the T1 value and each sheet goes in a different column - basically this is an order entry system and so the hidden sheets have data that feeds the 6 main sheets that we are referencing with the T1 value - each sheet is for a different model of the product and so on the sheet 18 (Unit Tracker) i have different columns for each model of product and then a simple formula on the last column (column H) that figures the total for all models. (i wish i could get you screenshots - i think it would make a whole lot more sense)

thanks again for your patience! i really appreciate it!!