Help with Optimizing Slow Working cut/paste data macro

[Chriz]

Hi All,

I'm looking for a way to increase the speed of a macro I've already written. The concept is to change the format of a very large sales report into something that can be manipulated into a pivot table.

The below code does work, but it runs rather slowly on the actual report I'm using it on at work (which is > 150k lines), so wondered if maybe there was a better way of doing it. I had considered an array, but after researching I am unsure how I would write the specific array values that I need, which are essentially always the 8 rows beneath the start row.

Essentially this macro cuts rows and transplants them onto a parent row which is linked to the account number. I have attached a before and after spreadsheet, as hopefully that will show you what I'm trying to achieve.

Here is the code:

Please Login or Register  to view this content.

[Mumps1]

See if this makes a difference. Change the sheet name (in red) to suit your needs.

Please Login or Register  to view this content.

[Chriz]

See if this makes a difference. Change the sheet name (in red) to suit your needs.

Please Login or Register  to view this content.

Thanks! I'll give this a try tomorrow

[bakerman2]

@ Chriz

Please don't quote whole posts -- it's just clutter. If you are responding to a post out of sequence, limit quoted content to a few relevant lines that makes clear to whom and what you are responding

For normal conversational replies, try using the QUICK REPLY box below.

[Chriz]

Apologies for that.

[Chriz]

@Mumps1,

Now that i've tested this program, I can confirm that it is MUCH faster, so thanks! But I have discovered an issue when I tried it out on live data here in the office, I've noted that on the first pass that the loop does, that it seems to repeat the "SALES" Data from 2018 multiple times, rather than having the correct figures copied over from Sales 2019, Returns 2017 - 2019 and Errors 2017 - 2019.

If you've got time I'd be very grateful if you could take a look, hopefully it's something easy to fix. I don't think you would have noticed it as I rather stupidly copied the test data over and over again on the example I gave.

Edit: I've added your macro to the attached, and changed the line of data that I can see is being repeated, if you run the 'Chriz' Macro on the attached file, you can see what I'm referring to.

Thanks

Chriz

[Mumps1]

Try:

Please Login or Register  to view this content.

[Chriz]

@Mumps1, Confirm it is working perfectly now! Many thanks for your help with this, it's taught me some very useful things that I can incorporate into future macros!

[Mumps1]

You are very welcome. Out of curiosity, about how long did it take to run on the 150 k lines?

[Chriz]

Hi Mumps1, I didn't time it with a stopwatch, but it was no longer than 30 secs! My previous macro was taking at least 5-6 mins

[Mumps1]

OK. Thanks for letting me know.