The task I am trying to accomplish is to sum the totals amount and Partial amounts In specific cells
I tried to complete my idea but could not do it .... for illustration
This code to transpose the last row from horizontal to vertical ( final total row - The row is not fixed )
from the two sheets cash sales & Deferred Sales to the two sheets Total cash sales & Total Deferred Sales.
Code: Select all
Option Explicit
Sub TransposeTotals()
Dim sourceRange As Range
Dim sourceCell As Range
Dim targetCell As Range
Dim lastRow As Long
Dim dValue
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 8
Select Case i
Case 1
With Sheets("cash sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("B1:AC1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total cash sales").Range("A8")
Case 2
With Sheets("cash sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("AI1:AN1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total cash sales").Range("G8")
Case 3
With Sheets("cash sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("AP1:AR1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total cash sales").Range("G15")
Case 4
With Sheets("cash sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("AV1:BW1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total cash sales").Range("G21")
Case 5
With Sheets("Deferred Sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("B1:AC1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total Deferred Sales").Range("A8")
Case 6
With Sheets("Deferred Sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("AI1:AN1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total Deferred Sales").Range("G8")
Case 7
With Sheets("Deferred Sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("AP1:AR1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total Deferred Sales").Range("G15")
Case 8
With Sheets("Deferred Sales")
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
Set sourceRange = .Range("AV1:BW1").Offset(lastRow - 1, 0)
End With
Set targetCell = Sheets("Total Deferred Sales").Range("G21")
End Select
For Each sourceCell In sourceRange
If sourceCell.Value = "" Then
targetCell.Resize(1, 2).Value = ""
Else
dValue = Round(sourceCell.Value, 2)
targetCell.Value = Round((dValue - Int(dValue)) * 100, 2)
targetCell.Offset(0, 1).Value = Int(dValue)
End If
Set targetCell = targetCell.Offset(1, 0)
Next sourceCell
Next i
Application.ScreenUpdating = True
End Sub
and Also the net amount as shown in the two sheets ( Total cash sales & Total Deferred Sales )
If you click on the button, you'll see what I mean ... Please see the yellow cells ... Thank you for any help on this in advance.