We help IT Professionals succeed at work.

Create combinations in renamed sheets for specific groups only

The code makes combinations from data in B10:AG19 in "sheet1" and puts them in sheets "NumbersX" where "X" is 1-10.

When the code is executed again it deletes data in sheets "NumbersX" then writes new data.

The goal here is to edit the code to:


1) Number the sheets using the number in B10:B19. In this instance "Numbers_3, Numbers_4, numbers_7, etc.

2)  write combinations using only the first number (located in B10:B19). After it is finished wit combinations that match the first number stop with that sheet then go to next sheet, etc.

FYI: I have 1) manually edited sheet names to match the desired result and the first sheet shows only combinations with the number 3 that matches the number in B10.

There is no data in the last sheet since there are no numbers in B19:AG19
CreateCominationsFromGoups_2-2020_F.xlsm
Comment
Watch Question

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You didn't upload a workbook.

Author

Commented:

Martin,


File uploaded now.


Thanks

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I don't understand why you are attaching a workbook so similar to one of your previous ones, but in this one you only have sheets for Numbers_3, 4, 7, 8, 10, 11, 17, 18, 2 and one named just "Numbers_". It fails as soon as you try to write to the non-existent named "Numbers_1". What do you want me to do here?
Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try changing the error routine to this:

Err_Q_28233532:

  lngErr_Number = Err.Number
  If Err.Number = -2147352565 Or Err.Number = 9 Then
    ThisWorkbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
    ActiveSheet.Name = "Numbers_" & CStr(lngNumbers)
    Resume Next
  End If
  strErr_Description = Err.Description
  
  On Error Resume Next
  
  Application.ScreenUpdating = True
  
  Beep
  
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _
         ThisWorkbook.Name
  Resume Exit_Q_28233532
  
End Sub

Open in new window

Author

Commented:

Martin,


The goal is to limit the combinations to only those that match the first number in each row at B10:B19 and to name each sheet with that number.


I will attach the original file that works.

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this.
29173071.xlsm

Author

Commented:

Martin,


The code does rename pages but it fails to stop when combinations for the first number are complete. 

See picture

Also, it makes a "sheet12" then when run again "sheet11". If these are used during processing they should be deleted as it may cause undesired operation and overhead. (See bottom of picture).


The idea is to slowly build on the current code so I need it as clean and as little overhead as possible.


Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
I'm sorry that I didn't look far enough down the sheets to see the numbers that don't match the sheet name. I will fix that but fist let me ask you about something else.

The code in the Q_28233532 sub (which is now inappropriately named) always calls the CreateCombinations Sub with 3 parameters. Those 3 parameters are 1) the source of the data, 2) the destination of the sets, and 3) the number of cells in a set. However, in the CreateCombinations Sub there is code that checks to see if those values have been supplied, and if not it prepared to ask for the value(s) via inputbox's. Given how the CreateCombinations is called, those inputbox's aren't necessary and the code associated with them could be eliminated. Do you want me to do that?

Author

Commented:

Yes, they can be eliminated as long as it can be eliminated without affecting the outcome.

 I believe it’s part of the original code that asked for 1) the source of the data, 2) the destination of the sets, and 3) the number of cells in a set. 


it’s been automated   to 1) pick from B10:AG19, 2) make new sheets for each set and 3) 5 cells in each set.

Social distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
See if this is better.
29173071a.xlsm

Author

Commented:

Martin,


Looks like it will work. A bit more testing and I'll know better. I get an error if I use less than 32 numbers in each row, but no worries there. I've corrected that.

The real question is:

since it only makes combinations with one common number, can it handle up 70 numbers ? 

If not 70 then what is the maximum total numbers it can make combinations with?

Martin LissSocial distance - Don't touch your face - Wash your hands for 20 seconds
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
can it handle up 70 numbers ?
Give it a try, The worst that can happen is that it crashes or hangs up.

Author

Commented:

It does not work with the current macro. There must be some sort of limit. I'll open another question and close this one.