summing and subtracting among sheets by code insted of formula

[abdelfattah]

hi

i truly appreciate if any body help about sum and subtract among five sheets from a2:e and show the result in sheet6 i would like this formula "=sheets1!E2:d1000+sheets2!E2:d1000-sheets13!E2:d1000-sheets4!E2:d1000+sheets5!E2:d1000 i prefer by code becuase fast and dynamic

 

[offthelip]

Your requirements are not entirely clear but does this help:

Sub test()
With Worksheets("Sheet1")
 sht1 = Range("E2:E1000")
End With
With Worksheets("Sheet2")
 sht2 = Range("E2:E1000")
End With
With Worksheets("Sheet3")
 sht3 = Range("E2:E1000")
End With
With Worksheets("Sheet4")
 sht4 = Range("E2:E1000")
End With
With Worksheets("Sheet5")
 sht5 = Range("E2:E1000")
End With
With Worksheets("Sheet6")
 sht6 = Range("E2:E1000")
For i = 1 To 999
 sht6(i, 1) = sht1(i, 1) + sht2(i, 1) - sht3(i, 1) - sht4(i, 1) + sht5(i, 1)
Next i
  Range("E2:E1000") = sht6
 

End With

 

[abdelfattah]

thanks but it gives me 0 and it should the copy data from a2 :d

 

[offthelip]

Can you be a bit more specific about which cells you want copied and summed to where

 

[abdelfattah]

it begins from a2:d all of sheets of course summing and subtracting in column d as in above image i would dynamic range

 

[abdelfattah]

sorry fro correction from a2: e

 

[offthelip]

I am still not entirely sure what you want, your explanation is a bit cryptic, try this:

Sub test()
With Worksheets("Sheet1")
sht1 = Range(.Cells(2, 1), .Cells(1000, 5))
End With
With Worksheets("Sheet2")
sht2 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet3")
sht3 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet4")
sht4 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet5")
sht5 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet6")
sht6 = Range(.Cells(2, 1), .Cells(1000, 5))
For i = 1 To 999
sht6(i, 5) = sht1(i, 1) + sht2(i, 1) - sht3(i, 1) - sht4(i, 1) + sht5(i, 1)
For j = 1 To 4
sht6(i, j) = sht1(i, j)
Next i

  Range(.Cells(2, 5), .Cells(1000, 5)) = sht6


End With
End Sub

 

[abdelfattah]

it gives me error compile error invalid next control variable reference

 

[abdelfattah]

you can see my tables in multiple sheets it supposes copy data from a2 :e and shows the result in sheet6 then should do summing and subtracting in column e this is inventory contain the purchase and sales and returns that's why I do summing and subtracting in column e I hope this help

 

[offthelip]

try this, I missed out a next j statement:
What does a2:e mean? It is unclear what this is

Sub test()
With Worksheets("Sheet1")
sht1 = Range(.Cells(2, 1), .Cells(1000, 5))
End With
With Worksheets("Sheet2")
sht2 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet3")
sht3 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet4")
sht4 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet5")
sht5 = Range(.Cells(2, 5), .Cells(1000, 5))
End With
With Worksheets("Sheet6")
sht6 = Range(.Cells(2, 1), .Cells(1000, 5))
For i = 1 To 999
sht6(i, 5) = sht1(i, 1) + sht2(i, 1) - sht3(i, 1) - sht4(i, 1) + sht5(i, 1)
For j = 1 To 4
sht6(i, j) = sht1(i, j)
Next j
Next i

  Range(.Cells(2, 5), .Cells(1000, 5)) = sht6


End With
End Sub