Multi save

  • Thread starter Thread starter Lawson
  • Start date Start date
L

Lawson

this program is supposed to save each open spreadsheet in
the approproate spot on the server when the user enters a
job # in cell h251, but it keeps refering back to the
first spreadsheet and saving it as many times as there
are spreadsheets open....

Private Sub CommandButton11_Click()
If Range("k265") = 1 Then '(if only one sheet is open)
If Range("h251") = "Job #" Then Exit Sub
ActiveWorkbook.SaveAs Filename:="\\AAA-server-1
\estimate\Quotes\EST" & Range("q262") & "000\EST" & Range
("q262") & Range("q263") & "00\" & Range("h251")
& ".xls", FileFormat:=xlNormal, Password:="",
WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False
Else
For Each w In Application.Workbooks: w.Activate:
If Range("q297") = 0 Then
MsgBox "There are some files not linked."
Exit Sub
Else
ActiveWorkbook.SaveAs Filename:="\\AAA-server-1
\estimate\Quotes\EST" & Range("q262") & "000\EST" & Range
("q262") & Range("q263") & "00\" & Range("h251")
& ".xls", FileFormat:=xlNormal, Password:="",
WriteResPassword:="", ReadOnlyRecommended:=False,
CreateBackup:=False
End If
Next w
End If
End Sub
 
Hi Lawson,

You need to be careful with fully-qualifying your range references. Since
the code resides in a Worksheet module, unqualified Range references will
end up referring to ranges on the Worksheet that holds the CommandButton.

Also, in your For Each...Next loop, you are using ActiveWorkbook.SaveAs -
just change this to w.SaveAs, and it should work. If your check of Q297 is
supposed to be looking in each workbook as you loop through them, you should
use w.Sheets("<worksheetname>").Range("Q297") instead of just Range("Q297").

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Back
Top