VBA to compare cells from 2 different worksheets and then if matched, copy certain cells

Testing Author Royalities 1.xlsm

Hello,
Still kinda new to the VBA programming. Got a bunch of help so far. Basically what I am trying to accomplish to make individual royalty sheets for each Author.

So far I am able to create a new worksheet for each Author, name it correctly, and paste in what books the Author wrote (starting at A51). Now the tricky part comes into play.

I need to look at each Book name and then compare it to the "REPORTS" wooksheet Column A, when there is a match, it needs to copy certain Columns from the matched Row. Once it is finished with that, in needs to keep checking, since there can be multiple entries of the same book name, the difference will be in the "transaction type", so it needs to copy the certain columns on that row and paste them into the new worksheet on the next line down.

Hopefully you can see the Example WorkSheet that I would like to finally have. called "Anderson,Jeff" It shows that he had written 1 book called "Go the Distance" A51 and that it actually had 2 different transaction types E16 and E17.

Unfortunately, the "REPORTS" will always be changing, books come in, books go away... Same with Authors, so the program has to be dynamic.

I will always start with the first 3 worksheets. "Reports" "Authors" and "Royality Blank"

Thanks for any help on this.

I do not know what is the relevance of the sheet "Royality Blank" with the task at hand.

The match and search part is not that difficult, but will part the month "March" always be on the fixed position?
It is not also hard to create a sheet name for each author, but not the design you have. So, the code will not create sheet names(Author), but checks if the sheet name exists. If it does, it will copy the relevant cells to that name. The name should be created first by hand as you did with Anderson. If the code is unable to find the name, it will create its own. This is not what you want.

Hello AB33,
Were you able to see the Macro already in the excel workbook, called Macro1? Sorry I did not make that clear at all. It already creates a sheet for each Author with the design from "Royality Blank"

If you are able to run the macro, first rename the worksheet "Anderson, Jeff" to something different. I meant to name it something like "This is what I want" before I uploaded it, but forgot.

Here is Macro just in case.

Please Login or Register  to view this content.

Also, RIght after the "end if" statement is where I was think the code needed to be placed, with all the comments.

Thanks
Tony

Okay, I will have a look at the code. In the mean time, could you answer this question?

Will the month "March" always be on the fixed position?

Assuming that your code creates sheets(Tabs) for each author in column A, you want to do now the search and copy bit. Is this right?
I am approaching your request from this angle. If a name (tab) exists, the search and match for each row in column B will be copied to that name. The code I have could also create a name if this name does not exist, but it only creates a name(Tab) with author name and blank cells. However, If your code can create names, that fine, my code with then do the match and copy part.
I am assuming that there is consistent on the layout of your sheet names- the same as Anderson because the code will copy the match rows starting at row 16 and increases by 1.

Look at my code. It has created a sheet name for each author and populates with a match in column B,rows 16 and 17, if the author has a match search in column B.

sorry, I forgot to answer that question.

The month "March" will always be in the same location.

Assuming that your code creates sheets(Tabs) for each author in column A, you want to do now the search and copy bit. Is this right? Yes



I will take a look at you attachment now.

Thanks for helping.

Okay,
I left out that bit from the code, and will put it back once you get back with feedback.

Just looked at you code, looks like it works correctly. I wish I could understand it a little better.

Were you able to run my code? It makes the worksheets with the formatting from the "Royalities Blank".

I thought you have already got a code, but when I tried it I got an error. I will have a look.

Hmmm.. that's weird, It run fine for me.

But, I was able to change your code a little to copy the "Blank" and it looks really good now.

Here is the code.

Please Login or Register  to view this content.

If I delete all the tabs except the first 3, and run the code, it looks likes it all works.

I really need to look at what you did and try to understand more.

Now all it needs is the Date "March" copied. is that the part in the code that you had commented out?

I wonder if the error was because of the tab "Anderson, Jeff" is already there??

Hardy,
No, the code you have will not work as there is not built -in line to test if a sheet name exists.
I have found round of getting the same result. Since you have already created the sheet name "Royalty", I have written a code which duplicate this sheet name and change the name of the author in B7. Obviously, I am assuming that it is the standard of this template you wanted to follow.
I have two codes. The codes are self explanatory.
I have tested the code on "Anderson" and it works, but you have merged cells, so the code might not work on the other sheet names. I have to manually unmerge the cells in Anderson. I am afraid, you might not need to do on the other sheets as well.
Will be off of my PC for few hours.

Cool, I will take a look.

Thanks for all the help. I will get back, lunch time is coming quickly, and I like to leave work for a little while.

I gotta say, I really like the Delete Macro.. very helpful.

So if I run "createnames" macro first, then run "SearchfoZirh" macro second, it look likes it makes everything

Yes, it was a standard format that I wanted to follow.

I will remove the merged cells from the "Royality Blank" and see if that helps anything

I gotta say, I really like the Delete Macro.. very helpful.

So if I run "createnames" macro first, then run "SearchfoZirh" macro second, it look likes it makes everything

Yes, it was a standard format that I wanted to follow.

I will remove the merged cells from the "Royality Blank" and see if that helps anything

Too funny, I didn't realize that I needed to click the next page, So I thought that my last reply didn't go through... so now there are two posts.

Well, you can incorporate both in to one, or the tidy one would be to call the second code from the first one, so you do not have to open booth codes to run.
Change these line on the createname code(First one)

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

Thank you so much.

Now that you got it working, I'm gonna try to decipher what you coded up so I will me smarter