aditya kaistha

aditya kaistha

  • 2k
  • 2
  • 154

vba excel create new sheet in excel using existing macro

May 5 2020 5:19 AM
I am having a Excel sheet having 6 lakhs of Records and when I am applying macro its records getting reduced to 15k .Please let me know how within the macro in can put all these 15k records in a new excel sheet.As mention below using macro
 
Sub DeleteRecord()
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
Dim MySheet As String
MySheet = ActiveSheet.Name
ActiveSheet.Cells(1, 1).CurrentRegion.AutoFilter Field:=33, Criteria1:= _ ">=-.09", Operator:=xlAnd, Criteria2:="<=.01"
Dim cnt As Long
cnt = Worksheets(MySheet).Cells.SpecialCells(xlCellTypeLastCell).Row ActiveSheet.Range("A2", ActiveCell.SpecialCells(xlLastCell)).Select
If cnt > 3 Then
Selection.EntireRow.Delete
End
If ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 30
ActiveSheet.Cells(1, 1).CurrentRegion.AutoFilter Field:=33 ' Range("Claims[[#Headers],[Change in Calculated Contribution]]").Select Cells(1, 33).Select Selection.AutoFilter
End Sub

Brought to you by: