Macro to copy and save a worksheet to a new workbook

[skmerrey]

Hi Everyone,

I've found the code below online. It originally exported all the worksheets to their own individual workbooks and saved them in the same location as the current workbook.

I've adapted it to export only one sheet named "EH", it creates the new workbook with this single sheet but doesn't save it and gives me a runtime error '424' Object required. When U press Debug it highlights the row of text that I have underlined.

Can anyone see what I've done wrong?

Sub Test18()

Dim xPath As String
xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.Sheets("EH").Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & EH.Name & ".xls"
    Application.ActiveWorkbook.Close False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

Thanks
Shaun

 

[Ashhu]

Dear Shaun
I understand that you need to export and save Tab named EH from the existing sheet to defined location. if i am right, try below code

Sub Test18()

Dim xPath As String
Dim strFilename As String

strFilename = "EH"

xPath = Application.ActiveWorkbook.Path
Application.ScreenUpdating = False
Application.DisplayAlerts = False
ThisWorkbook.Sheets("EH").Copy
    Application.ActiveWorkbook.SaveAs Filename:=xPath & "\" & strFilename & ".xls"
    Application.ActiveWorkbook.Close False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

.

 

[skmerrey]

Thankyou Ashhu, that is exactly what I need.