monkeyharris
Active Member
- Joined
- Jan 20, 2008
- Messages
- 346
- Office Version
- 365
- Platform
- Windows
I have the below Macro but have just noticed a MASSIVE error where it will only work in the sheet called ExcelReport_08_10_201. Is there any way i can change this so it runs in the sheet i'm in at the time of running the Macro. Below is my Macro.............
Sub Users()
'
' Users Macro
'
'
Columns("M:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("M2").Select
ActiveCell.FormulaR1C1 = "21"
Range("M3").Select
ActiveCell.FormulaR1C1 = "22"
Range("M4").Select
ActiveCell.FormulaR1C1 = "16"
Range("M5").Select
ActiveCell.FormulaR1C1 = "20"
Range("M6").Select
ActiveCell.FormulaR1C1 = "7"
Range("M7").Select
ActiveCell.FormulaR1C1 = "11"
Range("M8").Select
ActiveCell.FormulaR1C1 = "19"
Range("M9").Select
ActiveCell.FormulaR1C1 = "12"
Range("M10").Select
ActiveCell.FormulaR1C1 = "9"
Range("M11").Select
ActiveCell.FormulaR1C1 = "17"
Range("M12").Select
ActiveCell.FormulaR1C1 = "18"
Range("M13").Select
ActiveCell.FormulaR1C1 = "6"
Range("M14").Select
ActiveCell.FormulaR1C1 = "10"
Range("M15").Select
ActiveCell.FormulaR1C1 = "8"
Range("M16").Select
ActiveCell.FormulaR1C1 = "14"
Range("M17").Select
ActiveCell.FormulaR1C1 = "13"
Range("N17").Select
ActiveCell.FormulaR1C1 = "Wendy"
Range("N16").Select
ActiveCell.FormulaR1C1 = "ViviP"
Range("N15").Select
ActiveCell.FormulaR1C1 = "Sharon"
Range("N14").Select
ActiveCell.FormulaR1C1 = "Robin"
Range("N13").Select
ActiveCell.FormulaR1C1 = "Rosie"
Range("N12").Select
ActiveCell.FormulaR1C1 = "Paul"
Range("N11").Select
ActiveCell.FormulaR1C1 = "Natalie"
Range("N10").Select
ActiveCell.FormulaR1C1 = "Martin"
Range("N9").Select
ActiveCell.FormulaR1C1 = "Marcia"
Range("N8").Select
ActiveCell.FormulaR1C1 = "Mark"
Range("N7").Select
ActiveCell.FormulaR1C1 = "Laura"
Range("N6").Select
ActiveCell.FormulaR1C1 = "Lee"
Range("N5").Select
ActiveCell.FormulaR1C1 = "Jade"
Range("N4").Select
ActiveCell.FormulaR1C1 = "Graham"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Impatex"
Range("N2").Select
ActiveCell.FormulaR1C1 = "James"
Range("N3").Select
Columns("N:N").ColumnWidth = 10
Range("M2:N17").Select
ActiveWorkbook.Worksheets("ExcelReport_08_10_2014").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ExcelReport_08_10_2014").Sort.SortFields.Add Key:= _
Range("M2:M17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ExcelReport_08_10_2014").Sort
.SetRange Range("M2:N17")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll ToRight:=5
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("O2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],R2C13:R17C14,2)"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O661")
Range("O2:O661").Select
Range("O1").Select
ActiveCell.FormulaR1C1 = "User"
Columns("O:O").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("P:P,M:M,N:N").Select
Range("N1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("M4").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("A1").Select
End Sub
Sub Users()
'
' Users Macro
'
'
Columns("M:N").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("M2").Select
ActiveCell.FormulaR1C1 = "21"
Range("M3").Select
ActiveCell.FormulaR1C1 = "22"
Range("M4").Select
ActiveCell.FormulaR1C1 = "16"
Range("M5").Select
ActiveCell.FormulaR1C1 = "20"
Range("M6").Select
ActiveCell.FormulaR1C1 = "7"
Range("M7").Select
ActiveCell.FormulaR1C1 = "11"
Range("M8").Select
ActiveCell.FormulaR1C1 = "19"
Range("M9").Select
ActiveCell.FormulaR1C1 = "12"
Range("M10").Select
ActiveCell.FormulaR1C1 = "9"
Range("M11").Select
ActiveCell.FormulaR1C1 = "17"
Range("M12").Select
ActiveCell.FormulaR1C1 = "18"
Range("M13").Select
ActiveCell.FormulaR1C1 = "6"
Range("M14").Select
ActiveCell.FormulaR1C1 = "10"
Range("M15").Select
ActiveCell.FormulaR1C1 = "8"
Range("M16").Select
ActiveCell.FormulaR1C1 = "14"
Range("M17").Select
ActiveCell.FormulaR1C1 = "13"
Range("N17").Select
ActiveCell.FormulaR1C1 = "Wendy"
Range("N16").Select
ActiveCell.FormulaR1C1 = "ViviP"
Range("N15").Select
ActiveCell.FormulaR1C1 = "Sharon"
Range("N14").Select
ActiveCell.FormulaR1C1 = "Robin"
Range("N13").Select
ActiveCell.FormulaR1C1 = "Rosie"
Range("N12").Select
ActiveCell.FormulaR1C1 = "Paul"
Range("N11").Select
ActiveCell.FormulaR1C1 = "Natalie"
Range("N10").Select
ActiveCell.FormulaR1C1 = "Martin"
Range("N9").Select
ActiveCell.FormulaR1C1 = "Marcia"
Range("N8").Select
ActiveCell.FormulaR1C1 = "Mark"
Range("N7").Select
ActiveCell.FormulaR1C1 = "Laura"
Range("N6").Select
ActiveCell.FormulaR1C1 = "Lee"
Range("N5").Select
ActiveCell.FormulaR1C1 = "Jade"
Range("N4").Select
ActiveCell.FormulaR1C1 = "Graham"
Range("N3").Select
ActiveCell.FormulaR1C1 = "Impatex"
Range("N2").Select
ActiveCell.FormulaR1C1 = "James"
Range("N3").Select
Columns("N:N").ColumnWidth = 10
Range("M2:N17").Select
ActiveWorkbook.Worksheets("ExcelReport_08_10_2014").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ExcelReport_08_10_2014").Sort.SortFields.Add Key:= _
Range("M2:M17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("ExcelReport_08_10_2014").Sort
.SetRange Range("M2:N17")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWindow.SmallScroll ToRight:=5
Columns("O:O").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("O2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[1],R2C13:R17C14,2)"
Range("O2").Select
Selection.AutoFill Destination:=Range("O2:O661")
Range("O2:O661").Select
Range("O1").Select
ActiveCell.FormulaR1C1 = "User"
Columns("O:O").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("P:P,M:M,N:N").Select
Range("N1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("M4").Select
ActiveWindow.LargeScroll ToRight:=-1
Range("A1").Select
End Sub