Creating Dynamic Reports from Excel Data using VBA,VBA Teacher Sourav,Kolkata 08910141720

Dim sdsheet, ersheet As Worksheet

k = ActiveWorkbook.Sheets.Count

    For i = k To 1 Step -1
        t = Sheets(i).Name
        If t = "Emp_rpt_insurance" Then
            Application.DisplayAlerts = False
                Sheets(i).Delete
            Application.DisplayAlerts = True
          
        End If
    Next i
On Error Resume Next
  ActiveWorkbook.Sheets.Add(After:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)).Name = "Emp_rpt_insurance"
Set sdsheet = ThisWorkbook.Sheets("Sortsheet")
Set ersheet = ThisWorkbook.Sheets("Emp_rpt_insurance")
If sdsheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
sdlr = 2
Else
sdlr = sdsheet.Cells(Rows.Count, 1).End(xlUp).Row
End If
y = 2

ersheet.Cells(1, 1) = "Emp ID"
ersheet.Cells(1, 2) = "First Name"
ersheet.Cells(1, 3) = "Last Name"
ersheet.Cells(1, 4) = "Address"
ersheet.Cells(1, 5) = "Zipcode"
ersheet.Cells(1, 6) = "Mail"
ersheet.Cells(1, 7) = "Date Of Birth"
ersheet.Cells(1, 8) = "Phone"








For x = 2 To sdlr
If (UCase(sdsheet.Cells(x, 14)) = "A") And (CInt(sdsheet.Cells(x, 17)) >= 40) Then
ersheet.Cells(y, 1) = sdsheet.Cells(x, 1)

ersheet.Cells(y, 2) = sdsheet.Cells(x, 2)

ersheet.Cells(y, 3) = sdsheet.Cells(x, 3)

ersheet.Cells(y, 4) = sdsheet.Cells(x, 5)

ersheet.Cells(y, 5) = sdsheet.Cells(x, 9)

ersheet.Cells(y, 6) = sdsheet.Cells(x, 12)

ersheet.Cells(y, 7) = sdsheet.Cells(x, 15)

ersheet.Cells(y, 8) = "XXX-XXX-" & Right(sdsheet.Cells(x, 10), 4)

y = y + 1
End If



Next x


ersheet.Cells.Columns.AutoFit

'If ersheet.Cells(Rows.Count, 1).End(xlUp).Row = 1 Then
'erlr = 2
'Else
'erlr = ersheet.Cells(Rows.Count, 1).End(xlUp).Row
'End If
'