How to Automatically Copy New Data into the next sheet and have it automatically sorted??

Hey rylo,

Yeah, I just want new entries that I enter into Sheet 1 (the entire row of the new entry; no changes or conditional filter) transferred from sheet 1 to sheet 2, and then sorted by Source within sheet 2. I currently have sheet 2 sorted on 3 levels (source, then plant, then date), but that is not necessary for the automatic sort.

Hi ReedDOT

Please try following code. Data range is in between A to U. You may change based on your example.

Regards,

Narasimharao

With Worksheets("Sheet2")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).row
End With

Worksheets("Sheet1").Range("a:u").Copy _
Destination:=Worksheets("Sheet2").Cells(NextRow, "A")

Worksheets("Sheet2").Range("A:U").Sort Key1:=Worksheets("Sheet2").Range("A1"), Order1:=xlDescending, Header:=xlYes

Originally Posted by Narasimharao Nandula

Hi ReedDOT

Please try following code. Data range is in between A to U. You may change based on your example.

Regards,

Narasimharao

With Worksheets("Sheet2")
NextRow = .Cells(.Rows.Count, "A").End(xlUp).row
End With

Worksheets("Sheet1").Range("a:u").Copy _
Destination:=Worksheets("Sheet2").Cells(NextRow, "A")

Worksheets("Sheet2").Range("A:U").Sort Key1:=Worksheets("Sheet2").Range("A1"), Order1:=xlDescending, Header:=xlYes

Hey Narasimharao,

I tried changing the letters in that code (to the best of my knowledge), but I could not get it to do anything.
I am sure I did something wrong based on my ignorance of coding in excel. This is what I put in based on your reply

Private Sub Worksheet_Change(ByVal Target As Range)
With Worksheets("Sheet2")
NextRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With

Worksheets("Sheet1").Range("B:H").Copy _
Destination:=Worksheets("Sheet2").Cells(NextRow, "B")

Worksheets("Sheet2").Range("B:H").Sort Key1:=Worksheets("Sheet2").Range("B6:H32"), Order1:=xlDescending, Header:=xlYes
End Sub

Please advise

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.