Re-arranging structure data and recalculate formulas by add new sheet

O

Omar M

Board Regular
Joined
Jan 11, 2024
Messages
66
Office Version
  1. 2019
Platform
  1. Windows
Hello,
I would arrange data again by add new sheet "Arra"
this is the original data
KshfHsab1.xls
ABCDEFGH
1
2
3
4
5ACCOUNT LIST
6
7BALANCE: CREDIT (85.00) TO:FATTUH
8
9DATEVOUCHER NOINVOICE NODESCRIBEBALANCECREDITDEBIT
102023.12.31 BALANCE DAY 2023/12/31 -41430.0000.0041,430.00
112024.01.09489RECEIVED VOUCHER-23430.00018,000.000.00
122024.01.20145PURCHASES-20180.0003,250.000.00
132024.01.30548RECEIVED VOUCHER5.00020,185.000.00
142024.04.24817SALES-26995.0000.0027,000.00
152024.04.24771RECEIVED VOUCHER5.00027,000.000.00
162024.04.29846SALES-23595.0000.0023,600.00
172024.06.02937RECEIVED VOUCHER1405.00025,000.000.00
182024.06.02996SALES-57995.0000.0059,400.00
192024.06.03939RECEIVED VOUCHER-32995.00025,000.000.00
202024.07.061008RECEIVED VOUCHER0.00032,995.000.00
212024.07.131107SALES-27000.0000.0027,000.00
222024.07.131031RECEIVED VOUCHER0.00027,000.000.00
232024.08.11970000078SALES-4150.0000.004,150.00
242024.08.15970000117SALES-8300.0000.004,150.00
252024.08.17970000121SALES-29900.0000.0021,600.00
262024.08.17910000216RECEIVED VOUCHER-29550.000350.000.00
272024.08.29910000217RECEIVED VOUCHER-9550.00020,000.000.00
282024.09.05970000273SALES-13850.0000.004,300.00
292024.09.23910000335RECEIVED VOUCHER85.00013,935.000.00
30TOTAL85.000212,715.00212,630.00
31
ACCA
Cell Formulas
RangeFormula
E12E12=E22-20180




the result
KshfHsab1.xls
ABCDEFGH
1
2
3
4
5ACCOUNT LIST
6
7BALANCE: CREDIT (85.00)
8
9DATENAMEDESCRIBEINVOICE NOVOUCHER NODEBITCREDITBALANCE
102023.12.31FATTUH BALANCE DAY 2023/12/31 41,430.000.0041430.000
112024.01.09FATTUHRECEIVED VOUCHER4890.0018,000.0023430.000
122024.01.20FATTUHPURCHASES1450.003,250.0020180.000
132024.01.30FATTUHRECEIVED VOUCHER5480.0020,185.00-5.000
142024.04.24FATTUHSALES81727,000.000.0026995.000
152024.04.24FATTUHRECEIVED VOUCHER7710.0027,000.00-5.000
162024.04.29FATTUHSALES84623,600.000.0023595.000
172024.06.02FATTUHRECEIVED VOUCHER9370.0025,000.00-1405.000
182024.06.02FATTUHSALES99659,400.000.0057995.000
192024.06.03FATTUHRECEIVED VOUCHER9390.0025,000.0032995.000
202024.07.06FATTUHRECEIVED VOUCHER10080.0032,995.000.000
212024.07.13FATTUHSALES110727,000.000.0027000.000
222024.07.13FATTUHRECEIVED VOUCHER10310.0027,000.000.000
232024.08.11FATTUHSALES9700000784,150.000.004150.000
242024.08.15FATTUHSALES9700001174,150.000.008300.000
252024.08.17FATTUHSALES97000012121,600.000.0029900.000
262024.08.17FATTUHRECEIVED VOUCHER9100002160.00350.0029550.000
272024.08.29FATTUHRECEIVED VOUCHER9100002170.0020,000.009550.000
282024.09.05FATTUHSALES9700002734,300.000.0013850.000
292024.09.23FATTUHRECEIVED VOUCHER9100003350.0013,935.00-85.000
30TOTAL212,630.00212,715.00-85.000
Arra
Cell Formulas
RangeFormula
H10,H30H10=F10-G10
H11:H29H11=H10+F11-G11
F30:G30F30=SUM(F10:F29)

without forgetting auto fill name in column B based on D7 and recalculate formulas in column BALANCE and TOTAL row , but I don't need it as in ACCA sheet..every time when run the macro should add new sheet with date today like this Arra_14-11-2024.
thanks
 
Sort by date Sort by votes
Omar M said:
Thanks Fuji,
and I hope fixing about Arra sheet. there is no already existed Arra sheet ,macro should add it as I said in OP.

