Hidden sheet

[topi1]

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

 

[Arithos]

How do I also reprotect?

Sub protecting()
ActiveWorkbook.Unprotect "password"


ActiveWorkbook.Protect "password"
End Sub

 

[Arithos]

Instead of working toward hiding unhiding etc, is there a way to Keep "report-start" and "123" unhiden but password protected, and then to use some form of user interface protection?
Thanks.

not that I know of, BUT..

if you look at my code in Post #5, you can see I have screenupdating = False, this means the user will never actually see anything... and then you make the sheets go hidden again.

 

[Arithos]

Your code looks very recorded =P

But, the report-start sheet contains a template or just simple information?

 

[Arithos]

You did this to copy.

[COLOR=#333333]Sheets([/COLOR][COLOR=#ff0000]"Report-start"[/COLOR][COLOR=#333333]).Select[/COLOR]


[COLOR=#333333]Columns("X:Z").Select[/COLOR]
[COLOR=#333333]Selection.Copy[/COLOR]


[COLOR=#333333]Sheets([/COLOR][COLOR=#0000ff]"123"[/COLOR][COLOR=#333333]).Select[/COLOR]
[COLOR=#333333]Columns("X:Z").Select[/COLOR]
[COLOR=#333333]Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _[/COLOR]
[COLOR=#333333]xlNone, SkipBlanks:=False, Transpose:=False[/COLOR]
[COLOR=#333333]Application.CutCopyMode = False[/COLOR]

You could do this instead

[COLOR=#333333]Sheets([/COLOR][COLOR=#0000ff]"123"[/COLOR][COLOR=#333333]).[/COLOR][COLOR=#333333]Columns("X:Z").value = [/COLOR][COLOR=#333333]Sheets([/COLOR][COLOR=#ff0000]"Report-start"[/COLOR][COLOR=#333333]).Columns("X:Z").value[/COLOR]

Code looks so much cooler, and its faster..

 

[wigi]

Keep the sheets hidden, do NOT Select or Activate and protect the sheets with argument UserInterfaceOnly set to True. Then you're good.

 

[Arithos]

Keep the sheets hidden, do NOT Select or Activate and protect the sheets with argument UserInterfaceOnly set to True. Then you're good.

Interresting, so you would do

userinterfaceonly = true

in the start of your code? and set it to False in the end?

or is it

Application.UserControl = True

 

[topi1]

Got it done. Many many thanks to all.

 

[wigi]

Interresting, so you would do

userinterfaceonly = true

in the start of your code? and set it to False in the end?

or is it

Application.UserControl = True

Protect sheets with Userinterfaceonly when opening the file (ThisWorkbook_Open event). That's all.
Unless you would Unprotect for some reason, in that case Protect again with the property set to True.

 

[Arithos]

Protect sheets with Userinterfaceonly when opening the file (ThisWorkbook_Open event). That's all.
Unless you would Unprotect for some reason, in that case Protect again with the property set to True.

Thx, useful to know