Weird Set Object and Copy Destination behaivour

[andruha149]

Hello! I have a large piece of code below, so i highlighted the problem part. I'm curious, why there is nothing happening when i try to use Range.Copy Destination between Set worksheets in different workbooks. Everything is working fine but this red part. The weirdest thing is that it was working perfectly fine without border formatting part, however it stopped working after it, this function works good in the code below red. Also the side question - is there any obvious way to shorten the formatting or just overall code to make it run faster. Thanks in advance!

Please Login or Register  to view this content.

[leelnich]

You forgot the "." before your ranges.

Please Login or Register  to view this content.

Please click the Add Reputation star below any helpful posts, and use Thread Tools (up top) to mark your thread as SOLVED once you have your answer. Thanks!-Lee

[andruha149]

hahaha, jesus, i'm so stupid. sorry!

though 2nd part of question, how can i shorten the border formatting/the rest of the code if possible?

[xlnitwit]

Hi,

It looks like you only need a Range().Borderaround statement.

[andruha149]

Ok, thanks everyone. I have shorten the code and modified it a bit - i added at the end of the code function, which saves the opened file in variable folder (saves if exist, creates otherwise cell value defined foldername). But now my loop code broken, i get invalid procedure call or argument error on sFile = Dir right before the end of the loop. I assume it's somehow connected with wrong sFolder path after it saves the file to a new folder. Anyone could help me please?

Please Login or Register  to view this content.

[xlnitwit]

You can't nest one Dir function inside another like that.

[andruha149]

You can't nest one Dir function inside another like that.

So what would be the way to avoid Dir in creating new folder or in choosing next file?

[xlnitwit]

You might use the Scripting.Filesystemobject and its FolderExists method.

[andruha149]

You might use the Scripting.Filesystemobject and its FolderExists method.

Please Login or Register  to view this content.

Worked perfect, thanks for advice.

[andruha149]

Ok, here I am again guys. Modifying and modifying my code. Now I'm working on Error handling part:

1) I put

Please Login or Register  to view this content.

at the start of my loop
2) I plan to use:

Please Login or Register  to view this content.

to create a list of document names at the end of the macro in custom userform. Here I personally see 2 ways how to do it:
1) I collect the errors data (each time error -> retrieve document name and store it on separate sheet) and later proceed to create a MsgBox/UserForm with this data via ShiftDown
2) Other way - I'm not sure it's possible, but to create a MsgBox/UserForm and dynamically update it each time error occurs with document name and show it at the end of whole procedure.

What are your opinions on what is the best way to implement my changes? Thanks in advance!

Please Login or Register  to view this content.