Selecting active workbook - not personal.xls

[BretStallwood]

Hi,
First of all I'm a beginner in VBA, but thank you in advance for any help.

I'm trying to create a macro to split all sheets from an active workbook into separate files. After a bit of a Google I've below the below code which works great for me. However, when I moved the code from the workbook to my Personal.xls it is now trying to run the code on Personal.xls rather than the file I'm running the macro on.

I'd do not want to write the filename into VBA as I want to be able to run it on any filename.

Sub Splitbook()
MyPath = ThisWorkbook.Path
For Each sht In ThisWorkbook.Sheets
sht.Copy
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
ActiveWorkbook.SaveAs _
Filename:=MyPath & "\" & sht.Name & ".xlsx"
ActiveWorkbook.Close SaveChanges:=False
Next sht
End Sub


To test this code I just created and saved a file to my desktop called Test.xlsx, named the sheets "A","B",and "C" and put "QWERTY" in each A1 cell.

 

[dave3009]

Hi and welcome to the board.

Try changing the first part of your code like this

mainWB = ActiveWorkbook
MyPath = mainWB.Path
For Each sht In mainWB.Sheets

 

[BretStallwood]

Hi Dave,

Thank you for your help.

I've tried that and am getting run-time error '438'
Object doesn't support this property or method

Hi and welcome to the board.

Try changing the first part of your code like this

mainWB = ActiveWorkbook
MyPath = mainWB.Path
For Each sht In mainWB.Sheets

 

[dave3009]

Hi

An alternative might be just to grab the workbooks name and use it in the latter part of the routine.

mainWB = ActiveWorkbook.Name
MyPath = Workbooks(mainWB).Path
For Each sht In Workbooks(mainWB)
...

 

[BretStallwood]

I'm getting the same error doing this.

 

[dave3009]

Sorry, I forgot to also copy the .Sheets in the For Each loop, does this rectify the issue or do you get the same error?

 

[BretStallwood]

Amazing. That worked.
Thank you very much!