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

Hello ExcelForum

This is my first post.

I would like to have the NEW ENTRIES of Sheet 1 automatically transferred to Sheet 2. When in Sheet 2 I would like the data to be sorted Alphabetically by the 1st column (source column; a-z).

The data is the same in both sheets; sheet 1 is input by date; sheet 2 is sorted alphabetically.

I use excel 2010 and have never used macros before. But I have used Visual Basic before (about 3.5 years ago).

My example workbook is attached.

Thanks for all your help in advance!

Hi

As the data in both sheets is the same, does that mean you are just transferring everything from sheet1 to sheet2, and then sorting by Source and Finess Modulus?

rylo

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

Please Login or Register  to view this content.

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.

Hey nemo74,

The code definitely executed what I wanted. And that commandbutton_click1 is bringing back a little bit of my Visual Basic memory.
So now, with that code, would I have to go to "view code" and click "run" every time I finish entering data??

I put it in a UserForm with the commandbutton caption "DONE", but when that button is visible in sheet 1, I cannot enter anything into the sheet.

I like the idea of having a button for it. Is there a way to do the copy and sort automatically though after a row is completed in sheet 1.
I made the sheet for another group to use, so I am just trying to make it easy for them and difficult to mess up. I would not mind doing all this manually, but I may not be using the workbook all the time, and I do not want them to have to give me all the data everytime they finish the tests.

If it cannot be done automatically, how do I make it to where the button pops up when the workbook is opened and allows the user to enter data and click the button when done? (Maybe even save and close when the button is clicked as well.)


__
Believe it or not, I used to be good at VB. Guess that's why they say "use it or lose it".

Hi Reed,
Sorry for the long responce time, I am at work and got caught up in work lol. Here attach this code to the Sheet were they enter data. When the make a change it will replcate it on the other sheet.

Please Login or Register  to view this content.