Results 1 to 1 of 1

Copy/paste past protection and macros

Threaded View

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Office 365 64-Bit, 2108, build 14326.21018
    Posts
    3,974

    Copy/paste past protection and macros

    Here's the situation: Workbook A is my tool; it wants to copy the data from Workbook B, Sheet 1, to Workbook C, Sheet "Posn".

    The problem: Wkbk C has a ton of macros in it, all used when the workbook is being viewed by a user. In this case I'm just updating the workbook via macro, so don't want the macros within WkbkC to fire. The particular event causing me heartburn now is that
    Wkbk C, sheet "Posn" is protected, and has code tied to the "Worksheet_Activate" event, so if I activate the sheet the macros fire and interfere with my code.

    I'd like to either (Best solution) know how to disable macros within only that workbook when my macro opens it, and keep them suppressed the whole time I'm working in it, or (second best) be able to copy and paste my data into a protected worksheet. Below is the code I'm trying to use for the copy and paste (without the Activate it bombs due to protection status:

    Set ws = Tool.Worksheets("Posn")
       
        ws.Activate
        
        With ws
            .Unprotect
            .AutoFilterMode = False
    
            'Make sure we have a clean canvas to write on
            Cells.Select
            Selection.ClearContents
        End With
        
        'Copy the Posn Information
        UPDT.Sheets("Sheet1").UsedRange.Copy
        
        'Paste the values
        Tool.Sheets("Posn").Cells(2, 1).PasteSpecial Paste:=xlPasteValues
            
        'Clear the Clipboard
        Application.CutCopyMode = False
        
        'reset the used range
        Tool.Sheets("Posn").UsedRange
    Last edited by jomili; 08-30-2011 at 08:42 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1