Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Skankles
 
Posts: n/a
Default Macro Errs when sheet is protected

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   Report Post  
Skankles
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Can a macro format a hidden sheet? Robert Excel Discussion (Misc queries) 1 February 9th 05 07:13 PM
how to hide rows in a protected sheet Prakash Excel Worksheet Functions 7 January 18th 05 03:42 PM
2 questions, copying data from sheet to sheet and assigning macro Boris Excel Worksheet Functions 0 December 16th 04 07:11 PM
Macro, select Sheet "Number", NOT Sheet Name DAA Excel Worksheet Functions 4 November 30th 04 06:29 PM
Why can't my macro use Auto Filter when I told the Sheet Protecti. KC Rippstein Excel Worksheet Functions 1 October 28th 04 07:13 PM


All times are GMT +1. The time now is 09:26 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"