S
Skankles
This is a Macro that works for archiving data from one reusable master
sheet to an archive sheet...
Sub copy_1()
Dim sourceRange As Range
Dim destrange As Range
Dim Yo As Integer
Yo = 25 * (Month(Sheets("Data Central").Range("C2")) - 1) + 1
Set sourceRange = Sheets("Data Central").Range
("C5:C29,D529,K5:K29")
Set destrange = Sheets("Sheet2").Cells(Yo, 1)
sourceRange.Copy destrange
Sheets("Sheet2").Columns("A:C").ClearFormats
Sheets("Sheet2").Columns("A").NumberFormat = "d-mmm"
Sheets("Sheet2").Columns("B:C").NumberFormat = "#,##0.00"
End Sub
Perhaps it is not efficient, but it works, until I try to protect the
formulas in the master sheet. When I protect "Data Central," allowing
users to edit only C2, C5:C29 and K5:29, I get a subscript out of Range
error and the VBA debugger begins.
Can somebody please help me resolve this issue? And any suggestions on
how this code could be streamlined, if that's the case, would be
appreciated.
Later.
James
sheet to an archive sheet...
Sub copy_1()
Dim sourceRange As Range
Dim destrange As Range
Dim Yo As Integer
Yo = 25 * (Month(Sheets("Data Central").Range("C2")) - 1) + 1
Set sourceRange = Sheets("Data Central").Range
("C5:C29,D529,K5:K29")
Set destrange = Sheets("Sheet2").Cells(Yo, 1)
sourceRange.Copy destrange
Sheets("Sheet2").Columns("A:C").ClearFormats
Sheets("Sheet2").Columns("A").NumberFormat = "d-mmm"
Sheets("Sheet2").Columns("B:C").NumberFormat = "#,##0.00"
End Sub
Perhaps it is not efficient, but it works, until I try to protect the
formulas in the master sheet. When I protect "Data Central," allowing
users to edit only C2, C5:C29 and K5:29, I get a subscript out of Range
error and the VBA debugger begins.
Can somebody please help me resolve this issue? And any suggestions on
how this code could be streamlined, if that's the case, would be
appreciated.
Later.
James