help save worksheet to new wb

[KK1966]

i searched the thread find the code but can't ok as i use excel 2007

Sub savesheet2()
Application.ScreenUpdating = False
ActiveSheet.Select
ActiveSheet.Copy
ThisFile = "PcikupExp" & Format(Date, "ddmmyy" & "hh:mm") & ".xlsx"
ActiveSheet.SaveAs Filename:="D:\Exp"
Application.ScreenUpdating = True
ActiveWorkbook.Close
End Sub

pls help do any part i should change or pls help

[Teeroy]

Try the following. The ":" is a restricted charater for a filename so is changed to "-" and I changed your file location for my testing.

[vba]Sub savesheet()
Dim Original_Workbooks As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Original_Workbooks = Workbooks.Count
ThisFile = "PickupExp" & Replace(Format(Now(), "ddmmyy" & "hh:mm") & ".xlsx", ":", "-")
ActiveSheet.Copy
With Workbooks(Original_Workbooks + 1)
.SaveAs Filename:="C:\Test\" & ThisFile
.Close
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub[/vba]

[KK1966]

Try the following. The ":" is a restricted charater for a filename so is changed to "-" and I changed your file location for my testing.

[vba]Sub savesheet()
Dim Original_Workbooks As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Original_Workbooks = Workbooks.Count
ThisFile = "PickupExp" & Replace(Format(Now(), "ddmmyy" & "hh:mm") & ".xlsx", ":", "-")
ActiveSheet.Copy
With Workbooks(Original_Workbooks + 1)
.SaveAs Filename:="C:\Test\" & ThisFile
.Close
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub[/vba]

Teeroy , thank very your help,
the code work with Run Time Error "1004"
Message " The file could not be accessed, try one of the following:

I do sure the file / folder haven't the symbol characters

[Teeroy]

It looks like the file coudn't be saved at the location specified. Did you change the file location? The line
[vba].SaveAs Filename:="C:\Test\" & ThisFile [/vba]
should change to
[vba].SaveAs Filename:="D:\Exp\" & ThisFile [/vba]
to save in the folder "D:\Exp\".

[KK1966]

It looks like the file coudn't be saved at the location specified. Did you change the file location? The line
[vba].SaveAs Filename:="C:\Test\" & ThisFile [/vba]
should change to
[vba].SaveAs Filename:="D:\Exp\" & ThisFile [/vba]
to save in the folder "D:\Exp\".

Changed the path, but same the error issue !!
highlighted

.SaveAs Filename:="D:\Exp\" & ThisFile

[Teeroy]

Can you please copy and post the exact code you have in the workbook?

[KK1966]

Can you please copy and post the exact code you have in the workbook?

here's exactly your code :
[VBA]Sub savesheet()
Dim Original_Workbooks As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Original_Workbooks = Workbooks.Count
ThisFile = "PickupExp" & Replace(Format(Now(), "ddmmyy" & "hh:mm") & ".xlsx", "-", ":")
ActiveSheet.Copy
With Workbooks(Original_Workbooks + 1)
.SaveAs fileName:="D:\Exp\" & ThisFile
.Close
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub[/VBA]

[Teeroy]

OK, if you have the default save set as .XLS or you start from a .XLS file you might need the file format specified. Change the .SaveAs line to

[vba].SaveAs fileName:="D:\Exp\" & ThisFile FileFormat:=51[/vba]

[KK1966]

OK, if you have the default save set as .XLS or you start from a .XLS file you might need the file format specified. Change the .SaveAs line to

[vba].SaveAs fileName:="D:\Exp\" & ThisFile FileFormat:=51[/vba]

yeah ... its work great
thanks very much your help .

[Teeroy]

You are welcome.