PDA

View Full Version : help save worksheet to new wb



KK1966
03-26-2013, 08:47 PM
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
03-26-2013, 10:56 PM
Try the following. The ":" is a restricted charater for a filename so is changed to "-" and I changed your file location for my testing.

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

KK1966
03-26-2013, 11:15 PM
Try the following. The ":" is a restricted charater for a filename so is changed to "-" and I changed your file location for my testing.

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


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

:( :bow:

Teeroy
03-26-2013, 11:27 PM
It looks like the file coudn't be saved at the location specified. Did you change the file location? The line
.SaveAs Filename:="C:\Test\" & ThisFile
should change to
.SaveAs Filename:="D:\Exp\" & ThisFile
to save in the folder "D:\Exp\".

KK1966
03-26-2013, 11:44 PM
It looks like the file coudn't be saved at the location specified. Did you change the file location? The line
.SaveAs Filename:="C:\Test\" & ThisFile
should change to
.SaveAs Filename:="D:\Exp\" & ThisFile
to save in the folder "D:\Exp\".


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

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

Teeroy
03-26-2013, 11:59 PM
Can you please copy and post the exact code you have in the workbook?

KK1966
03-27-2013, 12:04 AM
Can you please copy and post the exact code you have in the workbook?
here's exactly your code :
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

Teeroy
03-27-2013, 01:59 AM
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

.SaveAs fileName:="D:\Exp\" & ThisFile FileFormat:=51

KK1966
03-27-2013, 02:19 AM
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

.SaveAs fileName:="D:\Exp\" & ThisFile FileFormat:=51



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

Teeroy
03-27-2013, 02:22 AM
You are welcome.