and every time run the macro for same day ,then should replace data for added sheet today.
Click to expand...
Insert one line
Rich (BB code): 
    Dim myName$, Bal#
    If [not(isref(arra!a1))] Then Sheets.Add.Name = "Arra"  '<--- this line
    With Sheets("arra")
 
Upvote 0

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Omar M said:
the result
Click to expand...
@Omar M, I gave you the answer! According to your 2nd picture, in C7 BALANCE: CREDIT (85.00) is located. What or who is stopping you from changing this in my code to the cell you need? .Cells(7, 3).Value where 7 is the 7th row and 3 is column C, that's where you make the changes. I wrote the code according to the task.
 
Upvote 0
Fuji said:
Insert one line
Rich (BB code): 
    Dim myName$, Bal#
    If [not(isref(arra!a1))] Then Sheets.Add.Name = "Arra"  '<--- this line
    With Sheets("arra")
Click to expand...
ok when add new sheets should be based on date today like Arra_17-11-2024 and when run every time for the same day then will replace data , not add new sheet again for the same day, but when there is new date today then will add new sheet for instance is 18-11-2024 then will add like this Arra_18-11-2024
also when show debit in B7 then shouldn't show brackets , just show brackets when show credit word.
last thing I try show number in b7 like this #,##0.00 but doesn't work for me!
Rich (BB code): 
.[b7] = "BALANCE: " & Format(IIf(Bal < 0, "CREDIT", IIf(Bal > 0, "DEBIT", "")), "#,##0.00") & "(" & Abs(Bal) & ")"
 
Upvote 0
According to your 2nd picture, in C7 BALANCE: CREDIT (85.00) is located.
Click to expand...
ok sorry about it !
What or who is stopping you from changing this in my code to the cell you need? .Cells(7, 3).Value where 7 is the 7th row and 3 is column C, that's where you make the changes
Click to expand...
actually I don't notice you changed from B7 to C7 and this is not what I ask for it !
just see the picture the data doesn't show formatting and borders this is not what I ask for it first of all :rolleyes: and should autofit.
KshfHsab1.xls
ABCDEFGH
1
2
3
4
5ACCOUNT LIST
6
7BALANCE: DEBIT 48485.00
8
9DATENAMEDESCRIBEINVOICE NOVOUCHER NODEBITCREDITBALANCE
102023.12.31FATTUH BALANCE DAY 2023/12/31 90000.0000.00090000.000
112024.01.09FATTUHRECEIVED VOUCHER4890.00018000.00072000.000
122024.01.20FATTUHPURCHASES1450.0003250.00068750.000
132024.01.30FATTUHRECEIVED VOUCHER5480.00020185.00048565.000
142024.04.24FATTUHSALES81727000.0000.00075565.000
152024.04.24FATTUHRECEIVED VOUCHER7710.00027000.00048565.000
162024.04.29FATTUHSALES84623600.0000.00072165.000
172024.06.02FATTUHRECEIVED VOUCHER9370.00025000.00047165.000
182024.06.02FATTUHSALES99659400.0000.000106565.000
192024.06.03FATTUHRECEIVED VOUCHER9390.00025000.00081565.000
202024.07.06FATTUHRECEIVED VOUCHER10080.00032995.00048570.000
212024.07.13FATTUHSALES110727000.0000.00075570.000
222024.07.13FATTUHRECEIVED VOUCHER10310.00027000.00048570.000
232024.08.11FATTUHSALES9700000784150.0000.00052720.000
242024.08.15FATTUHSALES9700001174150.0000.00056870.000
252024.08.17FATTUHSALES97000012121600.0000.00078470.000
262024.08.17FATTUHRECEIVED VOUCHER9100002160.000350.00078120.000
272024.08.29FATTUHRECEIVED VOUCHER9100002170.00020000.00058120.000
282024.09.05FATTUHSALES9700002734300.0000.00062420.000
292024.09.23FATTUHRECEIVED VOUCHER9100003350.00013935.00048485.000
30TOTAL261200.000212715.00048485.000
Arra 17-11-2024
Cell Formulas
RangeFormula
H10,H30H10=F10-G10
H11:H29H11=H10+F11-G11
F30:G30F30=SUM(F10:F29)
 
Upvote 0
VBA Code: 
Option Explicit

