VBA Trim with variables????

[DouglasWicker]

Afternoon all,

I am trying to copy cells from a specified sheet from a specified workbook.

In order to do so, the file directory and sheet name are being saved as the variables File and MON respectively.

This is how the code would work without the variables;

Please Login or Register  to view this content.

however when I try and put variables in, it won't work;

Please Login or Register  to view this content.

Anyone have any ideas??

[xlnitwit]

Hi,

Does File end with a square bracket? If not, you need to concatenate that in

Please Login or Register  to view this content.

[DouglasWicker]

Hi,

Does File end with a square bracket? If not, you need to concatenate that in

Hi,

I've tried that, however I then get too many square brackets. If you type in the file directory as opposed to using a variable, It turns C:\Users\[Test.xslx] into just [Test.xslx].

I am able to use a variable for the file name alone however which is why i'm sure this is possible!

This code below uses the File variable correctly however it takes the data from the first sheet every time;

Please Login or Register  to view this content.

This code below uses the MON variable fine however it requires me to manually enter the file directory;

Please Login or Register  to view this content.

I just want to be able to combine them both!

[xlnitwit]

What exactly is in the File variable?

[DouglasWicker]

What exactly is in the File variable?

I have been able to get the original code to work. The issue was that the string saved into the File variable was 'C:\User\Test.xslx'. I have found that if I put in square brackets so it reads 'C:\User\[Test.xslx]'.

Is there a way I can change the initial code so I don't have to manually add these brackets every time?

The file directory will change every time.

[xlnitwit]

Yes.

Please Login or Register  to view this content.

for example.

[DouglasWicker]

Yes.

Please Login or Register  to view this content.

for example.

That works great, thank you for your help

[xlnitwit]

You're welcome. Please remember to mark the thread Solved using the Thread Tools link at the top of the page.