Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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,D5:D29,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 |
#2
![]() |
|||
|
|||
![]()
More specifically, when Allow Users to Edit Ranges is in effect, the
error says Method 'Copy' of 'Range' failed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Can a macro format a hidden sheet? | Excel Discussion (Misc queries) | |||
how to hide rows in a protected sheet | Excel Worksheet Functions | |||
2 questions, copying data from sheet to sheet and assigning macro | Excel Worksheet Functions | |||
Macro, select Sheet "Number", NOT Sheet Name | Excel Worksheet Functions | |||
Why can't my macro use Auto Filter when I told the Sheet Protecti. | Excel Worksheet Functions |