Sub Re_Arranging_v2()
    Dim i           As Long
    Dim sName       As String: sName = "Arra " & Format(Date, "dd-mm-yyyy")
    Dim balanceVal  As Double

    If Evaluate("ISREF('" & sName & "'!A1)") = False Then _
            Sheets.Add(, Sheets(Sheets.Count)).Name = sName

    With ThisWorkbook.Worksheets("ACCA")
        Dim lastRow As Long: lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        Dim outtxt  As String: outtxt = Trim(Replace(.Cells(7, 4).Text, "TO:", ""))
        Dim dataArr As Variant: dataArr = .Range("A9:G" & lastRow).Value

        Dim destArr As Variant
        ReDim destArr(1 To UBound(dataArr), 1 To 8)

        For i = 1 To UBound(dataArr)
            Dim destRow As Long: destRow = i + 8

            destArr(i, 1) = dataArr(i, 1)
            destArr(i, 2) = outtxt
            destArr(i, 5) = dataArr(i, 2)
            destArr(i, 4) = dataArr(i, 3)
            destArr(i, 3) = dataArr(i, 4)
            destArr(i, 8) = dataArr(i, 5)
            destArr(i, 7) = dataArr(i, 6)
            destArr(i, 6) = dataArr(i, 7)
        Next i

    End With

    With ThisWorkbook.Worksheets(sName)
        .Cells(9, 1).Resize(UBound(destArr), 8).Value = destArr
        .Cells(9, 1).Resize(UBound(destArr), 8).Borders.LineStyle = xlContinuous
        .Cells(9, 1).Resize(UBound(destArr), 8).HorizontalAlignment = xlCenter
        .Cells(9, 1).Resize(, 8).Interior.Color = RGB(0, 176, 240)

        .Cells(5, 2).Value = "ACCOUNT LIST"
        .Cells(9, 2).Value = "NAME"
        .Cells(30, 5).Value = "TOTAL"
        .Cells(10, 8).Formula = "=F10-G10"
        .Cells(30, 8).Formula = "=F30-G30"

        With .Range("H11:H29")
            .Formula = "=H10+F11-G11"
        End With

        With .Range("F30:G30")
            .Formula = "=SUM(F10:F29)"
            .Borders.LineStyle = xlContinuous
            .Interior.Color = RGB(0, 176, 240)
        End With

        With .Range("E30:H30")
            .Borders.LineStyle = xlContinuous
            .Interior.Color = RGB(0, 176, 240)
        End With

        With .Range("F10:H30")
            .NumberFormat = "0.000"
        End With

        .Range("H30").Interior.Color = RGB(189, 215, 238)
        balanceVal = .Cells(30, 8).Value

        If balanceVal >= 0 Then
            .Cells(7, 2).Value = "BALANCE: DEBIT " & Format(balanceVal, "0.00")
        Else
            .Cells(7, 2).Value = "BALANCE: CREDIT (" & Format(Abs(balanceVal), "0.00") & ")"
        End If

        .Cells.EntireColumn.AutoFit
    End With

    Application.ScreenUpdating = True
End Sub
Re-arranging structure data and recalculate formulas by add new sheet.png
 
Upvote 0
yes based on your picture this is what I look for it about formatting, but strangely this is What I got !
KshfHsab1.xls
ABCDEFGH
1
2
3
4
5ACCOUNT LISTACCOUNT LIST
6
7BALANCE: DEBIT 48485.00BALANCE: DEBIT 48485.00
8
9DATENAMEDESCRIBEINVOICE NOVOUCHER NODEBITCREDITBALANCE
102023.12.31FATTUH BALANCE DAY 2023/12/31 90000.0000.00090000.000
112024.01.09FATTUHRECEIVED VOUCHER4890.00018000.00072000.000
122024.01.20FATTUHPURCHASES1450.0003250.00068750.000
132024.01.30FATTUHRECEIVED VOUCHER5480.00020185.00048565.000
142024.04.24FATTUHSALES81727000.0000.00075565.000
152024.04.24FATTUHRECEIVED VOUCHER7710.00027000.00048565.000
162024.04.29FATTUHSALES84623600.0000.00072165.000
172024.06.02FATTUHRECEIVED VOUCHER9370.00025000.00047165.000
182024.06.02FATTUHSALES99659400.0000.000106565.000
192024.06.03FATTUHRECEIVED VOUCHER9390.00025000.00081565.000
202024.07.06FATTUHRECEIVED VOUCHER10080.00032995.00048570.000
212024.07.13FATTUHSALES110727000.0000.00075570.000
222024.07.13FATTUHRECEIVED VOUCHER10310.00027000.00048570.000
232024.08.11FATTUHSALES9700000784150.0000.00052720.000
242024.08.15FATTUHSALES9700001174150.0000.00056870.000
252024.08.17FATTUHSALES97000012121600.0000.00078470.000
262024.08.17FATTUHRECEIVED VOUCHER9100002160.000350.00078120.000
272024.08.29FATTUHRECEIVED VOUCHER9100002170.00020000.00058120.000
282024.09.05FATTUHSALES9700002734300.0000.00062420.000
292024.09.23FATTUHRECEIVED VOUCHER9100003350.00013935.00048485.000
30TOTAL261200.000212715.00048485.000
Arra 17-11-2024
Cell Formulas
RangeFormula
H10,H30H10=F10-G10
H11:H29H11=H10+F11-G11
F30:G30F30=SUM(F10:F29)
 
