Automatically transfer data to next sheet

Tjomster

New Member
Joined
Oct 5, 2018
Messages
8
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?
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.

Code:
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
 
Upvote 0
Thank you for the code, but i keep getting "subscript out of range when" i enter a value in the first sheet :confused:
 
Upvote 0
Which line of code is highlighted when you click "Debug"? Do you have a sheet named "Sheet2"?
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,184
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top