Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Copy Macro?
Hi,
I have an macro that would protect the sheet and allow insert/delete columns and rows, as follow, Sub Auto_Open() With Worksheets("Sheet 1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub i would like to modify it so that when i duplicate "Sheet 1" to "Sheet 2", "Sheet 3", Sheet 4".....and so on, the macro would still work on all sheets that start with "Sheet", any idea? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Copy Macro?
Option Explicit
Sub Auto_Open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets If LCase(Left(wks.Name, 5)) = "sheet" Then With wks .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End If Next wks End Sub If you wanted all those other protection options: Option Explicit Sub Auto_Open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets If LCase(Left(wks.Name, 5)) = "sheet" Then With wks .Protect Password:="hi", userinterfaceonly:=True, _ DrawingObjects:=False, Contents:=True, _ Scenarios:=False, AllowFormattingCells:=True, _ AllowFormattingColumns:=True, AllowFormattingRows:=True, _ AllowInsertingColumns:=True, AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, _ AllowFiltering:=True, AllowUsingPivotTables:=True .EnableOutlining = True End With End If Next wks End Sub Henry wrote: Hi, I have an macro that would protect the sheet and allow insert/delete columns and rows, as follow, Sub Auto_Open() With Worksheets("Sheet 1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub i would like to modify it so that when i duplicate "Sheet 1" to "Sheet 2", "Sheet 3", Sheet 4".....and so on, the macro would still work on all sheets that start with "Sheet", any idea? Thanks -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Copy Macro?
Watch out for line wrap on that second sample.
Dave Peterson wrote: Option Explicit Sub Auto_Open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets If LCase(Left(wks.Name, 5)) = "sheet" Then With wks .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With End If Next wks End Sub If you wanted all those other protection options: Option Explicit Sub Auto_Open() Dim wks As Worksheet For Each wks In ThisWorkbook.Worksheets If LCase(Left(wks.Name, 5)) = "sheet" Then With wks .Protect Password:="hi", userinterfaceonly:=True, _ DrawingObjects:=False, Contents:=True, _ Scenarios:=False, AllowFormattingCells:=True, _ AllowFormattingColumns:=True, AllowFormattingRows:=True, _ AllowInsertingColumns:=True, AllowInsertingRows:=True, _ AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, _ AllowFiltering:=True, AllowUsingPivotTables:=True .EnableOutlining = True End With End If Next wks End Sub Henry wrote: Hi, I have an macro that would protect the sheet and allow insert/delete columns and rows, as follow, Sub Auto_Open() With Worksheets("Sheet 1") .Protect Password:="hi", userinterfaceonly:=True .EnableOutlining = True End With ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _ False, AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True, AllowInsertingColumns:=True, AllowInsertingRows _ :=True, AllowInsertingHyperlinks:=True, AllowDeletingColumns:=True, _ AllowDeletingRows:=True, AllowSorting:=True, AllowFiltering:=True, _ AllowUsingPivotTables:=True End Sub i would like to modify it so that when i duplicate "Sheet 1" to "Sheet 2", "Sheet 3", Sheet 4".....and so on, the macro would still work on all sheets that start with "Sheet", any idea? Thanks -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-populate, Auto-copy or Auto-fill? | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Saving a copy of excel file in macro but have it auto write | Excel Worksheet Functions | |||
is there a way to auto copy cells similar to auto sum? | Excel Worksheet Functions | |||
auto copy | Excel Programming |