Code to save sheet as

[chammy88]

Hello,

I have looked through some od the quesitons on Mr Excel but can't find a precise answer to what I'm looking for.

I have a daily report that is automated. At the end of code I've already written I want to report to be Saved As.

I have got a message box that comes up saying "would you like to save the daily report?" with Yes, No or Cancel buttons.

If yes is selected I would like the Save As dialogue box to open. I want the dialogue box to open to the correct "Save in" Folder and already be set to "Save as Type" PDF, so that the user just has to type the name of the file being saved as the date that they make the report.

Is this possible???

Thanks in advance

 

[Trevor G]

Try something like this

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> msg1()<br><SPAN style="color:#00007F">Dim</SPAN> ReturnValue <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> filesaveName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>ReturnValue = MsgBox("Do you want to save as PDF", vbYesNoCancel, "Test")<br><br><SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> ReturnValue<br><SPAN style="color:#00007F">Case</SPAN> vbYes:<br>filesaveName = Application.GetSaveAsFilename( _<br>fileFilter:="PDF Files (*.pdf), *.pdf")<br><SPAN style="color:#00007F">If</SPAN> filesaveName <> <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>MsgBox "Save as " & filesaveName<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">Case</SPAN> vbNo<br><SPAN style="color:#007F00">' do stuff</SPAN><br><SPAN style="color:#00007F">Case</SPAN> vbCancel<br><SPAN style="color:#007F00">' do stuff</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[chammy88]

that worked really well, thank you very much

You may also be able to help me with something else.

If i've got 3 arrays, call them Location, Cause and Duration and I want the code to loop through the arrays and for every time the location and cause are equal, to add their durations.

The example below shows that the arrays to end with show all locations, causes and durations but whenever the Location and cause are equal the durations are summed.


eg.
start with:
Location
Plant
Crusher
Stacker
Crusher

Cause
Breakdown
Hosing
Bearing
Hosing

Duration
5
4
6
1

End with:

Location
Plant
Stacker
Crusher

Cause
Breakdown
Bearing
Hosing

Duration
5
6
5

 

[Arul.rajesh]

Sub arra()
Location = Array("Plant", "Crusher", "Stacker", "Crusher")
cause = Array("Breakdown", "Hosing", "Bearing", "Hosing")
Duration = Array(5, 6, 4, 1)
Dim MaKiAnkh     As New Collection
Dim uL, uC, uD   As Integer
Dim Result()         As Variant
Set w = Application.WorksheetFunction
uL = UBound(Location)
uC = UBound(cause)
uD = UBound(Duration)
Dim SumRange() As Variant
ReDim SumRange(1 To uL)
If uC = uL And uC = uD Then
    For i = 1 To uL
    On Error Resume Next
    MaKiAnkh.Add Item:=Location(i) & "|" & cause(i), Key:=Location(i) & cause(i)
    Err.Clear
    SumRange(i) = Location(i) & "|" & cause(i)
    Next i
On Error GoTo 0
Else
Exit Sub
End If
co = MaKiAnkh.Count
ReDim Result(1 To co, 1 To 3)
For j = 1 To co
Num = MaKiAnkh(j)
Result(j, 1) = Left$(MaKiAnkh(j), w.Find("|", MaKiAnkh(j)) - 1)
Result(j, 2) = Right$(MaKiAnkh(j), Len(MaKiAnkh(j)) - w.Find("|", MaKiAnkh(j)))
du = 0
For K = 1 To uD
    If SumRange(K) = MaKiAnkh(j) Then
    du = du + Duration(K)
    End If
Next K
Result(j, 3) = du
Next j
End Sub

The result array will contain your Array... I think you meant 5,7,4