TurningFinal
New Member
- Joined
- Nov 29, 2016
- Messages
- 3
Hi All,
I hate to be 'that guy', but I cant seem to work this one out on my own. Please note that I have zero training in Excel or VBA, I've simply taught myself to google quite well.
Problem is as follows.
I have a large scheduling spreadsheet, which is populated via a macro from a SAP dump.
It puts the data into areas based on shift and date of the work to be completed. The code I've written requires that each days and areas worth of work be selected, and then when i run it, it sorts and moves it all to the top of the selection (as part of sorting).
I have gone through the sheet and created a heap of named ranges where the data is initially dumped, and I'm trying to loop through each range and run the same code.
So as you can see the 1st set of commented out code and what is in blue is what im trying to do. The uncommented code works, but requires i make the selections manually.
I think that if i can get each Name looped through, the rest of the code will work. Happy to listen to any ideas you may have regarding my code in general.
I hate to be 'that guy', but I cant seem to work this one out on my own. Please note that I have zero training in Excel or VBA, I've simply taught myself to google quite well.
Problem is as follows.
I have a large scheduling spreadsheet, which is populated via a macro from a SAP dump.
It puts the data into areas based on shift and date of the work to be completed. The code I've written requires that each days and areas worth of work be selected, and then when i run it, it sorts and moves it all to the top of the selection (as part of sorting).
I have gone through the sheet and created a heap of named ranges where the data is initially dumped, and I'm trying to loop through each range and run the same code.
So as you can see the 1st set of commented out code and what is in blue is what im trying to do. The uncommented code works, but requires i make the selections manually.
I think that if i can get each Name looped through, the rest of the code will work. Happy to listen to any ideas you may have regarding my code in general.
Code:
Option Compare Text
'mapped to mouse 3 via Autohotkey on ctrl-shift-Q
Sub SortByFLOC()
Dim i As Integer
[COLOR=#0000ff]'Sort by functional location description.[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]' Dim N As Name[/COLOR]
[COLOR=#0000ff]' For Each N In ActiveWorkbook.Names[/COLOR]
[COLOR=#0000ff]'[/COLOR]
[COLOR=#0000ff]' N.SortFields.Add Key:=Selection.columns(6), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]' N.SortFields.Add Key:=Selection.columns(8), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]' N.SortFields.Add Key:=Selection.columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff]' N.SetRange N[/COLOR]
[COLOR=#0000ff]' N.Apply[/COLOR]
[COLOR=#0000ff]' Next N[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff] [/COLOR]
[COLOR=#0000ff] With ActiveSheet.Sort[/COLOR]
[COLOR=#0000ff] .SortFields.Clear[/COLOR]
[COLOR=#0000ff] .SortFields.Add Key:=Selection.columns(6), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff] .SortFields.Add Key:=Selection.columns(8), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff] .SortFields.Add Key:=Selection.columns(2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers[/COLOR]
[COLOR=#0000ff] .SetRange Selection[/COLOR]
[COLOR=#0000ff] .Apply[/COLOR]
[COLOR=#0000ff] End With[/COLOR]
' Set font to arial, size to 12 in case of any manually entered lines
With Selection.Font
.Name = "Arial"
.Size = 12
End With
'If a cell is highlighted RGBorange AND bolded, it will retain its bolding, but if it is bold without color, it will be de-bolded
For Each c In Selection
If c.Value Like "*switch*" Then GoTo BypassFormat
If c.Value Like "*hr*" Then
c.Font.bold = True
c.Interior.Color = rgbOrange
BypassFormat:
End If
If c.Font.bold = True And c.Interior.Color = rgbOrange And c.Value = "" Then
c.Interior.Color = xlNone
c.Font.bold = False
End If
If c.Font.bold = True And c.Interior.Color <> rgbOrange Then
c.Font.bold = False
End If
Next
'horizontal align the hours and man allocations
'change for i = 6 to 9 digits to reflect columns to be centered
For i = 6 To 9
Selection.columns(i).HorizontalAlignment = xlCenter
Next i
For i = 1 To 1
Selection.columns(i).HorizontalAlignment = xlCenter
Next i
'set the 1st column to the grey already in the schedule
For col = 1 To 1
Selection.columns(col).Interior.Color = RGB(242, 242, 242)
Next col
'set the right hand border of the 1st column to the same as the schedule already has
With Selection
Set Rng = Selection.columns(1)
End With
With Rng.Borders(2)
.LineStyle = 1
.ColorIndex = 0
.TintAndShade = 0
.Weight = 2
End With
End Sub