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
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