VBA to separate specific Tabs into separate workbooks

[redneckopen]

How would I separate tabs into different workbook (no need to save at this point) by the first four characters of the Tab name using VBA?

example; Canada, Chile, Colombia, would be separated by cana, chil, colo. There is additional information on each tab but these would be leading.

 

[redneckopen]

Yes 1 sheet only

 

[redneckopen]

Now I am getting a run-time error 'This extension can not be used with the selected file' when I name the sheet

 

[redneckopen]

should it be .SaveAs (NewName & ".xlsm") instead of .SaveAs (NewName & ".xlsx")

 

[igold]

Whichever you want the extension to be. Only put the actual name of the file in the input box. The code will automatically add whichever extension you put in that line of code. It is a new book with no VBA in it so you should be able to save as an xlsx (providing you are running Excel 2007 or higher)...

 

[redneckopen]

So after trying the .xlsx with the same error I changed to .xlsm and it worked once then asked me to name the workbook which I did and then it stopped the process. When I opened the file that was saved it just copied the (Master)first tab 69 times along with still having all of the other individual tabs.

 

[igold]

I don't know what to tell you. The code tested fine for me with a mock up of what I believed your data to look like. If you want to paste a graphical representation of what you have or email me a copy of something that is similar to your workbook, then feel free to PM me and I will give you my email address.