Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi save
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Multi save
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] Lawson wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to find a value with multi-column, multi-record list | Excel Worksheet Functions | |||
need to save multi lingual content from an Excel file to a CSV fil | Excel Discussion (Misc queries) | |||
Complex Multi-condition, multi-workbook count | Excel Discussion (Misc queries) | |||
how can I paste multi-line/multi-paragraph data into ONE cell? | Excel Discussion (Misc queries) | |||
Extract values from a multi-select multi-column list-box | Excel Programming |