Macro - Active sheet (want to no non-specific)

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
310
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
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

RickXL

MrExcel MVP
Joined
Sep 9, 2013
Messages
4,314
ActiveSheet

Hi,

I haven't actually tried it but this should work:

Rich (BB code):
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
    ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:= _
        Range("M2:M17"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveSheet.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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,550
Messages
5,529,472
Members
409,884
Latest member
Msinmath
Top