Run Time Error

[billandrew]

Afternoon

Is there a work around to the below seems to work well in other scripts.

The error occurs at the ThisWorkbook.Activate

Thanks All...

Dim filepath As StringSet wb2 = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy before:=wb2.Sheets(1)
wb2.Activate
filepath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
wb2.SaveAs filepath & "Results" & " " & Format(Now, "mm-dd-yy hhmm") & ".xlsx", FileFormat:=51

 

[DanteAmor]

Is it all your code?
What does the error say?

 

[Michael M]

Why do you need that line of code ??

ThisWorkbook.Activate

Are there a number of workbook open ??
Have you tried removing it ?
What are you actually trying to do with the code ??

 

[billandrew]

@Michael M

I'm not sure why i need this?

 

[TheoScott]

pretty good code, right?

 

[billandrew]

Michael

Trying to add workbook and save to Desktop.

I've changed to this, however it seems to save to downloads folder not the desktop.

Thanks for any help...

Dim wb As WorkbookSet wb = Workbooks.Add
ThisWorkbook.Activate
ActiveSheet.Copy before:=wb.Sheets(1)
Dim WSHShell As Object
Dim DesktopPath As String
Set WSHShell = CreateObject("WScript.Shell")
DesktopPath = WSHShell.specialfolders("Desktop")
ActiveWorkbook.SaveAs "Results" & " " & Format(Now, "mm-dd-yy hhmm") & ".xlsx", FileFormat:=51
Set WSHShell = Nothing

 

[MARK858]

You haven't included DesktopPath in your SaveAs line.

 

[billandrew]

ok

I'll try this and let all know.

Thank You sir

 

[billandrew]

Update:

In the OP I may have not stated what I was attempting to attain. Save a worksheet ["Results"] from the current workbook to a new workbook and save to desktop. This script does what I need. Hopefully it helps others.

Thanks to DanteAmor, Michael & Mark for your assistance.

Dim Filepath As String    Sheets("Results").Copy
    Filepath = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
    ActiveWorkbook.SaveAs filename:=Filepath & "Results" & " " & Format(Now, "mm-dd-yy hhmm") & ".xlsx", FileFormat:=51

 

[DanteAmor]

Update:

In the OP I may have not stated what I was attempting to attain. Save a worksheet ["Results"] from the current workbook to a new workbook and save to desktop. This script does what I need. Hopefully it helps others.

Thanks to DanteAmor, Michael & Mark for your assistance.

I did not do much, but thanks for the feedback.