Random Rooms

Hello

I am stock. Help.

I added a columns for dates and when I click the button it stop working the button for reset.

My original worksheet was in Sheet2 and the vba code was working for it.
I left the original code but I don't know which one that I need to change so that it will work for the random in column for Room which B8:E35 which used to be A8:C35.

Thank you in advance

See attachment.

Sub pick_Random()

Sheets("Random Matrix").Unprotect "RD"    '<----- Change as required

Dim i&, j&, k&, rng, rd(1 To 10000, 1 To 1)
Dim cell As Range
For Each cell In Range("I8:I20") ' change to actual range of random numbers
    If Not IsEmpty(cell) Then
        k = k + 1
        rd(k, 1) = cell.Value
    End If
Next
rng = Range("A8:C35").Value
Randomize
For i = 1 To UBound(rng)
    For j = 1 To 3
        If j <> 2 Then rng(i, j) = rd(Int(Rnd * k) + 1, 1)
    Next
Next
Range("A8:C35").Value = rng

'add this for the allow users of this worksheet to: Format cells and Sort
With Sheets("Random Matrix") 'change it here
    .Protect _
        Password:="RD", _
        DrawingObjects:=True, _
        Contents:=True, _
        Scenarios:=True, _
        AllowFormattingCells:=True, _
        AllowSorting:=True
    .EnableSelection = xlUnlockedCells
End With

Range("B1").Select
End Sub

It should randomize from B8:B35 and E8:E35

Thank you

Using the MACRO RECORDER :

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("B8:B35").Select
    ActiveWindow.SmallScroll Down:=-15
    Range("A7:C35").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Add Key:=Range( _
        "A8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Random Matrix").Sort
        .SetRange Range("A7:C35")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=3
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Add Key:=Range( _
        "A8"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Random Matrix").Sort
        .SetRange Range("A7:C35")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Add Key:=Range( _
        "B9:B35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Random Matrix").Sort
        .SetRange Range("A7:C35")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWindow.SmallScroll Down:=0

Macro2

End Sub

Sub Macro2()
'
' Macro2 Macro
'

'
    Range("D8:F35").Select
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Random Matrix").Sort.SortFields.Add Key:=Range( _
        "E8:E35"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Random Matrix").Sort
        .SetRange Range("D8:F35")
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("A1").Select
End Sub

Hi sorry.... I have to closed this forum.

I have sent a wrong worksheet.

Thank you

Thank you Logit but I have to unsubscribed with this forum.

Hello

I wanted Sheet1 to work like Sheet2.
I created Sheet1 which I added columns for dates. My range for randomized is in B8:B35 and E8:E35. But when I transferred or copied the code from Module1 to Module2 Then I did not work.

Can you please modify the code in Module2. See attachment worksheet.

Thank you

Hi
Ok.. Thank you bebo.

Bebo
Good morning

Can you please modify and place the range K8:K to Sheet3 A1:A?

Thanks

I wanted to type all the room number in Sheet3 A1:A because its easier.

thank you

Great! Thank you so much