Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Pivot Table

0

I

Answer
Discuss

Discussion

To surround code with code tags, either press the Code button, select "Code goes here" and paste the code, or first paste the code, then select the code you pasted and press the Code button.
To make your question clear, tell us what you wish your code to do and what it is that the code doesn't do or doesn't do as you wish.
You can amend your question by pressing the Edit button at the bottom of your screen.
Variatus (rep: 4889) Nov 13, '19 at 6:06 am
Sorry, you will have to do better. I want to spend my time on providing help and answers. Therefore I can't spare an hour just to find your question.
Variatus (rep: 4889) Nov 13, '19 at 7:49 pm
Add to Discussion

Answers

0

I regret having to say that I didn't get to your question but here is what I have done.

  1. Added a standard code module to your workbook and pasted your code into it.
    You can use the same code in another workbook or add-in. Just make sure that you specify the workbook to work on as "ActiveWorkbook" (which I haven't looked out for but I think there is no problem so far).
  2. Added two sub routines at the bottom which are called from the Main, reducing the size of the code they replace by up to90%.
  3. Commented your code, largely with the aim of urging you to dispense with the Select statements which are so ubiquitous in your code. The macro recorder needs this but a human doesn't, and neither do Excel or VBA.

After reviewing about 20% down your code I ran into trouble. It seems like you forgot to turn off the recorder and continued typing. Your input is needed to delete what doesn't make sense. So I stopped my review. You will see the difference where I stopped formatting and every line is commented out.

The important thing is that you own this code. Therefore I suggest that you start at the top, as I did, and read my comments. Make sure you understand all the changes I made. There aren't many but they are cutting deep. 

Then take out (delete) those lines of code which I don't know what to do with (see my comments).

Finally replace all Select statements in the unreviewed part of the code with proper addressing. You have ample examples how to do it in the part of the code where I did the job.

After you have done all that your original question will still be there but the environment will have changed. Put it into the new thread. Continue in this thread with any questions that might arise from the changes I have made. Note that I didn't test run my code. I wanted to leave some work for you lol:

BTW, I am impressed by the size of the task you have taken on and the progress you have made while working on it. Keep at it. It's turning out beautifully.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login