Using string to saveas getting subscript out of range error

[pbarry]

I am trying to set things as strings and saving using those variables, but I can't seem to get it to work. There is no way to use a dynamic variable either with a cell range containing data, as this is formatted exactly how it needs to be imported. So, I had to manually set the if...elseif, and the length of the path and everything going on I seem to have missed something. I am toying around with the second elseif, as 370 is the first worksheet in the workbook, so that is different than the rest. Any fresh eyes out there able to catch something I am doing wrong besides having gross looking filepaths?

Please Login or Register  to view this content.

[HaHoBe]

Hi, pbarry,

giving a Format inside a String wonīt be really helpfull as the command canīt be reckognized by VBA. Maybe try

Please Login or Register  to view this content.

as an example for the first occurrance of the Format.

Ciao,
Holger

[pbarry]

HaHoBe, thanks for the advice. I will try it out here in a little bit. Makes sense to separate things within the string. I'll let you know how it works!

[pbarry]

It worked for the first 2 of 6 saves, then I got a "method 'saveas' of object'_workbook failed". Not sure why it worked the first 2 workbooks, but gets hung up here?

[HaHoBe]

Hi, pbarry,

what is Company370_ in the third part: a variable or a string?

Maybe try

Please Login or Register  to view this content.

Ciao,
Holger

[pbarry]

It gets through Company 370 and 380 fine, but struggles here:

Please Login or Register  to view this content.

looking at this, here's the error. wow for stupid, I thought I changed them all.

Please Login or Register  to view this content.

The Companies are sorted prior to the IF...ELSE IF statement so it will look from lowest to highest.

[pbarry]

Ok, I must be all confused here...thsi is the code that I have in the macro. I must have pulled it from somewhere else with the ^^ reply, sorry for the confusion.

Please Login or Register  to view this content.

[pbarry]

Hahobe, I tried your modification by bringing the tail end of that filename into the declared variable and it is still getting hung up on the 3 set of criteria. Kind of frustrating when it works for the first two, then fails the next three then works on the last one? As far as my eyes can see, the paths are correct after checking it a dozen times and it doesn't seem to want to work. I will try restarting my computer and see if that solves it.

[pbarry]

Funny thing, on half of the paths, the verbiage is this "2. Formatted MRP Forecast", and then the other half like this, "2. Formatted MRP". Inconsistent data ruined a few of my hours, sorry for being a pest on here!