How to structure the data of GSTR 2A B2B in one click for 2A reco
All you spent lots of time in data structuring in office such as remove space between the rows,remove the first few rows,Merged the cells
What if , all this done on single click
U save lots of precious time
Here is the steps
1 open the sheet
2. open the vba by ALT +F11
3. Add the module
4. copy the below code
Sub Macro1()
Rows("1:4").Select
Range("P1").Activate
Selection.Delete Shift:=xlUp
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 11
ActiveWindow.LargeScroll ToRight:=-1
ActiveSheet.Shapes.Range(Array("Picture 1")).Select
Selection.Delete
Range("C1:F1").Select
Selection.ClearContents
Range("C1:F1").Select
ActiveCell.FormulaR1C1 = ""
Range("C1:F1").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
Range("J1:J2").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("K1:N1").Select
Selection.ClearContents
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.UnMerge
ActiveWindow.ScrollColumn = 5
Range("N1").Select
ActiveCell.FormulaR1C1 = ""
Range("N1:N2").Select
Range("N2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("M1:M2").Select
Range("M2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("L1:L2").Select
Range("L2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("K1:K2").Select
Range("K2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("F1:F2").Select
Range("F2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("E1:E2").Select
Range("E2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("D1:D2").Select
Range("D2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Range("C1:C2").Select
Range("C2").Activate
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
Cells.Select
Selection.AutoFilter
Range("D1:D2").Select
ActiveSheet.Range("$A$1:$O$1043").AutoFilter Field:=4, Criteria1:="="
Rows("5:5").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
ActiveSheet.Range("$A$1:$O$697").AutoFilter Field:=4
ActiveSheet.Range("$A$1:$O$697").AutoFilter Field:=9, Criteria1:=Array("0", _
"12", "18", "28", "5", "="), Operator:=xlFilterValues
Rows("3:3").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Delete Shift:=xlUp
Range("A346").Select
ActiveSheet.Range("$A$1:$O$345").AutoFilter Field:=9, Criteria1:="<>"
Columns("C:C").Select
Selection.Replace What:="-total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
With Selection
.HorizontalAlignment = xlLeft
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.SmallScroll Down:=171
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
End Sub
paste it in module.
5. then minimize the visual basic and select the B2B sheet only (not any other , or you lost the data )
6. then press ALT + F11 and then press F5
you get your sheet ready
All this code are verified
Only for B2B sheet , not any other sheet
Precaution : only B2B sheet open
If any of your data is lost , we are not responsible for your loss
For safety apply it on duplicate file , not in original file
Comments
Post a Comment