Problem with vba copy find nested loop

Hi This is my first thread.

I need some expert advise.

I am trying to search for data in sheet1 based on row and column and want to copy the value into sheet 2 through nested loop. when i try this code, but it is not working.
not sure what is wrong.

Any one suggestion is much appreciated.

for example, i want (Book1,Person1) total (1+7) to sheet2 (Book1,Person1)

Sheet 1:

Book1 Book2 Book3
jan person1 1 3 10
person2 7 8 4
person3 10 21 7

feb person1 7 3 2
person4 5 11 6
person10 6 5

marchperson2 8 30 3
person3 3 9 1
person5 4 6 8
------------------------------------------------------------------------
sheet2:

Book1 Book4 Book2
Person1
person3
person10
person4
person6
person2
person5



this is my code

Please Login or Register  to view this content.

Hi Anna,

First, Edit your post and select all the lines of Code and then click on the "#" sign above the message area. This will block your code to look like code.

Next - I'd do this problem by changing your table (which I call a Crosstab format) to a normal Excel table and do a Pivot Table on it. See the attached which has a macro I've used many times for this conversion and the pivot table, which is your answer. I'll fiddle with your code and do a double loop in a few minutes and IF I succeed, I'll attach my VBA answer later.

Hi Anna and welcome to the forum. I forgot to say that in my first post.

I've written code to step through all the cells on sheet1 and put the numbers in sheet2. You can see my code in the attached. Then I realized you most likely want the SUM of books sold by each person. This lead me back to the Pivot Table answer where I simply changed the Values to Sum instead of Count and the answer I believe you are looking for. Read my code and get some hints from it but I don't think it is what you are looking for.

Hi Anna,

If you want to do Sums then this code and attachment are for you:

Please Login or Register  to view this content.

Hi Anna,

You can copy the cells of a Pivot Table and then Paste them using "Values Only" to another place in a worksheet. Is that your question?

You can also Drill Down by double clicking on a Pivot Table cell. This is also called "Show Detail". This will create a new sheet showing only the data that made up that value in the Pivot. Is that your question?
See http://chandoo.org/wp/2010/01/27/pivot-table-tricks/