Set X as Active Sheet

Hi Everyone, trying to edit this code. Rather than having X as a set file, I'd like to change it to the Active Sheet when the macro was ran.

Please Login or Register  to view this content.

I thought I could simply do Set x = ActiveSheet, but this did not work. Any suggestions?

you have already got x declared as a workbook - was there no error message if you tried to run.

Originally Posted by torachan

you have already got x declared as a workbook - was there no error message if you tried to run.

My goal is to not declare it to a certain workbook as the file changes every time (e.g name changes every month). Thats why I need it to be just the active sheet that I loaded and then ran the macro.

Looks like 'Set x = Active.Workbook' solved the probelm.

Looks like useless to create a variable object on an already existing object, just using directly ActiveSheet rather than X …

Originally Posted by Marc L

Looks like useless to create a variable object on an already existing object, just using directly ActiveSheet rather than X …

Would you be able to give an example of the edit?

Just remove the Set x codeline then in the code replace any x by ActiveSheet …
But according to your initial post the active worksheet is not in workbook x but in workbook y !

Another way to go rather than creating object variable is to use a With block like you can see within VBA help.

Correction : use set x = ActiveSheet if you wanna save the active sheet before to open the workbook y …

Originally Posted by Marc L

Correction : use set x = ActiveSheet if you wanna save the active sheet before to open the workbook y …

That doesn't seem to be working for me. just errors on the Activesheet line.

Variable must be defined obviously as Worksheet rather than Workbook if it's for the active sheet rather than the active workbook …