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

 

[PMRetired2012]

also posted at: https://www.ozgrid.com/forum/index.php?thread-add/75/

 

[Peter_SSs]

also posted at: https://www.ozgrid.com/forum/index.php?thread-add/75/

The link is not working for me.

 

[PMRetired2012]

Try this

Extracting data from 2 columns and putting the data in one cell in another worksheet. Those 2 collums come from 12 worksheets. - OzGrid Free Excel/VBA Help Forum

[xpost] [/xpost] 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.…

www.ozgrid.com

 

[Peter_SSs]

You could try this macro with a copy of your workbook.

Sub Resale_Total_YTD()
  Dim a As Variant
  Dim tot As Double
  Dim i As Long, j As Long
  
  For j = 1 To 12
    With Sheets(MonthName(j, True))
      a = .Range("D2", .Range("E" & Rows.Count).End(xlUp)).Value
      For i = 1 To UBound(a)
        If LCase(a(i, 1)) = "resale" Then tot = tot + a(i, 2)
      Next i
    End With
  Next j
  Sheets("Year to Date").Range("A45").Value = tot
End Sub

 

[PMRetired2012]

Peter where would i put this code?

 

[Peter_SSs]

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

 

[PMRetired2012]

Peter where would i put this code?

To implement ..
1. With your workbook active press Alt+F11 to bring up the vba window.
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Press Alt+F8 to bring up the Macro dialog
6. Select the macro & click ‘Run’
7. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

Peter that didnt work...What i want to do is simply take all the cells that say Resale in collum D over 12 worksheets (Jan thru Dec) and the amount associated with resale in collum E (jan thru dec) and place that acount on a another worsheet named YTD. I also dont want that process to effect anyother Cells on the YTD sheet..
Dennis

 

[Peter_SSs]

Peter that didnt work...

In what way did it not work?
- Error message (what message on what line)?
- Put the wrong answer in the right cell?
- Put the right answer in the wrong cell?
- Crashed Excel?
- Did nothing?
- Something else (what)?

What i want to do is simply take all the cells that say Resale in collum D over 12 worksheets (Jan thru Dec) and the amount associated with resale in collum E (jan thru dec) and place that acount on a another worsheet named YTD. I also dont want that process to effect anyother Cells on the YTD sheet..

I thought that is exactly what my code did.

Only possible issue there I can see is here you called it YTD sheet but earlier you called it "Year to date" sheet.

Here is my sample data

PMRetired2012.xlsm
	D	E
1
2	Resale	1
3	Labor	2
4	Utility	3
5	Utility	4
6	Labor	5
7	Utility	6
8	Resale	7
9	Utility	8
10	Labor	9
11	Resale	10
12
Jan

PMRetired2012.xlsm
	D	E
1
2	Resale	1
3	Labor	2
4	Utility	3
5	Resale	4
6	Utility	5
7	Resale	6
8	Utility	7
9	Resale	8
10	Labor	9
11	Labor	10
12
Feb

All other sheets Mar to Dec are blank. My understanding is that you wanted the yellow cells summed and placed into cell A45 of 'Year to Date'.
Here is my 'Year to Date' sheet after running the code.

PMRetired2012.xlsm
	A
44
45	37
46
Year to Date

Perhaps you can use that example, or your own, to explain
a) what is wrong with this
b) what should happen instead

 

[PMRetired2012]

In what way did it not work?
- Error message (what message on what line)?
- Put the wrong answer in the right cell?
- Put the right answer in the wrong cell?
- Crashed Excel?
- Did nothing?
- Something else (what)?


I thought that is exactly what my code did.

Only possible issue there I can see is here you called it YTD sheet but earlier you called it "Year to date" sheet.

Here is my sample data

PMRetired2012.xlsm
	D	E
1
2	Resale	1
3	Labor	2
4	Utility	3
5	Utility	4
6	Labor	5
7	Utility	6
8	Resale	7
9	Utility	8
10	Labor	9
11	Resale	10
12
Jan

PMRetired2012.xlsm
	D	E
1
2	Resale	1
3	Labor	2
4	Utility	3
5	Resale	4
6	Utility	5
7	Resale	6
8	Utility	7
9	Resale	8
10	Labor	9
11	Labor	10
12
Feb

All other sheets Mar to Dec are blank. My understanding is that you wanted the yellow cells summed and placed into cell A45 of 'Year to Date'.
Here is my 'Year to Date' sheet after running the code.

PMRetired2012.xlsm
	A
44
45	37
46
Year to Date

Perhaps you can use that example, or your own, to explain
a) what is wrong with this
b) what should happen instead

Peter,
I get a error message of: Run time error 9, subscript out of range....
the line of code it highlights is: With Sheets(MonthName(j, True))
1. do i have to name the months (Jan-Dec) where it says MonthName?
2. i corrected the year to date sheet problem. Mine says YTD INFO
3. when we get this into a module does it run automaticaly or do i have to make a control button or will it just happen?
thanks Peter