HI Macro for Fillter and save (File name)

[Newton_N]

Hello, I need help, i have a workbook with alot on information and i want to record some macros to save me sometime and get the information i want.

1. I want a macro for filtering. I recorded a macro for filtering (select a person from the drop down and it filters the product that person sold) but it is not working properly
2. Macro for save as (filename) i want a macro that will export into PDF but save the file as Month (in A1) and Person (in C4)
Please find attached.

Thanks,

Newton

 

[bugmonsta]

to answer question 2:

This is the basic export function, which will dump the file in c:\temp (to be changed by you) with the month (a1) and name (c4)

Sub Export_ActiveSheet_as_PDF()
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, filename:= _
        "C:\Temp\" & Range("A1") & " " & Range("c4") & ".pdf", Quality:=xlQualityStandard, IncludeDocProperties:=True _
        , IgnorePrintAreas:=False, OpenAfterPublish:=True
End Sub

To expand on this, which will offer you to overwrite a file as well (I posted this last week for someone to also email it - stripped the email code out):

Sub PDFit()
Dim sht As Worksheet
Dim FFile As String
Dim AskMeYesorNo As Integer
Dim UsedRng As Range
Set sht = ActiveSheet

FFile = "c:\temp\" & Range("a1") & " - " & Range("c4") & ".pdf"
' change the above to change your location/filename etc

'Check if file already exist
If Len(Dir(FFile)) > 0 Then
AskMeYesorNo = MsgBox(Folder & " already exists." & vbCrLf & vbCrLf & "Do you want to overwrite it (say yes!)", _
vbYesNo + vbQuestion, "File Exists")
On Error Resume Next

'if you answer yes I will remove the original file
If AskMeYesorNo = vbYes Then
Kill FFile
Else
'if you answer No then you get a message
MsgBox "Yo!, I need to overwrite this file to create the new one, to attach!." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Quiting!"
Exit Sub
End If

'if I cant delete the file then I will display a message to you
If Err.Number <> 0 Then
MsgBox "Unable to delete existing file.  Please make sure the file is not open or write protected." _
& vbCrLf & vbCrLf & "Press OK to exit this macro.", vbCritical, "Unable to Delete File"
Exit Sub
End If
End If

'this is where we choose the used range of data to export
Set UsedRng = sht.UsedRange
If Application.WorksheetFunction.CountA(UsedRng.Cells) <> 0 Then

'Export as PDF file, this will also open the PDF, change to false if you dont want it to open
sht.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FFile, Quality:=xlQualityStandard, OpenAfterPublish:=True

End If


End Sub

 

[bugmonsta]

In question 1 you said you picked from a drop down list, you will need to expand on this....

in the meantime, this code presumes you have the username in column F and will ask you to enter the name. If the name does not exist then it will display a message and do nothing. If the name exists then it will filter to that entered name.

Sub Macro2()

Dim UserName As Variant
UserName = InputBox("Enter the username", "Asking for data from the user", "Please enter some text here!")

With ActiveSheet
If WorksheetFunction.CountIf(.Columns(6), UserName) <= 0 Then
MsgBox "Cant find that name in column F, is that a typo you have entered?": Exit Sub
Else
Range("F1").Select

    ActiveSheet.Range("F:F").AutoFilter Field:=1, Criteria1:=UserName
    End If
    End With
End Sub