Sleeper: VBA - sort data

[frade]

Hello,

Please have a look at my file.

In sheet3, when you click on the button "sort data" placed
(for information in the column O), the data are sorted by "num of repeat"
then by day and number of dilutions..Ok the first step works well! but...after... when I click on the button "REPRODUCIBILITY" , the results haved changed
(you can follow that by seeing the link between ID and result of this ID)

What can I change in my code?

Thanks a lot!

Fran?ois

[acw]

HI

Is the data in sheet3 supposed to be related to sheet2? Is it supposed to be a raw copy (at least columns A:E)?

If so, then how did the result in sheet2, cell F2 get calculated? IF it is the same formula used in the macro from the reproducibility button, then it may pay to check. When I put that formula into sheet2, I got a different answer for the average of item 1 (113 cf 8.5).


Tony

[frade]

Hi,



Thanks for your help. Yes, sheet 2 and sheet 3 are well related...the first five columns are the same(same results also).
I would like automatically to create a exact copy of this columns
directly in sheet 3. But what could I do?

Fran?ois

PS: The colum F (sheet2) is the average for the results with the same day and dilution

[acw]

Hi

So you want to create a value copy of columns A:F from Sheet2 to sheet3? Do you want it to copy on the press of a button, or when something changes on sheet 2? What is the trigger that will bring the data from sheet2 to sheet3?


Tony

[frade]

Hi,

Thanks Tony. I would like to create a copy
when something changes on sheet 2 (just when
something has changed on the five first columns)

Franks

[frade]

Do you have an idea?

Thanks

Fran?ois

[acw]

Hi

You could put a change event macro on sheet 2. Right click on the sheet2 tab, select view code and enter

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
 If Target.Column <= 5 Then
  lastrow = Sheets("sheet2").Range("a65536").End(xlUp).Row
  Sheets("sheet3").Range("a1:e" & lastrow) = Sheets("sheet2").Range("a1:e" & lastrow).Value
 End If
End Sub

There are a couple of assumptions in this.
1) The last row in sheet2 will cover any previous data in sheet3
2) Column A will always have data going to the last row.

If you are likely to have data in sheet3 that will be longer than existing data in sheet2, then it may pay to do a test of the last rows in both sheets, and take whichever is the larger value.


Tony

[frade]

Ok acw, thanks for your advice.
In this case, I prefer to keep my code to avoid mistakes
when I change the results in sheet2

fran?ois