Construct a report from changes/addition/deletion of datas

[PRADEEPB270]

I have a file having 23 sheets.Here,I have taken an example with 3 sheets working.Namely,AL,ST and FA.

Datas have downloaded from system and paste on the relevant sheets i.e. AL,ST AND FA.

Now,I want the VBA codes working or perfect macro who solve my following constraints:-

1-Sheet name change and Insert from AL,ST and FA to AL01,ST01 and FA01 ( Refer attach file with GreenTab Colour Sheet name).
2-All rows should be delete having Amount < 0 in column G of sheets AL01 etc.
3-Now,please focus on the oneTab colour sheet example i.e.AL01:-
a-'Material' and 'Material Description' from Col.B of sheet 'AL'.
b-'Book Qty' is valuated stock ,Col.C from sheet 'AL'.
c-'Rate'is the formula i.e.Col.G/Col.D
d-'Phy.Qty'-Automatic Insert a column after col.'E' on each sheet with column width 12.00 (89 pixels ).
e-'Amount'-from 'value of valuated stock' ( Col.E) of sheet 'AL'
f-Automatic ascending order all the sheets having prefix '01' e.g.AL01,FA01 AND ST01 ETC.

Can the above workings be possible through VBA codes?

Please refer the attach file for more clarifications.

[Rudi]

Try this...

[PRADEEPB270]

Super result.You are the GOD of the excel .Very....Glad to find the fantastic result.Many-2 thanks to you for your awesome efforts.

Regards

[Rudi]

Super result.You are the GOD of the excel .Very....Glad to find the fantastic result.Many-2 thanks to you for your awesome efforts.

Regards

Geez... I am SO far from that accolade!!!
BTW: I notice I misread the last instruction about sorting. I sorted the data instead of the sheets...
Maybe I'll let the "real" gods of Excel sort that one out (pardon the pun!)

[HansV]

To sort the sheets, add this procedure to the module:

Code: Select all

Sub Sort01Sheets()
    Dim i As Long
    Dim j As Long
    Dim n As Long
    n = Worksheets.Count
    For i = 1 To n - 1
        If Right(Worksheets(i).Name, 2) = "01" Then
            For j = i + 1 To n
                If Right(Worksheets(j).Name, 2) = "01" Then
                    If Worksheets(i).Name > Worksheets(j).Name Then
                        Worksheets(j).Move Before:=Worksheets(i)
                    End If
                End If
            Next j
        End If
    Next i
End Sub

and call it at the end of rudi's CreateReports macro:

Code: Select all

    ...
    Next sh
    ' *** New ***
    Sort01Sheets
    ' *** New ***
    Sheets(1).Select
    Application.ScreenUpdating = True