Contentens Clearing on wrong worksheet

[Jon_P]

Experts, I could really use some help and direction...

I have a Marco that clears specific column of data on a worksheet [POF1_Load Format] before rewriting new data from Inputbox. It works correctly (As long As) I am on the correct worksheet however after I added an instructions Tab, the macro also clears part of my instruction as well as properly clears the data on the correct tab.

I am trying to figure out how to do one of the two options below....

1. click the button I created and have it switch to the input [POF1_Load Format] tab and execute the Marco so it will run correctly then go back to the instructions page

2. when the button is clicked only clear the content defined in the [POF1_Load Format] tab.

Below is my code and I have attached an Excel file with the code I have assembled, the excel file has been limited to only the data and information needed...

Thank you in advance...

Please Login or Register  to view this content.

[bakerman2]

Deleted because OP complied to Forum Rules.

[TMS]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here


That said, the purpose of using With is to avoid using Select.

You need to use, for example, .Range("D2"), to link a range to the worksheet in the With statement



Your code:

Please Login or Register  to view this content.

[xlnitwit]

That said, the purpose of using With is to avoid using Select.

I don't think I would agree with that- the two aren't related?

[TMS]

Choice of words might not be ideal. However, as a general rule, you should avoid using Select. Using With allows you to create relationships and link objects. I believe it has efficiency advantages and can certainly shorten code statements. I use it to avoid using Select. Perhaps you don't or use it some other way.

Whatever, I am fairly certain that using With in the code shown without linking the range to the worksheet is the cause of the issue. And I have never seen Select used within a With construct.

[TMS]

Try:

Please Login or Register  to view this content.

[bakerman2]

I'd go for this one because occasionally empty rows might be responsable for not clearing entire column D using End(xlDown)

Please Login or Register  to view this content.

[Jon_P]

Thank for the quick replays, I will try both and let you know how they work...

[Jon_P]

bakerman2 worked perfectly... thank you..

[Jon_P]

I fixed it... Sorry, I will make sure to follow rule 3 next time...

[bakerman2]

Glad to help.