revaluation report

[BeatriceP]

Hi guys!

Some of us, at work, are using this macro in order to run a revaluation report.
The working file looks pretty ugly and the macro has been recorded and all apears to be a mess, so I have to re-write this as to be more user friendly.

I would like to know if there's an easy way of doing this besides using a pivot ( check alt+ohu ) to generate the first three sheets.
You can find attached also a new download ( dd.xls ) which can be used when you run the macro.

Thanks a lot in advance!

 

[SirJB7]

Hi, BeatriceP!

As lazier than usual because of the weekend, let us go by parts as Jack said a century ago.
a) Modules 2 thru 5 are empty, so delete them
b) This is the code for Sub ClearData in module Module6:

Option Explicit
 
Sub ClearData()
    Sheets("Total Summary").Range("A2:I100").ClearContents
    Sheets("Creditor Summary").Range("A3:I100, A103:I200").ClearContents 'why B3?
    Sheets("Debtor Summary").Range("A3:I100, A103:I200").ClearContents
    Sheets("Pivot Tables").Cells.ClearContents
    Sheets("Pivot Sheet").Cells.ClearContents
    Sheets("testreval").Activate 'need select?
End Sub

c) Module1... ... Module1!... ... which Module1?... ... Ohhh, that Module1... ... maybe later, I didn't even give a look at it...

Regards!

 

[SirJB7]

Hi, BeatriceP!

I downloaded again your 2nd original file, I pressed Clear Data and I got an error 1004 in one of the lines:
ActiveSheet.Next.Select
when the active worksheet was "Debtor Summary". I think it's because of hidden worksheets.

Would you please upload a working, non-error workbook? Or you besides your requirement of polishing the code as you wrote in your 1st post there are any issues? The idea is to test both buttons, specially the 2nd one and analyze what they do: for that the code should works.

Regards!

 

[BeatriceP]

Hello kind Sir

Thank you for the ClearData code, but that's not really the issue here. You can even ignore that button.
I know everything looks messy because this macro has been recorded step by step, by another person. I am interested in finding out how to re-write the code for Module1 - "Start new Month Calculation", in order to get the data for "Total Summary", "Creditor" and "Debitor" by accounts especially ( see in Creditor sheet, if you scroll down )

Thanks!
Bee

 

[Smallman]

Hi Bee

There is not enough information in your file to give an appropriate answer. Anyone who takes on your project falls at the first hurdle. The macro in question is called Reveldata and it starts by manipulating the testreval sheet. We have to make that assumption because the Macro on the testreval sheet is pointing to the Reveldata macro and it starts off like this;

Rows("1:7").Select

No external reference means it wants to manipulate data on that tab. Problem is that tab is completely empty. I think a fair assumption is that the DD sheet is meant to be pasted on this tab prior but when you get into the macro that theory seems to fall in the water also. There are a couple of steps you have left out. It might be an idea to step out the process before you start the macro RevelData.

Is there any reason you don’t just manipulate the data into the tabular format you are trying to achieve and then have the 3 sheets sitting there waiting with formula for the data to be dropped in. I realise you want to be flexible with the naming of the currencies in your list but I would imagine a simple test of new currency items each month would be the way to go. You could then just add these new currencies to your formula sheet. That way you are not building and rebuilding formula each month. I say Vote 1 a Template!!!

Anyways any more information would be handy as the code would be straight forward to replicate in an efficient manner.

Take care


Smallman

 

[BeatriceP]

Hi there!

The macro starts like this because it first deletes all the unnecessary columns, due to the standard spreadsheet export from SAP.
So I have highlighted a row in "Total summary" tab which contains the formulas ( more or less ) for CHF currency. This should be generated for all the currencies found in testreval download. Also, for the second and third tabs, the same data should be generated but sorted by accounts ( e.g. 440010, 440030, etc )

Do you think you can help me a bit with this?

Thanks a lot!
Bee

 

[SirJB7]

Hi, BeatriceP!

Your last uploaded file doesn't have any code so I continued with your first uploaded one.

This is a better version of the code of module Module1:

Option Explicit

Sub Reveldata()
    With ActiveSheet
        .Rows("1:7").Delete Shift:=xlUp
        .Columns("A:B").Delete Shift:=xlToLeft
        .Columns("J:J").Delete Shift:=xlToLeft
        .Cells.Sort Key1:=Range("F2"), Order1:=xlAscending, Header:=xlYes, _
            OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
    End With
    With Worksheets("Total Summary")
        .Activate
        With Range(.[A2], .[A2].End(xlDown))
            .FormulaR1C1 = "=IF('Pivot Sheet'!R[-1]C="""","""",'Pivot Sheet'!R[-1]C)"
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
        With Range(.[B2], .[B2].End(xlDown))
            .FormulaR1C1 = "=SUMIF(testreval!C[3],'Total Summary'!RC[-1],testreval!C[5])"
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
        With Range(.[C2], .[C2].End(xlDown))
            .FormulaR1C1 = "=IF(RC[-2]="""","""",SUMIF(testreval!C[2],'Total Summary'!RC[-2],testreval!C[5]))"
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
        With Range(.[D2], .[D2].End(xlDown))
            .FormulaR1C1 = "=IF(RC[-3]="""","""",IF(RC[-2]=0,0,RC[-2]/RC[-1]))"
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
        With Range(.[E2], .[E2].End(xlDown))
            .FormulaR1C1 = "=IF(RC[-4]="""","""",RC[-2]+SUMIF(testreval!C,'Total Summary'!RC[-4],testreval!C[9]))"
            .Copy
            .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End With
        Range(.[B2:E2, [B2:E2].End(xlDown)]).NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
    End With
'...
    Range("F2").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-5]="""","""",IF(RC[-4]=0,0,RC[-4]/RC[-1]))"
    Range("F2").Select
    Selection.Copy
    Range("F3:F100").Select
    ActiveSheet.Paste
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Selection.NumberFormat = "#,##0.00_ ;[Red]-#,##0.00 "
'...
'...
'...
End Sub

I've done up to column E, 5 columns, and I both got bored & felt asleep, so I hope that this structure serves you as a model for all the remaining columns. I can't be of further help on this since I'm unable to spend more time translating the code.

Just advise if any issue.

Regards!