Hi,
First of all, thanks to all who offer their valuable time and help. Much appreciated. I use the following code in my workbook from the "Home" sheet. I keep the sheets "123" and "Report-start" unhidden in order for macro to work. Is there a way to modify the following macro such that I can keep "123" and "Report-start" hidden? Please note that I password protect the entire workbook.
Many thanks.
Sub Finishall()
'
' Finishall Macro
'
'
Sheets("Report-start").Select
Columns("X:Z").Select
Selection.Copy
Sheets("123").Select
Columns("X:Z").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("123").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("123").Sort.SortFields.Add Key:=Range("Y1:Y82"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("123").Sort.SortFields.Add Key:=Range("Z1:Z82"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("123").Sort
.SetRange Range("X1:Z100")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("X:X").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With
Columns("Y").Replace 2, "#N/A", xlWhole
Intersect(Columns("X"), Columns("Y").SpecialCells(xlConstants, xlErrors).EntireRow).ClearContents
Columns("Y").Replace "#N/A", 2, xlWhole
LastRow = Cells(Rows.Count, "x").End(xlUp).Row
Range("x1:x" & LastRow).Copy
Sheets("Report-start").Select
Range("A1").Select
Sheets("123").Select
Range("A1").Select
Sheets("Home").Select
Range("O4").Select
End Sub
First of all, thanks to all who offer their valuable time and help. Much appreciated. I use the following code in my workbook from the "Home" sheet. I keep the sheets "123" and "Report-start" unhidden in order for macro to work. Is there a way to modify the following macro such that I can keep "123" and "Report-start" hidden? Please note that I password protect the entire workbook.
Many thanks.
Sub Finishall()
'
' Finishall Macro
'
'
Sheets("Report-start").Select
Columns("X:Z").Select
Selection.Copy
Sheets("123").Select
Columns("X:Z").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("123").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("123").Sort.SortFields.Add Key:=Range("Y1:Y82"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("123").Sort.SortFields.Add Key:=Range("Z1:Z82"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("123").Sort
.SetRange Range("X1:Z100")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Columns("X:X").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlTop
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With ActiveSheet.Cells
.ClearFormats
.Font.Name = Application.StandardFont
.Font.Size = Application.StandardFontSize
End With
Columns("Y").Replace 2, "#N/A", xlWhole
Intersect(Columns("X"), Columns("Y").SpecialCells(xlConstants, xlErrors).EntireRow).ClearContents
Columns("Y").Replace "#N/A", 2, xlWhole
LastRow = Cells(Rows.Count, "x").End(xlUp).Row
Range("x1:x" & LastRow).Copy
Sheets("Report-start").Select
Range("A1").Select
Sheets("123").Select
Range("A1").Select
Sheets("Home").Select
Range("O4").Select
End Sub