narsing rao
Member
Hi,
i have created a macro for report generation , in this process i will copy data from one sheet to another . once i copy, rows below the copied part has to be deleted as they are not required, in this macro Rows("28:29").Select are showing as deleted because i have copied the data till 27th row remaining two rows(28 and 29 ) from previous days report those i don't want in this today's report so i have deleted them.but i need this to be selected dynamically because count of rows may change daily.
this is created purely using macros please bare with code.
i have created a macro for report generation , in this process i will copy data from one sheet to another . once i copy, rows below the copied part has to be deleted as they are not required, in this macro Rows("28:29").Select are showing as deleted because i have copied the data till 27th row remaining two rows(28 and 29 ) from previous days report those i don't want in this today's report so i have deleted them.but i need this to be selected dynamically because count of rows may change daily.
this is created purely using macros please bare with code.
Code:
Sub Macroubpsreport()
'
' Macroubpsreport Macro
'
'
Cells.Select
Application.CutCopyMode = False
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.MergeCells = False
End With
Columns("A:A").Select
Selection.Delete Shift:=xlToLeft
Columns("H:H").Select
Selection.Delete Shift:=xlToLeft
Columns("I:J").Select
Selection.Delete Shift:=xlToLeft
Columns("K:N").Select
Selection.Delete Shift:=xlToLeft
Columns("L:M").Select
Selection.Delete Shift:=xlToLeft
Columns("N:N").Select
Selection.Delete Shift:=xlToLeft
Columns("O:Q").Select
Selection.Delete Shift:=xlToLeft
Columns("P:AG").Select
Selection.Delete Shift:=xlToLeft
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 1
Rows("1:1").Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$AU$1822").AutoFilter Field:=9, Criteria1:=Array( _
"With Assignee", "With CCB Approver After Patch Initiation", _
"With Consultation Team for Ownership", _
"With Release Manager After RCD Initiation", _
"With Release Manager Team For RCD Approval", "With Requestor For Clarification" _
, "With Reviewer For Clarification", "With Support Team for Ownership"), _
Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AU$1822").AutoFilter Field:=2, Criteria1:=Array( _
"ANDHRABANK", "DENA", "EDBE", "EXIML3SAS_FINATS", "ICICI", "KBL", "NIBL", _
"NKGSBINDIA_FINIMPL", "PNB", "SOHAR_IMPL", "VB"), Operator:=xlFilterValues
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
Range("O2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.TextToColumns Destination:=Range("O2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="d", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
Sheets("SLA Status").Select
Range("B2").Select
ActiveSheet.Paste
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 11
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 13
Rows("28:29").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("E19").Select
ActiveWorkbook.RefreshAll
End Sub