vba.- Adding Sheets.

montecarlo2012

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows
Hello all.
VBA Code:
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:
1622481516761.png

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

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,655
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows
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 :cry::coffee:, do you,
because also require to change sheet numbers along with J.
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,655
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
    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

Well-known Member
Joined
Jan 26, 2011
Messages
763
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Forum statistics

Threads
1,140,999
Messages
5,703,645
Members
421,307
Latest member
morrden86

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
Top