Automatically transfer data to next sheet

[Tjomster]

Hello all

I have two different sheets and want the raw input I add into the first one to automatically transfer in to the same cell on the next sheet and in to a formula. E.g I have written =-‘Sheet1’!A1 in sheet2 to get the first input transferred and change sign, but when I add more information on the first sheet I have to go to sheet 2 and drag/pull down to transfer the additional information. Is there any way I can make the additional rows transfer automatically and in to the formula without having to go to sheet2 and pull down?

 

[mumps]

You actually don't need the formula at all. A macro could do what you want. Please explain in detail what you want to do. In which cells in Sheet1 will you be entering data?

 

[Tjomster]

It is originally a large sales report with information in cells ranging from A to P in Sheet1. In most of the cells in Sheet2 i just want the exact same inofrmation to transfer, but in some cells i also want it to change sign (-) from the original data. So when i add another another row with information on the sales report i want this to automatically transfer to the next sheet and that some cells change sign.

 

[mumps]

So you want to transfer the data you input into any cell in columns A to P to the corresponding cell in Sheet2. If this is correct, I would need to know specifically which cells would need to change signs.

 

[Tjomster]

Let's say i only want every cell in column K to change sign for now.

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your first sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter a value in A:P and exit the cell.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("A:P")) Is Nothing Then Exit Sub
    Select Case Target.Column
        Case Is = 11
            Sheets("Sheet2").Range(Target.Address) = "-" & Target
        Case Else
            Sheets("Sheet2").Range(Target.Address) = Target
    End Select
End Sub

 

[Tjomster]

Thank you for the code, but i keep getting "subscript out of range when" i enter a value in the first sheet

 

[mumps]

Which line of code is highlighted when you click "Debug"? Do you have a sheet named "Sheet2"?

 

[Tjomster]

Yes it worked now, thank you so much!

 

[mumps]

You are very welcome.