vba.- Adding Sheets.

[montecarlo2012]

Hello all.

Sub S9_8()
    Dim SrcWS As Worksheet, DestWS As Worksheet
    Dim rngData As Range, cell As Range, M, N
    Dim rngDest As Range, i As Long
    Set SrcWS = Sheet1
':::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    Set DestWS = Sheet9        '::::::LOOP REQUIRE
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
    Set rngDest = DestWS.Range("C2")
    For i = 0 To 5
        Set rngData = SrcWS.Range(SrcWS.Cells(2, 2 + i), SrcWS.Cells(SrcWS.Rows.Count, 2 + i).End(xlUp))
        M = -1
        For Each cell In rngData
 '::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
            If cell = 8 Then    ':::::::::::::::::LOOP REQUIRE
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
                rngDest.Offset(0, M) = N
                N = 0
                M = M + 1
            Else
                N = N + 1
            End If
        Next cell
        Set rngDest = rngDest.Offset(16)
    Next i
Dim V, Rg As Range
    With Application
        For Each V In Split("B2 B18 B34 B50 B66 B82")
            Set Rg = Range(V, Range(V).End(xlToRight))
            Range(V)(3).Resize(4).Value2 = .Transpose(Array(.Average(Rg), .Count(Rg), .Max(Rg), .Mode(Rg)))
        Next
    End With
            Set Rg = Nothing
'::::::::::::::::::::::::::::::::::::::::::B2::::::::::::::::::::::::::::::::::::::::::::::::::::::
Range("B8").Formula = "=COUNTIF(B2:XX2,B7)" 'QTY MODE
Range("B9").Formula = "=COUNTIF(B2:XX2,B2)"   'QTY LAST
':::::::::::::::::::::::::::::::::::::::::: C 18:::::::::::::::::::::::::::::::::::::::::::::::::::::::
Range("B24").Formula = "=COUNTIF(B18:XX18,B17)" 'QTY MODE
Range("B25").Formula = "=COUNTIF(B18:XX18,B18)"   'QTY LAST
':::::::::::::::::::::::::::::::::::::::::::D34::::::::::::::::::::::::::::::::::::::::::::::::::::::
Range("B40").Formula = "=COUNTIF(B34:XX34,B33)" 'QTY MODE
Range("B41").Formula = "=COUNTIF(B34:XX34,B34)"   'QTY LAST
'.::::::::::::::::::::::::::::::::::::::::E50::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Range("B56").Formula = "=COUNTIF(B50:XX50,B49)" 'QTY MODE
Range("B57").Formula = "=COUNTIF(B50:XX50,B50)"   'QTY LAST
'::::::::::::::::::::::::::::::::::::::::::F66::::::::::::::::::::::::::::::::::::::::::::::::::::::
Range("B72").Formula = "=COUNTIF(B66:XX66,B65)" 'QTY MODE
Range("B73").Formula = "=COUNTIF(B66:XX66,B66)"   'QTY LAST
':::::::::::::::::::::::::::::::::::::::::::G82:::::::::::::::::::::::::::::::::::::::::::::::::::
Range("B88").Formula = "=COUNTIF(B82:XX82,B81)" 'QTY MODE
Range("B89").Formula = "=COUNTIF(B82:XX82,B82)"   'QTY LAST
'::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Sheet1.Range("L9").Value = Sheet9.Range("B2").Value 'LAST GAME
Sheet1.Range("N9").Value = Sheet9.Range("B7").Value  'MODE
Sheet1.Range("O9").Value = Sheet9.Range("B24").Value  'PRINT QTY  MODE
Sheet1.Range("k9").Value = Sheet9.Range("B25").Value  'PRINT QTY LAST
End Sub

There are two comments in this code that said 'LOOP REQUIRE'
because, what I am doing now is adding a sheet and inserted a new module
and copy and paste again and again the same code.
in the sub ►"Sub S9_8()◄ meaning sheet9 report number 8 etc. etc.

every time that the ►> If cell = 8 Then ◄ change the number I change the sheet, like this:

So the question is, how to loop or avoid this
I have to open 53 sheets in order to see the report on each number
how to do this one time instead of 53 times.

your feedback is important
thank you for reading this.

 

[montecarlo2012]

I really try to study VBA, but everything is in my own, and here, I hope one day be like you, until now I am trying to develop some kind of step that help me to resolve my data mining problems.
like first using a big board, determine the variable, and to understand how much the flowchart let me understand also, just in case you want to answer, do you use any algorithm to resolve problems.
Sorry, I when to far.
ok thank you for everything.

 

[Alex Blakenburg]

You are doing a lot of the right things. Writing a code plan in plain english as comments helps to get you started. Oz Du Soleil recently did a LinkedIn post on starting with a simple data set and I think you are doing that too.

You might find Paul Kelly's Youtube video on finding errors interesting
( 9 mins )

 

[montecarlo2012]

Thanks, before you go, do you think is possible for you to look the second part of this case, sorry there are a second episode , do you,
because also require to change sheet numbers along with J.

 

[Alex Blakenburg]

What do you mean ?
Assuming you mean you want j in the sheet name then you need to work out what you want to happen if the sheet already exists.
Also since you already have a sheet 3 and possibly 1 and 2, how do you want to name the sheets (j = 1,2,3 are already taken).
Given that I am in a totally different time zone and will probably log out soon it might be worth doing it as a separate post.

Naming the sheet is just this and the code below indicates the positioning
DestWS.Name = "Output " & j 'Rename new Sheet based on j

But just as is it will error out if the sheet already exists. So it needs if sheet exists code added and you need to work out what action you want to take if it already exists.
Just deleting is an option but may cause you some grief.

    For j = 1 To 3
        Worksheets.Add After:=Worksheets(Worksheets.Count)
        Set DestWS = ActiveSheet        'location of the results to write
        DestWS.Name = "Output " & j     'Rename new Sheet based on j
        Set rngDest = DestWS.Range("C2

 

[montecarlo2012]

I am really sorry, is true you are in different time zone, my apology.

Maybe better new thread, or maybe next Saturday, I just think that you
already Know what exactly I am doing.

it is fine, Thank you Alex
your are great person, and very intelligent.