Extracting data from 2 columns and putting the data in one cell in another worksheet. Those 2 collums come from 12 worksheets.

[PMRetired2012]

What i want to do is:
1. In column D over 12 worksheets i have the words or description of : resale, Labor, utility. depending on what is said the items was.
2. In column E I have a dollar amount listed in those 12 worksheets.
3. There are 15 worksheets in this workbook named ( Jan-Dec, Year to date, option page, New equip repairs)
4. What i want to do is with this information i gave you is: I want to search thru column D of all 12 worksheets and extract the word resale and in column E i want to extract the dollar amount that is connected with the word resale.
5. Then i want to to add all the amounts that are associated with resale over all 12 months and put them in A45 cell on the worksheet named "Year to date".
The problem is i don't know if i use a VLOOKUP formula or i use some sort of macro. I'm just kind of lost on this one.

Dennis

 

[Peter_SSs]

Do you think we could make that code work as im entering the information into each monthly worksheet to show up in the cell A45 on the YTD INFO page as im enter the information without making a button to make that happen?

Sure. Keep that existing macro and put this code in the ThisWorkbook module in the vba window

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If IsDate("1-" & Sh.Name) And Not Intersect(Target, Sh.Range("D:E")) Is Nothing Then
    Application.EnableEvents = False
    Resale_Total_YTD  '<- This is the name of the existing macro
    Application.EnableEvents = True
  End If
End Sub

 

[PMRetired2012]

Sure. Keep that existing macro and put this code in the ThisWorkbook module in the vba window

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
  If IsDate("1-" & Sh.Name) And Not Intersect(Target, Sh.Range("D:E")) Is Nothing Then
    Application.EnableEvents = False
    Resale_Total_YTD  '<- This is the name of the existing macro
    Application.EnableEvents = True
  End If
End Sub

View attachment 32669

OK Peter i want to do someting elese with this code and the code we used before. (The code you gave me before where i need to use a control button to make the macro work) What i want to do is in columns D and E on each worksheet there are other options that were put in those columns. Lets say i want to have Utilities or insurance or any other options that is in colums D and amounts pulled out of collumn E. would i copy the same code that you gave me before and place one in the section of Thisworkbook and create a module for the other code like you did before but only insert which option i wanted to have pulled out and put in the proper place in the new code i copied? or is there a way to use the code you orginally gave me to incorprate the other options? Not sure how clear i was on this but lets give it a try.
Thanks

 

[PMRetired2012]

Peter did you see my last post?

 

[Peter_SSs]

Not sure how clear i was on this ...

Not at all I'm afraid. I have no idea what you are wanting.

 

[PMRetired2012]

Peter,
OK Peter i want to try to do someting elese with this code and the code we used before. (The code you gave me before where i need to use a control button to make the macro work) What i want to do is in columns D and E on each worksheet there are other options that were put in those columns. Lets say i want to have Utilities or insurance or any other options that is in colums D and amounts pulled out of collumn E. would i copy the same code that you gave me before and place one in the section of Thisworkbook and create a module for the other code like you did before but only insert which option i wanted to have pulled out and put in the proper place in the new code i copied? or is there a way to use the code you orginally gave me to incorprate the other options? Not sure how clear i was on this but lets give it a try. (I think you will want to look back to the previous code you helped me with in this thread)
Thanks

 

[PMRetired2012]

Peter did you understand what I was saying in last post? It is very hard for me to explain what I'm trying to say at times lol
Thanks

 

[Peter_SSs]

Peter did you understand what I was saying in last post?

No - it was exactly the same as post #22 that I did not understand.