Upvote 0
@Omar M, Look for the Problem on your side, on my screen is the desired result for you. So, look for what you are doing wrong. Judging by your old topics, you always have something wrong. I will not participate in this topic anymore. Good luck.
P.S. And by the way, the code provided by @Fuji works correctly for me with the desired result.
 
Upvote 0
Rich (BB code): 
Sub test()
    Dim myName$, Bal#, s$
    s = "Arra_" & Format$(Date, "dd-mm-yyyy")
    If Evaluate("not(isref('" & s & "'!a1))") Then Sheets.Add.Name = s
    With Sheets(s)
        Sheets("acca").Cells.Copy .[a1]
        .Columns("g").Copy .[h1]: myName = Split(.[d7], ":")(1): .[d7] = ""
        .[b5:b7].Cut .[c5]   '<--- this line
        .Range("a" & Rows.Count).End(xlUp)(2).EntireRow.Clear
        With .[a9].CurrentRegion
            .Value = Application.Index(.Value, Evaluate("row(1:" & .Rows.Count & ")"), [{1,4,4,3,2,7,6,5}])
            .HorizontalAlignment = xlCenter: .Range("b1") = "NAME": .Borders.Weight = 2
            .Columns("b").Offset(1).Resize(.Rows.Count - 1) = myName
            .Columns("f").Value = .Parent.Evaluate(Replace("if((#<>"""")*(isnumber(#)),abs(#),if(#<>"""",#,""""))", "#", .Columns("f").Address))
            .Range("h2").FormulaR1C1 = "=rc[-2]-rc[-1]"
            .Range("h3").Resize(.Rows.Count - 2).FormulaR1C1 = "=r[-1]c+rc[-2]-rc[-1]"
            With .Rows(.Rows.Count + 1)
                With .Range("e1")
                    .Value = "TOTAL": .Font.Bold = True
                    .Resize(, 3).Interior.Color = 15123099
                    .Resize(, 3).Borders.Weight = 2
                End With
                .Range("h1").Interior.Color = 16774348
                .Range("f1:h1").FormulaR1C1 = _
                Array("=sum(r10c:r[-1]c)", "=sum(r10c:r[-1]c)", "=rc[-2]-rc[-1]")
                Bal = .Range("h1")
            End With
            .ColumnWidth = 50: .EntireColumn.AutoFit: .Rows.AutoFit
        End With
        .[c7] = "BALANCE: " & IIf(Bal < 0, "CREDIT", IIf(Bal > 0, "DEBIT", "")) & "(" & Abs(Bal) & ")"  '<--- this line
    End With
End Sub
 
Upvote 0
Solution
thanks again Fuji.

may you read this please?
Omar M said:
also when show debit in B7 then shouldn't show brackets , just show brackets when show credit word.
last thing I try show number in b7 like this #,##0.00 but doesn't work for me!
Rich (BB code): 
.[b7] = "BALANCE: " & Format(IIf(Bal < 0, "CREDIT", IIf(Bal > 0, "DEBIT", "")), "#,##0.00") & "(" & Abs(Bal) & ")"
Click to expand...
 
Upvote 0
Code: 
        s = Format$(ABS(Bal), "#,#.00")
        .[b7] = "BALANCE: " & IIf(Bal > 0, "DEBIT " & s, IIf(Bal < 0, "CREDIT (" & s & ")", 0))
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
sorting duplicates Customers names by add two formulas in last column
Replies
3
Views
374
Kerryx
K
A
  • Solved
Compare two sheets based on lastrow with highlighted cells
2 3
Replies
26
Views
341
mumps
mumps
O
  • Solved
Find and match header in different location for the column based on sheet name
Replies
8
Views
140
Omar M
O
A
  • Question Question
Populate data based on matching last part with sheet name across sheets
Replies
2
Views
111
Ali M
A
O
  • Solved
Put amount in DEBIT,CREDIT columns based on matching part of item
Replies
9
Views
207
Omran Y
O

Forum statistics

Threads
1,224,823
Messages
6,181,179
Members
453,021
Latest member
Justyna P

Share this page

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