[SOLVED] Activesheet Reference for every line of code

[skellz022782]

Hi Everyone-

I'm semi new to the world of VBA Macro's. I use the macro recorder quite a bit to get me going and was wondering if someone could help me with something. I'm attempting to write a Macro to delete rows that meet a certain criteria but I gave up on that task. Instead I've been deleting the rows using the Macro Recorder and a filter. So the Idea behind the Macro is that it

1.) Copies Data from a Table to a worksheet
2.) Finds any row that has waste in column 4 and gets rid of the column
3.) Uses a VLookup to get a SKU for the stone type
4.) subtotals tonnage produced based on SKU

I started to get very frusturated because it seems for everyline of code I go through, I need to add the prefix activesheet everytime I refer to a range. Can someone just explain to me why that is and how to fix it?

So to do the first two steps of this, I have:

Please Login or Register  to view this content.

[Olly]

EDIT: Title changed and code tags added, thanks.

[skellz022782]

Sorry I corrected both per forum rules.

[Olly]

Your code can be completely rewritten to avoid selecting everything...

Will be easier with an example of your workbook, and what you are actually trying to achieve.

Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Olly]

Obviously completely untested (no workbook to test against), but a rewrite might look something like this:

Please Login or Register  to view this content.

[Norie]

If you omit ActiveCells then the code will automatically refer to the active sheet when you use things like Cells/Range etc.

Saying that though, what you should really be doing is expicitly referring to the sheets you want to work with.

For example the first part of the code, copying the table could be written like this.

Please Login or Register  to view this content.

[skellz022782]

Obviously completely untested (no workbook to test against), but a rewrite might look something like this:

Please Login or Register  to view this content.

This just worked flawlessly. Not only that, it ran way faster. Much appreciated.

[skellz022782]

If you omit ActiveCells then the code will automatically refer to the active sheet when you use things like Cells/Range etc.

Saying that though, what you should really be doing is expicitly referring to the sheets you want to work with.

For example the first part of the code, copying the table could be written like this.

Please Login or Register  to view this content.

Thanks for the tip. So I guess I should stay away from selecting?

[Olly]

Happy to help. Hope it's helped you learn a better way of writing code, too!