Macro to Subtract from Previous Tab

[spyldbrat]

Hello,

I have a spreadsheet with multiple tabs. Each week, I add a new tab. The columns on all tabs contain the same exact data type in the same column (ex, col A on all tabs is the customer #).

I need a macro that will look at col A (customer #) on the current tab, then go previous tab, look in col A and locate that customer number, then look at column c and subtract the difference from the current and prev tabs. The result will be in the current tabs col D. I need the macro to subtract the following columns and display the result on current tab.

Subtract Display In
Col C Col D
Col E Col F
Col G Col H
Col I Col J
Col K Col L
Col M Col N

I tried to create this but failed miserably...

 

[Takae]

Please copy/past on Sheet Module of current TAB.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, wsN As Long
    Dim FC
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target.count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    wsN = ActiveSheet.Index
    Set FC = Sheets(wsN - 1).Range("A:A").Find(What:=Target.Value)
        If FC Is Nothing Then
            Exit Sub
        Else
            For i = 4 To 14 Step 2
            cells(Target.row, i).Value = cells(Target.row, i - 1).Value - Sheets(wsN - 1).cells(FC.row, i - 1).Value
            Next
        End If
End Sub

Colunm D of Current TAB = (Colunm C of Current TAB) - (Colunm C of previous TAB)
You can copy this worksheet every week. It carries the code to new worksheet. It does not correspond to the case of changing multiple cells simultaneously.
Hope this helps.

 

[spyldbrat]

Hi. I am having a hard time 'creating" this macro. When I go to"Macros" and I try typing the name in the Macro Name box, it won't let me create it unless I add the underscore to all the spaces on the first line. If I hit Alt F11 it opens teh Sheet Module but it shows me the coding for a macro that is already there. I know I am doing something wrong, I just don't what...

 

[Takae]

Not Alt+F11.
Right click on current sheet name. Then you can see "SheetModule"(I don't know correct English expression) . Please paste this code on the module.
You don't need to run the macro. If you type something on columnA, it runs automatically.

 

[spyldbrat]

I got an error:

Run-Time error 9:
Subscript out of range

This is what is highlighted:

Set FC = Sheets(wsN - 1).Range("A:A").Find(What:=Target.Value)

 

[Takae]

The current sheet must be the second and the subsequent sheet. I think you put the code on first sheet module.
And please replace with this code.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim i As Long, wsN As Long
    Dim FC
    Application.EnableEvents = False
    If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
    If Target.count > 1 Then Exit Sub
    If Target.Value = "" Then Exit Sub
    
    wsN = ActiveSheet.Index
    Set FC = Sheets(wsN - 1).Range("A:A").Find(What:=Target.Value, LookAt:=xlWhole)
        If FC Is Nothing Then
            Exit Sub
        Else
            For i = 4 To 14 Step 2
            cells(Target.row, i).Value = cells(Target.row, i - 1).Value - Sheets(wsN - 1).cells(FC.row, i - 1).Value
            Next
        End If
        Application.EnableEvents = True
End Sub

 

[spyldbrat]

So now it's not running at all .

I have my most recent tab as the last tab. When I right click my the most recent tab, I am selecting "VIEW CODE". From there, I am pasting your code. I then typing something in cell A1 and nothing is happening.


I don't know how to add screen shots to this or I would send you what I am looking at.