VBA to create workbook from sheet name, check if file exists

[maym]

Hi

I am trying to create a new workbook for each sheet in my workbook. So far I have this:

Please Login or Register  to view this content.

what i am trying to do next is to check if a workbook with the sheet name already exists. If it does, I want to copy the data to the bottom of the existing workbook. If it doesn't, I want to create a new workbook as above.

Can someone please help me solve this?

Thanks

[bakerman2]

You can check existance of a file with the Dir-command.

[maym]

Hi bakerman2,

thanks for your reply, but can you please elaborate on how to do this?

Thanks

[humdingaling]

Please Login or Register  to view this content.

you are making a brand new folder which is date and time stamped every time you run this...there would be no existing files in that folder?
are you referring to subfolders?

[maym]

I don't actually need a brand new folder each time. I only want to reference the folder, check if there is a workbook with the name of the sheet. If there is, I want to open it and add the data. If there isn't, I want to create a new one as above.

Thanks

[humdingaling]

anyway

Please Login or Register  to view this content.

http://www.rondebruin.nl/win/s9/win003.htm

[humdingaling]

i am confused about your pathing but in your case you would replace
FilePath = "C:\Users\Ron\test\book1.xlsm"
however your path works then

Please Login or Register  to view this content.

[maym]

thanks but I can't get this. If all my files were in C:\Users\Ron\test\

then I am trying to use something like this to check (sourcewb is active workbook)"

C:\Users\Ron\test\" & Sourcewb & sh.Name & ".*"

but it is giving me an error?

[maym]

I tried to loop but that also doesn't work:

Please Login or Register  to view this content.

Is there something that I am missing?

[humdingaling]

re arranged code to work with sheet name

Please Login or Register  to view this content.

[humdingaling]

in the attached example just put a file named testfile in there and you will see it work

[maym]

thank you so much. I am trying to create the file if it doesn't exist, however I get an error where I try to save it:

Please Login or Register  to view this content.

Is there something I have done incorrectly?

[humdingaling]

first off..you should probably ident properly to find out what code goes with what

some minor tweaks to the code below

Please Login or Register  to view this content.

[maym]

Thank you, that works grreat.

Lastly, if the workbook exists, I need to open it and copy the contents to the bottom of it. Is there a way I can do this?

[humdingaling]

yes but i have no idea what to copy from original sheet
you cant copy the whole sheet and paste into another whole sheet...ie A1 to XFD1048576 it just wont fit

you need to be specific about what you are copying and where you are copying it to

[maym]

thank you.

I would like to copy from A2 to column D (then down to last row) and then add this to the bottom of the data in the existing worksheet (first available row). Does that make sense? I can post an example if that is easier.

[humdingaling]

Please Login or Register  to view this content.

[maym]

works perfectly, thank you so much