Getting Error 1004 Object or Application Defined Error - Code to rearrange data

This code is meant to rearrange a set of data I have based off the values in column A. The data is currently arranged in blocks of 5-8 rows that only have unique data in column B. I am trying to read through column A and move the data to a new sheet. Depending on the value in column A, the data from column B is placed under that header in the new sheet. All the data from one block is placed in the same row. I have attached the dummy data excel sheet that contains a similar data set. The first sheet shows how the data currently is formatted. The second sheet shows how I want the data to look.
At the bottom is the code I have so far. I am new to VBA so from what I understand this code could me much shorter and it would run faster. I am not really worried about how fast it runs just so long as it will do what I want. I was finally able to get the debugger to show and highlight the line with the error, unfortunately it didn't help me.

This is the line that is highlighted

Please Login or Register  to view this content.

I have tried multiple lines. Originally the code looked more like this:

Please Login or Register  to view this content.

But I received the same error. I have tried to SET the activeworkbook.worksheets("Sheet1") to be wCurrent and the other to be wNew but I was still getting the same errors. I posted another thread here. It started out really disorganized and I think it may be throwing people off. Only one person has tried to help but they really haven't said anything but that they will help me eventually and I think they have already forgot. I don't want to keep bumping old outdated threads.

Thanks,

Troll

Please Login or Register  to view this content.

NOTE: the special case is set up so that on certain data blocks one column A value may be adjacent to multiple column B values, it takes all of those cells and puts them in multiple rows under the right header. Then it adds rows to my data block counter so that the next row doesn't overwrite that data.

Need to change all lines

Please Login or Register  to view this content.

Since "Name" is exact match, I do not think you need to use a name like function. I would put it this way

Please Login or Register  to view this content.

Thanks for the quick reply! I see I had the column and rows mixed up so I am changing what you suggested as well as all the other times I referenced a column,row to a row,column. I'll let you know when I have finished and we will see how it runs.

Thanks again. I was able to fix all of that code but I got an error like the one seen here. So I am fixing all my copy and paste functions.

Blazzed,
The link took me to a different site. Please post the corrected code(All lines) showing which line shows you an error.

I'm actually not getting the error anymore. But I don't think it is working logically. So I will have to check values and see where the logical errors are. Here is the 'working' (not errors) code.

Please Login or Register  to view this content.

I'm also trying to +rep with the little star but it's not loading anything. I have some other work I need to finish up then I'll be back and I'll try and get that +rep for you.

Thanks again.

Blazzed,
I have got the same issue. The site has been very slow and keep crashing.
I have improved the code for the first part, but somehow lost on the second part. We can improve on the second once I get your feedback. The second part of the code is wrong, it is just I do not understand the lines.

Sorry if you guys just got a random comment. When I came to look none of those solutions were here. The Code Norie posted is working as intended. I made one small change. I had to wait to add to the dataBlockCounter. It was just a visual error though. Thank you both very much. My data has finally been rearranged properly. +rep to both of you. I think the site was acting up before and that was why I couldn't give you rep AB33, but it has been given now.

D***, I must be a little slow today. I just now realized that file AB33 uploaded had a macro added to it. I like the use of case. It is working quite well. I think it may be just a little faster than the edits Norie made to my code. Once the database I am rearranging grows I can see speed becoming an issue so I have decided to use that macro. Thanks again. You guys are awesome!

Yea, I noticed that little line. I had at one point used that to find the last row, it was stored as an integer FinalRow. I think it may still be floating around on the old thread. But when I started running into errors I figured a static value was fine. But as I said before, when the database grows, it will be nice to not have to edit that value manually. His code came up with an error because the dataBlockCounter was initialized. I just copied parts of his into your code. The best of both!