How do you do the below? I.e. Click View than click Macros, Record Macros, etc.

Question

How do you do the below?

I.e. Click View than click Macros, Record Macros, etc. I'm stuck from there.

Record a macro named RoomSort. The macro should copy the range A4:K46 from the Sequential worksheet and paste the data in cell A4 of the Room worksheet. Do not stop the recording after this step.

With the macro still recording click cell A4. On the DATA tab, in the Sort & Filter group, click Sort. Using the Sort dialog box, sort the data by Room in alphabetical order. Add a second level sort that sorts the Days column using the following custom sort order: MWF, MW, M, W, TR, T, R, S. Add a third level sort that sorts the Start Time column from earliest to latest. Return to the Sequential worksheet, click cell A1 and stop the recording. Save the workbook as a Macro-Enabled Template.

Record a macro named RoomSort. The macro should copy the range A4:K46 from the Sequential worksheet and paste the data in cell A4 of the Room worksheet. Do not stop the recording after this step.

With the macro still recording click cell A4. On the DATA tab, in the Sort & Filter group, click Sort. Using the Sort dialog box, sort the data by Room in alphabetical order. Add a second level sort that sorts the Days column using the following custom sort order: MWF, MW, M, W, TR, T, R, S. Add a third level sort that sorts the Start Time column from earliest to latest. Return to the Sequential worksheet, click cell A1 and stop the recording. Save the workbook as a Macro-Enabled Template.

Explanation / Answer

Open a Excel and create Sequential and Room work sheets. In Sequential work sheet add data starting from A4 column. The below code will work considerting first column is Room, second column is Days, third column is Start time and etc till Column K.

Click on Developer Menu - > Macros ->

Enter Macro name as Macro1 and click on create button

Delete the code available there and paste the below code and save the file as Macro -enabled Template.

Run the Macro to check the output.

------------------------------

Sub Macro1()

'
' Macro1 Macro
'

'
Range("A4:K46").Select
Selection.Copy
Sheets("Room").Select
Range("A4").Select
ActiveSheet.Paste
Range("A4").Select
Application.CutCopyMode = False
Application.AddCustomList ListArray:=Array("MWF", "MW", "M", "W", "TR", "T", "R", _
"S")
ActiveWorkbook.Worksheets("Room").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Room").Sort.SortFields.Add Key:=Range("A5:A46"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Room").Sort.SortFields.Add Key:=Range("B5:B46"), _
SortOn:=xlSortOnValues, Order:=xlAscending, CustomOrder:= _
"MWF,MW,M,W,TR,T,R,S", DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Room").Sort.SortFields.Add Key:=Range("C5:C46"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Room").Sort
.SetRange Range("A4:K46")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Sequential").Select
Range("A1").Select
  
End Sub

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.