copy sheet - save as - 400 error

elwood96

New Member
Joined
Jun 12, 2008
Messages
9
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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
chane your save code for this:
Code:
ActiveWorkbook.SaveAs (myPath & ActiveSheet.Range("G11").Value & ".xlsx")
 

elwood96

New Member
Joined
Jun 12, 2008
Messages
9
changed it to the following and it gives the same error. Is this correct? sorry to be a pain.


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

End Sub
 

elwood96

New Member
Joined
Jun 12, 2008
Messages
9

ADVERTISEMENT

its a number. and the cell is a list box.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Then you also need to check if it has value or not.
try
Code:
With ActiveWorkbook
    If ActiveSheet.Range("G11") <> "" Then
        .SaveAs myPath & ActiveSheet.Range("G11").Value & ".xlsx"
    Else
        MsgBox "Unable to save"
    End If
    .Close False
End With
 

elwood96

New Member
Joined
Jun 12, 2008
Messages
9

ADVERTISEMENT

ok. progressing, i think.

It now gives the message "unable to save"....?
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Change to
Code:
Sub SaveASheet()
Dim fName As String
Dim myPath As String
Dim sht As Worksheet
myPath = "U:\3. Design\Supply Orders\Log\"
For Each sht In ThisWorkbook.Worksheets
    If (sht.Range("D1").Value <> "") * (sht.Range("g11").Value <> "") Then
        fName = myPath & sht.Range("g11").Value & ".xlsx"
        sht.Copy
        With ActiveWorkbook
            .SaveAs fName
            .Close False
        End With
    End If
Next sht
End Sub
 

Simon Lloyd

Well-known Member
Joined
Sep 10, 2006
Messages
756
Well caught Jindon!, i'm being lazy now but does the same work if there is a formula in the cell but not returning a value? i.e something like =IF(A1="","",B1), so the cell is not actually blank if A1 is blank because HasFormula.
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Well caught Jindon!, i'm being lazy now but does the same work if there is a formula in the cell but not returning a value? i.e something like =IF(A1="","",B1), so the cell is not actually blank if A1 is blank because HasFormula.
That's why I didn't use IsEmpty function
Rich (BB code):
If (sht.Range("D1").Value <> "") * (sht.Range("g11").Value <> "") Then
And of course, he needs more to check, if the value in G11 is valid characters/length for file name or not...
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,130,167
Messages
5,640,539
Members
417,151
Latest member
ChickenTenderer

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top