Posts by jindon


    you don't need to SELECT

    Private Sub CommandButton1_Click()
    Selection.PrintOut Copies:=1, Collate:=True
    ActiveWindow.ScrollRow = 1
    With Sheets("Delivery Data")
    .Rows("2").Copy destination:=.Rows("1")
    Application.CutCopyMode = False
    End With
    Sheets("Delivery Note").Select
    End Sub

    hope it works


    excuse me for interrupting.

    easy bit by the formula

    Names in Col.A
    P/L figures in Col.B

    Top 10
    then fill right and fill down to No.10

    Bottom 10
    do the same as above

    note: if there's 2 or more records which have identical P/L amount, only the first record
    will be displayed, therefore you need to modify maybe by cents.



    Worksheet_Change event shoud recognize

    try following code and insert a row somewhere

    Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Range("a1").Value = Range("a1").Value + 1
    Application.EnableEvents = True
    End Sub



    would you like to tell us the logic?



    you can rewrite as


    "row(a31) is only the substitute of 31"


    the reason why I used row(a31) is that if you set
    in cell A1, you can drag down and it increases as you go down.

    hope it helps


    here's another one


    Really sorry that I had mistake in my formula,

    should be


    please note: the formula above must be in the first row which you desired to display the data from. it doesn't matter of which ROW. If you start to display the data from A3, put the formula in A3. Row(a1) and column(a1) are not related to row and column, they are substitute of numbers and they increase as you drag. If you want to change the sheet name and criteria, YES, you can but others, absolutely NOT.



    Add new sheet

    in cell A2

    holding down Ctrl+Shift keys and press Enter to get out from the formula bar

    select cell A2 and drag to the right up to cell D2, then drag down as much as you need.

    Set the criteria in cell A1 e.g. Expense A

    copy entire sheet and add another sheet and paste, then set A1 again e.g. Expense B

    note: if you need to expand the data range, you must expand every 1000 in the formula and they must be identical.

    hope it helps


    try the code


    in cell F2
    =IF(COUNTIF(E$2:E2,E2)=1,ROW(),"") and drag down to the last row of the data in Col.A

    in cell G2
    =IF($F2<>"",SUMIF($E:$E,$E2,A:A),"") and drag to the right up to cell J2

    in cell K2

    then, select cells from G2 to K2 and drag down as long as you need.
    select entire columns from F to K --> copy --> pastescpecial/values
    sort by col.F in ascending order

    if you don't need origial data, just delete columns from A to F



    another method is to name the range like
    Sheets("SheetName").Range("A1:Z1000").name = "List"

    then you can use it in the formula. like


    if the worksheets like

    Sheet1, Sheet2, Sheet3, Sheet4, Sheet5 then
    cell in Sheet5

    Tom, Nancy, Henry, Charles, Total then
    cell in Total

    if you add new sheet in between the firstsheet and last sheet
    i.e. Sheet1 and Sheet4 or Tom and Charles
    it will be automatically included