Can't Insert Column Due to Non Blank Cells

I'm trying to run the below script, but on one of the worksheets I get a run time error 1004 when it gets to the point of inserting a column. It states I can't insert the column because non blank cells can't be shifted off the worksheet. A Ctrl+End on that worksheet takes me to cell XFD534. After researching a bit I've attempted in my code to change my used range by deleting all the columns to the right of my actual usable data, but this valiant attempt appears to be misguided and isn't working. Is there a way of resetting my used range in the code?

For bonus points: I'm self taught in VBA and am still learning, so if there is anything else in the code that could be improved I'd appreciate the feedback.

Thanks

Please Login or Register  to view this content.

Thanks for the response xladept. I appreciate your help. Your suggestion worked. But, while messing around with the code last night I figured out another fix that I think suites me better. I noticed the code I had to reset the used range was only firing on the sheet that was active at the time the code ran. I made a small adjustment to the code and it seems to be doing what I want:

Please Login or Register  to view this content.