Hi
I have two Sheets, Data and Summary I am trying to summarise the information on the Data sheet and remove Data that has no data. I want to copy columns of data to the Summary sheet and put the information at the bottom. Can’t seem to work it out. Process is below for you all.
Thanks
Wendy
I have two Sheets, Data and Summary I am trying to summarise the information on the Data sheet and remove Data that has no data. I want to copy columns of data to the Summary sheet and put the information at the bottom. Can’t seem to work it out. Process is below for you all.
Thanks
Wendy
Code:
Sub Macro1()
'
' Macro1 Macro
'
'
Range("O2").Select
ActiveCell.FormulaR1C1 = ""
Range("N2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*1000"
Selection.AutoFill Destination:=Range("N2:N50"), Type:=xlFillDefault
Range("N2:N50").Select
ActiveWindow.SmallScroll Down:=-38
Range("N2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.SmallScroll Down:=-18
Range("M2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Style = "Comma"
Selection.NumberFormat = "_-$* #,##0.0_-;-$* #,##0.0_-;_-$* ""-""??_-;_-@_-"
Selection.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
Range("A:A,M:M").Select
Range("M1").Activate
Selection.Copy
Sheets("Summary").Select
ActiveWindow.SmallScroll Down:=12
Range("A50").Select
ActiveWindow.SmallScroll Down:=-16
Sheets("Data").Select
ActiveWindow.SmallScroll Down:=-6
Range("A2:A50").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
ActiveWindow.SmallScroll Down:=-6
Range("A2").Select
Selection.End(xlDown).Select 'Add one row
ActiveSheet.Paste
Sheets("Data").Select
Range("M2:M50").Select
Range("M50").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("B2").Select
Selection.End(xlDown).Select 'Add one row
ActiveSheet.Paste
Range("A1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Data"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Amt"
Range("B1").Select
ActiveWorkbook.Worksheets("Summary").Sort.SortFields.Clear
Range("A1:C26").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Data").AutoFilter.Sort.SortFields.Add Key:=Range _
("M2:M50"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Data").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("A1").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*0.15"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C50"), Type:=xlFillDefault
Range("C2:C50").Select
ActiveWindow.SmallScroll Down:=-32
Range("C1").Select
ActiveCell.FormulaR1C1 = "Commission"
Range("C2").Select
Columns("C:C").EntireColumn.AutoFit
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Style = "Percent"
ActiveCell.FormulaR1C1 = "=RC[-1]*0.15"
Range("C2").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWindow.SmallScroll Down:=-20
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C50")
Range("C2:C50").Select
Selection.Style = "Currency"
Selection.NumberFormat = "_-$* #,##0.0_-;-$* #,##0.0_-;_-$* ""-""??_-;_-@_-"
Selection.NumberFormat = "_-$* #,##0_-;-$* #,##0_-;_-$* ""-""??_-;_-@_-"
Range("B2").Select
'zero
Dim x As Integer
For x = 2 To 100
If Range("B" & "X") = 0 Then
Range("X").Select
Range("X").Row.Delete
End If
Next
End Sub