Automatic filtering of excel data and save the filtered data in a pdf using VBA ,VBA Teacher Sourav,Kolkata 09748184075

Sub filter_pdf()

'We need to remove all filters first

Workbooks(ActiveWorkbook.Name).Activate
    Sheets("Sheet1").Activate
    Dim str3 As String



   
ActiveSheet.Cells.Select

Selection.ClearFormats

If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
    ActiveSheet.ShowAllData
End If

'Now we need to get the data by which we are going to filter

Dim Message, Title, Default
Message = "Enter a value "    ' Set prompt.
Title = "InputBox Demo"    ' Set title.
Default = "1"    ' Set default.
' Display message, title, and default value.
str3 = InputBox(Message, Title, Default)

'We need to find the address of the range to be filtered which is expanding horizantally


    Sheets("Sheet1").Activate
ActiveSheet.Range("A1").Select
   
    Dim i As Long
    Dim count As Integer

    Dim str1 As String
    For i = 1 To 500000
    If ActiveCell.Value = "Sales" Then
    str1 = ActiveCell.Address
    Exit For
    Else
    ActiveCell.Offset(0, 1).Select
End If

   
   
   
   
   
    Next i
    MsgBox (str1)
   ActiveSheet.Range(str1).Select
    For i = 1 To 500000
    If ActiveCell.Value = "" Then
   
    Exit For
    Else
    str1 = ActiveCell.Address
   
   
    ActiveCell.Offset(1, 0).Select
End If

   
   
   
   
   
    Next i
   
 
   MsgBox (str1)
  
    Dim str2 As String
    str2 = "A1:" & str1
    MsgBox (str2)
   Selection.Clear
   ActiveSheet.Range(str2).AutoFilter Field:=1, Criteria1:=str3, Operator:=xlAnd
  
  
   'Now the filtered data is presented in the sheet.we need to convert this result into a pdf
  
   Dim customer_code As String
Dim pdffolder As FileDialog

customer_code = Range("A2").Text


Set pdffolder = Application.FileDialog(msoFileDialogFolderPicker)
pdffolder.AllowMultiSelect = False
pdffolder.Show

  
   
        Dim dir As String
        dir = pdffolder.SelectedItems(1)
   
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=dir & "\" & customer_code & ".pdf", openafterpublish:=False
               
        MsgBox ("PDF Generated")
               
     
       

End Sub

Sort an user defined portion of excel table or range or data using VBA,VBA Teacher Sourav,Kolkata 8910141720

Sub Macro1()
'
' Macro1 Macro
'

'
Dim selectrange As Range
Dim sorton As Range
Set selectrange = Application.InputBox(prompt:="select the cells for the range", Type:=8)

Set sorton = Application.InputBox(prompt:="select the cells for the range to be sorted", Type:=8)
  
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range(sorton.Address) _
        , sorton:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range(selectrange.Address)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Convert a complete or partial worksheet of excel to pdf using VBA ,VBA Teacher Sourav,Kolkata 8910141720

Private Sub CommandButton1_Click()

Dim code As String
Dim mtd As String
Dim pdffolder As FileDialog

code = Range("m3").Text
mtd = Range("u7").Text

Set pdffolder = Application.FileDialog(msoFileDialogFolderPicker)
pdffolder.AllowMultiSelect = False
pdffolder.Show

    If Range("m3") = "" Then
   
        MsgBox ("Please Enter a Stockist Code")
       
    Else
   
        Dim dir As String
        dir = pdffolder.SelectedItems(1)
   
        ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=dir & "\" & code & "_" & mtd & ".pdf", openafterpublish:=False
               
        MsgBox ("PDF Generated")
               
        End If
       
End Sub

Use a variable inside find command in VBA,VBA Teacher Sourav,Kolkata 09748184075

Suppose I have a string variable containing subham$,to get the position of the dollar symbol we can use findcommand in excel ,however in VBA we have to use find like this




s = "subham$"
  x = Application.WorksheetFunction.Find("$", s)

s = Application.WorksheetFunction.Find("$", Range("A1"))



s = WorksheetFunction.Find("$", Range("A1"))

s = Application.Find("$", Range("A1"))

s = InStr(s, "$") 

ActiveCell.Formula = "=FIND(""$""," & """" & s & """" & ")"


 Sheets("Sheet1").Range("B1") = WorksheetFunction.Find("$", s, 1) 



Range("D1").FormulaR1C1 = "=FIND(""$"",""" & s & """)"