Hello All,
I am copying cells from a sheet titled "Data" to another sheet called "Status Update". The user will have the flexibility to edit only ranges in two columns, L & M, and others will be locked. I have the code to copy the cells and paste it but the allow edit ranges part gives the run time 1004: Application defined or object-defined error. The code i use is given below. Any help is appreciated.
ActiveWorkbook.Worksheets("Status Update").Unprotect "12345"
ActiveSheet.Cells.Locked = False
Set sh = Sheets("Data")
sh.Range("M1:M" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row).AutoFilter 1, "WIP"
With Worksheets("Data").AutoFilter.Range
Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
sh.Range("A2:A" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row).EntireRow.SpecialCells(xlCellTypeVisible).Copy
ActiveWorkbook.Worksheets("WIP Tracker").Range("A3").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets("WIP Tracker").Range("A3").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Sheets("Status Update").Columns("L:M").Select
Sheets("Status Update").Protection.AllowEditRanges.Add Title:="Range1", Range:=Columns( _
"L:M")
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
I am copying cells from a sheet titled "Data" to another sheet called "Status Update". The user will have the flexibility to edit only ranges in two columns, L & M, and others will be locked. I have the code to copy the cells and paste it but the allow edit ranges part gives the run time 1004: Application defined or object-defined error. The code i use is given below. Any help is appreciated.
ActiveWorkbook.Worksheets("Status Update").Unprotect "12345"
ActiveSheet.Cells.Locked = False
Set sh = Sheets("Data")
sh.Range("M1:M" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row).AutoFilter 1, "WIP"
With Worksheets("Data").AutoFilter.Range
Range("A" & .Offset(1, 0).SpecialCells(xlCellTypeVisible)(1).Row).Select
End With
sh.Range("A2:A" & sh.Cells(sh.Rows.Count, 1).End(xlUp).Row).EntireRow.SpecialCells(xlCellTypeVisible).Copy
ActiveWorkbook.Worksheets("WIP Tracker").Range("A3").PasteSpecial xlPasteValues
ActiveWorkbook.Worksheets("WIP Tracker").Range("A3").PasteSpecial xlPasteFormats
Application.CutCopyMode = False
Sheets("Status Update").Columns("L:M").Select
Sheets("Status Update").Protection.AllowEditRanges.Add Title:="Range1", Range:=Columns( _
"L:M")
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