Assistance with getting Loop to change sheets

Hi,

So I'm attempting to write a macro that will look for the next empty cell below D7 and insert a vlookup into that cell which it will then replace with values. The macro needs to do this in each sheet in the book exept the last 2, but at the moment it isn't changing sheets. It's just inserting the formula multiple times in the blank cell below D7 on the active sheet. Any ideas welcome:

Please Login or Register  to view this content.

Thanks!

Maybe throw a Sheets(I).Select in after the For. Not the best code but it should work. Maybe later when I wake up properly I can have another look


Regards, TMS

You're welcome.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

Thanks for the rep

You have a test workbook? That would be good to see

You can't select or activate a hidden sheet ... you can't do it manually. As you have discovered.

So you need to trap the error using On Error Resume Next, or On Error Goto label. Probably the latter. Put the label on the line before Next I. Like:

Please Login or Register  to view this content.

Regards, TMS

Thanks for getting back to me TMS.

I ended up unhiding the sheets (found a macro online to do that) and moving them to the end of the workbook. I then re-hid them (no idea what they're for - I inherited the sheet) and ended the loop 14 sheets earlier.

Works an absoulte treat.

Thanks again. Final Code:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards, TMS

Ah, should have said ... please use Code Tags. I really shouldn't answer questions if you don't comply with the rules. Oops. So, please, do me a favour and edit your posts (see below). And see my last post. It should cope with the hidden sheets ... in fact, it will actually put the formula in and convert it to a value. However, if you don't know what the sheets are, you might not want that to happen.

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.

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

You're welcome. . And thanks.


Note my last post will be faster and less harsh on the eyes as it doesn't select anything. You just need to adjust the sheet count as you have in your final version.


Regards, TMS

You could make your version quicker by switching ScreenUpdating off and on but that really just masks the fact that selecting sheets and cells is slow ... and unnecessary.

Regards, TMS

I'm still at the manipulating-recorded-macros-and-copying-stuff-I-find-online level.

I guess we all start there ... at least, I know I did. My learning style is to find something that works, adopt it, use it, understand it, adapt it and, hopefully, improve on it.