copy sheet - save as - 400 error

[elwood96]

Hi there,

When I test code below in blue in a blank worksheet it works fine and copies the relevant sheet to a new workbook and saves the file automatically. Great!

However, when I use this code in the workbook I want it gets as far as creating a new workbook and pasting the data, but then an error comes up saying nothing but "400", and does not complete the saving part.

I would greatly appreciate your help.


Private Sub Workbook_Open()
Sheets("Open").Activate
End Sub
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
myPath = "K:\3. Design\Supply Orders\Log\"
For Each sht In ThisWorkbook.Worksheets
If sht.Range("D1").Value <> "" Then
sht.Copy
With ActiveWorkbook
.SaveAs myPath & ActiveSheet.Range("G11").Value & ".xlsx"
.Close
End With
End If
Next sht

End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
If SaveAsUI = True Then
MsgBox "Please use 'Save' to Save this Workbook...", vbCritical, "Your title"
Cancel = True
End If
End Sub
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Range("f13").Value = "Update Register" Then
Cancel = True
MsgBox "You must complete the register before printing"
End If
End Sub

 

[elwood96]

hi jindon and simon,

thanks very much for your help. the code is now wokring very well.

apologies for not replying earlier to your solutions.