|Thank you for the code. As I noted in my previous post, recorded code is bloated and messy, with all sorts of extraneous instructions. For example, this section basically does nothing but Select some cells and scroll around the screen:|
Absolutely useless from a macro point of view, since it accomplishes nothing. There are lots of instructions in your code that do similar things, i.e. nothing useful. Obviously, that's not your fault, the recorder simply recorded everything you did, and when you re-run it, it simply does it all again.
As for your issue, I need to begin with this disclaimer: Without seeing your actual workbook the best I can do is guess at what your problem is.
That said, here is what I believe to be the root cause of your problem:
"Just to test it I will clear the contents in sheet 1 and run the macro again."
There seems to be a lot of Cutting, Copying and Pasting going on in your code. If you've cleared the data, what is left for the code to Cut or Copy? What you are probably seeing when you say "I can see the cursor moving everywhere" is the code Cutting, Copying and Pasting a bunch of empty cells.
The code doesn't save any data and it doesn't know anything about data that used to be someplace. All it does is mimic the Selection of cells and mimic the Cut, Copy and Paste instructions that you performed when you recorded it. It will also resize/delete/insert the columns as instructed, but since they are empty, it won't look like it is really doing anything. Now, since you entered data in certain cells (e.g. A/C Name), and a VLOOKUP formula in other cells, you will see them after the macro has been run because that data entry was recorded when you recorded the code:
ActiveCell.FormulaR1C1 = "A/C Name"
********* Side Note Begins *********
BTW, as an example of what I meant by the "bloated code" that the recorder produces, if you were to write that instruction "manually" it would simply be:
Range("H10") = "A/C Name"
A Selection/Cut/Paste instruction that records like this...
...can be reduced to this:
There is rarely a need to actually Select a Range/Cell/Column/etc. in order to do something with it in VBA.
Imagine how much easier it would be to follow your code if wasn't as bloated as what the Recorder produces, but again, that's not your fault.
********* Side Note Ends *********
Anyway, as far as I can tell, your issue is that your code has no data to work on because you cleared the sheet. All that the code is doing is formatting a bunch of empty columns and Copying/Cutting/Pasting a bunch of empty cells.
You'll have to tell me if you think I'm right, because, as I implied earlier, I can't see your worksheet from where I'm sitting.
Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.