Macro

[shaunkaz]

I have two worksheets, History and Current.

The current worksheet is updated with orders every day and what I do at the moment is copy the New orders from the previous day and paste them into the History worksheet

What i want for example is on the current sheet look at order date

Order date
row 50 21 June
row 51 21 June
row 52 22 June
row 53 23 June


On the History sheet
Order date
row 3750 21 June
row 3751 21 June

What I would do then is lookup to see what dates i have not got so in this case on the history i have not got 22 june or 23 june. I would copy these lines and paste into the history sheet and the next available blank line.

 

[shaunkaz]

Can anyone please help me with this formula

Thanks

 

[VoG]

Try like this. Note that History is a reserved name for a sheet so I used Sheet 2 - change to suit

Sub Hstry()
Dim LR As Long, i As Long
With Sheets("Current")
    LR = .Range("B" & Rows.Count).End(xlUp).Row
    For i = 2 To LR
        With .Range("B" & i)
            If .Value < Date Then .EntireRow.Cut Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End With
    Next i
    On Error Resume Next
    .Range("B2:B" & LR).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End With
End Sub

 

[Sektor]

Try this (adjust names).

[COLOR="Blue"]Sub[/COLOR] GoToHistory()

    [COLOR="Blue"]Dim[/COLOR] i [COLOR="Blue"]As[/COLOR] [COLOR="Blue"]Integer[/COLOR]
    [COLOR="Blue"]Dim[/COLOR] shHistory [COLOR="Blue"]As[/COLOR] Worksheet, shCurrent [COLOR="Blue"]As[/COLOR] Worksheet
    [COLOR="Blue"]Dim[/COLOR] rngHistory [COLOR="Blue"]As[/COLOR] Range
    
    [COLOR="Blue"]Set[/COLOR] shHistory = Sheets("History")
    [COLOR="Blue"]Set[/COLOR] shCurrent = Sheets("Current")
    
    [COLOR="Blue"]With[/COLOR] shHistory
        [COLOR="Blue"]Set[/COLOR] rngHistory = .Range("A1", .Range("A1").End(xlDown))
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]
    
    [COLOR="Blue"]With[/COLOR] shCurrent
        [COLOR="Blue"]For[/COLOR] i = 1 [COLOR="Blue"]To[/COLOR] .Range("A1").End(xlDown).Row
            [COLOR="Blue"]If[/COLOR] WorksheetFunction.CountIf(rngHistory, .Cells(i, "A")) > 0 [COLOR="Blue"]Then[/COLOR]
                shHistory.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) = .Cells(i, "A")
            [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]If[/COLOR]
        [COLOR="Blue"]Next[/COLOR]
    [COLOR="Blue"]End[/COLOR] [COLOR="Blue"]With[/COLOR]

[COLOR="Blue"]End[/COLOR] [COLOR="Blue"]Sub[/COLOR]