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]

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

I also forgot to tell you i have other page tab names: OPTIONS PAGE, NEW EQUIP REPAIRS

 

[Peter_SSs]

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?

Thanks. That does indicate a problem with the sheet names. The code is expecting to find sheets named "Jan", "Feb", "Mar" etc to "Dec"
When you get the error, click Debug then hover over the variable j in that line of code. A pop-up should show you the value of j at that point. If j was say 5 then the problem sheet will be the 'May' sheet. Check for any spaces before or after 'May' on the tab name. For instance, here I have gone to rename the May sheet and you can then see a space after the name

 

[Peter_SSs]

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?

No, it does not run automatically. You could make a button or run it as I described in post #7 steps 5 and 6

 

[PMRetired2012]

Thanks. That does indicate a problem with the sheet names. The code is expecting to find sheets named "Jan", "Feb", "Mar" etc to "Dec"
When you get the error, click Debug then hover over the variable j in that line of code. A pop-up should show you the value of j at that point. If j was say 5 then the problem sheet will be the 'May' sheet. Check for any spaces before or after 'May' on the tab name. For instance, here I have gone to rename the May sheet and you can then see a space after the name

View attachment 32622

I have the months spelled out like January, Febuary and ect to December does that matter?

 

[Peter_SSs]

I also forgot to tell you i have other page tab names: OPTIONS PAGE, NEW EQUIP REPAIRS

They should be no problem at all to my code.

 

[PMRetired2012]

I hover over j and it says =1

 

[Peter_SSs]

I have the months spelled out like January, Febuary and ect to December does that matter?

It sure does. You have never said that and repeatedly referred to them like this.

named ( Jan-Dec,

12 worksheets (Jan thru Dec)

Change the line of code to

With Sheets(MonthName(j, True))
With Sheets(MonthName(j))

 

[PMRetired2012]

It sure does. You have never said that and repeatedly referred to them like this.




Change the line of code to

With Sheets(MonthName(j, True))
With Sheets(MonthName(j))

Peter
I still get a error message of: Run time error 9, subscript out of range....
the line of code it highlights is: With Sheets(MonthName(j))
1. I corrected the year to date sheet problem. Mine says YTD INFO
2. I hover j now and it says =2
There is someting still not right about the code. Have you got any more ideas?
thanks Peter

 

[Peter_SSs]

: Run time error 9, subscript out of range....
the line of code it highlights is: With Sheets(MonthName(j))
1. I corrected the year to date sheet problem. Mine says YTD INFO
2. I hover j now and it says =2
There is someting still not right about the code. Have you got any more ideas?

Yes, that indicates you don't have a sheet called "February". Check again the spelling of that tab name, including that it does not have any leading or trailing spaces.

 

[PMRetired2012]

Yes, that indicates you don't have a sheet called "February". Check again the spelling of that tab name, including that it does not have any leading or trailing spaces.

WOW Peter it worked thanks alot.
Now lets see if we can take it one step farther.
1. 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?

thanks