Hi there,
So I put the whole thing on a command Button to make is easier to run after you enter the new data
Private Sub CommandButton1_Click()
'Open a With structure for Sheet2
With Worksheets("Sheet2")
'Declare variables for ListBox items and NextRow
Dim NextRow As Long
'Clear Sheet2
.UsedRange.Clear
'Define the NextRow variable as the direction to copy the cells.
NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row
'Close the With structure for Sheet2.
End With
'copying cells from sheet 1 to sheer 2
Worksheets("Sheet1").Range("B:H").Copy _
Destination:=Worksheets("Sheet2").Cells(NextRow, "b")
'sorting the cells onces they are moved. Will need to adjust the H colum if data grows outside of the range.
Worksheets("Sheet2").Range("B6:H200").Sort Key1:=Worksheets("Sheet2").Range("b6"), Order1:=xlAscending, Header:=xlYes
End Sub
This is my first VBA helping post, and I'm using this to learn VBA better, so there it my not be written the best, but it worked on my mock up. Let me know how it works for you.
Bookmarks