Change data based on new data on different worksheet

[lawrence24]

Hi guys,

I've got two sheets. One is a month view, where each row represents the current day. So say, Row 1(A) I enter 300. On the second sheet, I need a specific cell to automatically take that number. The next day, when I enter a new number in Row 2(A), I need the same cell on the second sheet to erase what it had before, and change to the new one. Does that make sense?

Thanks,
Law

 

[Arithos]

Hi guys,

I've got two sheets. One is a month view, where each row represents the current day. So say, Row 1(A) I enter 300. On the second sheet, I need a specific cell to automatically take that number. The next day, when I enter a new number in Row 2(A), I need the same cell on the second sheet to erase what it had before, and change to the new one. Does that make sense?

Thanks,
Law

Yeah, it does. You basicly need a specific cell on sheet 2, to use the newest data inputted in Column A, from the bottom? Will there be any other information below the entry in column A?

You could do a on change calculate macro for your worksheet.

 

[lawrence24]

Yeah, it does. You basicly need a specific cell on sheet 2, to use the newest data inputted in Column A, from the bottom? Will there be any other information below the entry in column A?

You could do a on change calculate macro for your worksheet.

Nope, nothing below. So from Row 1 to 30 (the whole month), I will have values. What I need is, when I enter into each one, the latest one should be taken into the second sheet. So, on the 30th of the month, on the main sheet, I have all the values for the whole month, but on the second sheet, I just have what I entered for the 30th.

How would I do this?

Thanks!

 

[lawrence24]

Anyone?

 

[Arithos]

Anyone?

Hi mate, sorry for the delay =(


Insert this code into your sheet, I use sheet1 and sheet2 as reference, change as you wish.

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("[COLOR=#ff0000]A1:A30"[/COLOR])) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
Else
    Sheets([COLOR=#ff0000]"Sheet2"[/COLOR]).Range([COLOR=#ff0000]"A1"[/COLOR]).Value = Sheets([COLOR=#ff0000]"Sheet1"[/COLOR]).Cells(Rows.Count, 1).End(xlUp).Value
End If


End Sub

This code works as follows, if you change one value in the range "A1:A30" , the first value (from the bottom of column A, will be inserted into cell A1 on sheet2, (change the sheetnames/ranges to fit).

For this specific macro to work you cant have anything below in column A, there is surely a workaround on that, but I dont know it.


Ok? Questions?

 

[Arithos]

Ok, figured it out.

Very intuitive really.. :S

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("A1:A30")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
Else
    Sheets("Sheet2").Range("A1").Value = Target.Value
End If


End Sub

This will replace A1 sheet 2, with any number changed in Range(A1:A30) in sheet 1

 

[lawrence24]

Ok, figured it out.

Very intuitive really.. :S

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("A1:A30")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
Else
    Sheets("Sheet2").Range("A1").Value = Target.Value
End If


End Sub

This will replace A1 sheet 2, with any number changed in Range(A1:A30) in sheet 1

That worked perfectly, thank you so much! Seriously! You saved me from going crazy!

I've got just one more question though! Now I have another sheet, and I need that one to update row by row whatever I enter into the first sheet. So basically, if I enter into A1 in Sheet 1, it should make the exact same thing on Sheet 3, A1, then, I enter into Sheet 1 A2, and the same thing should be in Sheet 3 A2. Is that possible? I tried changing your code, but alas, I have no clue what I'm doing.

Thanks again for helping!

 

[Arithos]

That worked perfectly, thank you so much! Seriously! You saved me from going crazy!

I've got just one more question though! Now I have another sheet, and I need that one to update row by row whatever I enter into the first sheet. So basically, if I enter into A1 in Sheet 1, it should make the exact same thing on Sheet 3, A1, then, I enter into Sheet 1 A2, and the same thing should be in Sheet 3 A2. Is that possible? I tried changing your code, but alas, I have no clue what I'm doing.

Thanks again for helping!

Wonderful.

Hehe,

One simple way to do this, is hold in CTRL and click on sheet1, and sheet3, then everything you do in sheet1 will be done in the exact same way in sheet3. If that is too much "hassle", then you basicly want range(A1:A30) to be the same in both sheets?

Change this line:

Sheets("Sheet2").Range("A1").Value = Target.Value

To this

[COLOR=#333333][I]Sheets("[/I][/COLOR][COLOR=#FF0000][I]Sheet3[/I][/COLOR][COLOR=#333333][I]").Range([/I][/COLOR][COLOR=#FF0000][I]cells(1,1),cells(30,1))[/I][/COLOR][COLOR=#333333][I].Value = [/I][/COLOR][COLOR=#333333][I]Sheets("[/I][/COLOR][COLOR=#FF0000][I]Sheet1[/I][/COLOR][COLOR=#333333][I]").Range([/I][/COLOR][COLOR=#FF0000][I]cells(1,1),cells(30,1)[/I][/COLOR][COLOR=#333333][I]).Value[/I][/COLOR]

I havent tested it, but should do the right thing.

 

[Arithos]

[COLOR=#333333][I]Sheets("[/I][/COLOR][COLOR=#FF0000][I]Sheet3[/I][/COLOR][COLOR=#333333][I]").Range([/I][/COLOR][COLOR=#FF0000][I]cells(1,1),cells(30,1))[/I][/COLOR][COLOR=#333333][I].Value = [/I][/COLOR][COLOR=#333333][I]Sheets("[/I][/COLOR][COLOR=#FF0000][I]Sheet1[/I][/COLOR][COLOR=#333333][I]").Range([/I][/COLOR][COLOR=#FF0000][I]cells(1,1),cells(30,1)[/I][/COLOR][COLOR=#333333][I]).Value[/I][/COLOR]

Did this work?
Didnt work on my end, I had to do a workaround for some reason, ended up with this.

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(Target, Range("A1:A30")) Is Nothing Or Target.Cells.Count > 1 Then
Exit Sub
Else
    Sheets("Sheet2").Range("A1").Value = Target.Value
        Sheets("Sheet1").Range(Cells(1, 1), Cells(30, 1)).Copy
        Sheets("Sheet3").Range("A1").PasteSpecial (12)
        Application.CutCopyMode = False
End If


End Sub

This does both operations in the same macro.

 

[lawrence24]

This worked! Oh my god, thank you so much! If you were anywhere nearby, I'd buy you a year's supply of beer! :D Thanks again!