+ Reply to Thread
Results 1 to 3 of 3

Digital Signature Stops Macro

Hybrid View

  1. #1
    Registered User
    Join Date
    07-21-2011
    Location
    Bournemouth, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Digital Signature Stops Macro

    I have a new problem with the same code so have started a new thread. (Hope this is O.K. moderators?)

    My code is conditionally opening spreadsheets in a folder, pulling information from certain cells and putting them in a master spreadsheet.

    When it comes across a spreadsheet that has been digitally signed I get the error message-

    Copy method of range class failed

    Option Explicit
    
    Sub nicksmacro()
    
      Dim C As Long
      Dim Cell As Range
      Dim DstRng As Range
      Dim MyDir, FN As String
      Dim LR As Long
      Dim SrcRng As Range
      
        Application.ScreenUpdating = False
           
       'Runtime
        MyDir = "\\C:\Test Folder\"
        
        Set DstRng = ThisWorkbook.Sheets("Sheet1").Range("A2")
        Set DstRng = DstRng.Resize(RowSize:=DstRng.Parent.UsedRange.Rows.Count)
        
          FN = Dir(MyDir & "*Summary*.xls")
        
          Do While FN <> ""
            If FN <> ThisWorkbook.Name Then
                With Workbooks.Open(MyDir & FN)
                    Set SrcRng = .Sheets("Sheet1").Range("E3,c22,c23,c24,c25")
                      For Each Cell In SrcRng.Cells
                        Cell.Copy DstRng.Offset(LR, C)
                        C = C + 1
                      Next Cell
                    .Close False
                    C = 0
                    LR = LR + 1
                End With
            End If
            FN = Dir()
          Loop
          
        Application.ScreenUpdating = True
    End Sub
    The debugger points at this line:

    Cell.Copy DstRng.Offset(LR, C)

    Is there a way to stop this happening?

  2. #2
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Digital Signature Stops Macro

    Hi nickpenny
    I look at the code and ask myself...what does LR evaluate to and ...what does C evaluate to? They're Dimmed as variables but I see no value set for either one.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  3. #3
    Registered User
    Join Date
    07-21-2011
    Location
    Bournemouth, England
    MS-Off Ver
    Excel 2007
    Posts
    8

    Re: Digital Signature Stops Macro

    If you follow the loop back, they increment the row count so the copied cells are pasted to the new spread sheet sequentially.

    I really need to know why it stops working when it is trying to extract information from cells in a digitally signed file.

+ Reply to Thread

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