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

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
110
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,601
Office Version
  1. 365
Platform
  1. Windows
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

VBA Code:
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

1613872899853.png
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

PMRetired2012

Board Regular
Joined
Aug 6, 2019
Messages
110
Sure. Keep that existing macro and put this code in the ThisWorkbook module in the vba window

VBA Code:
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

Board Regular
Joined
Aug 6, 2019
Messages
110

ADVERTISEMENT

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

Board Regular
Joined
Aug 6, 2019
Messages
110
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
 

Forum statistics

Threads
1,141,298
Messages
5,705,565
Members
421,399
Latest member
hjweiss00

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top