Error with VBA code

[KarenTh]

Hi again all, I'm hoping that you can help me again. I checked with my IT, but it's like pulling teeth! I have a large workbook which is our customer statements. I want to split each tab into a new workbook. The code runs fine up to a point and then it just stops halfway through. What am I doing wrong?

Also, I would like to rename the tabs before splitting them using a specific cell, D6. Is there any way I can include this in the same VBA code?


Please remember that I'm an Excel dummy, I'm more a copy and paste of girl so if anyone is willing to help me I would really appreciate it! I got this VBA code from the internet, and was quite proud of myself for getting that far!

Sub Splitbook()
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.Cells.Copy
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
ActiveSheet.Cells.PasteSpecial Paste:=xlPasteFormats
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & sht.Name & ".xls"
ActiveWorkbook.Close savechanges:=False
Next sht
End Sub<o></o>

 

[KarenTh]

Morning Dave, I'm so sorry it's still giving me the same error