Results 1 to 3 of 3
  1. #1
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    Answered: Range class failed error

    I have this Macro to backup some data in a different Workbook and then delete the Worksheet that was active when I ran the Macro.


    Code:
    Sub DeleteSheet()
    '
    ' DeleteSheet Macro
    '
    ActiveSheet.Range("AV1:BB1").Copy
    
    Workbooks.Open ("FILEPATH")
    
    
    ActiveSheet.Unprotect
    
    Dim ws As Worksheet
    
    Set ws = ActiveSheet
    
    For Each Cell In ws.Columns(1).Cells
        If IsEmpty(Cell) = True Then Cell.Select: Exit For
    Next Cell
    
        ActiveCell.PasteSpecial (xlPasteValues)
    
    ActiveCell.Offset(1, 0).Select
    
    ActiveSheet.Protect
    
    ActiveWorkbook.Close savechanges:=True
    
    
     
    ActiveWindow.SelectedSheets.Delete
        
    End Sub
    The Macro copies the desired cells, opens the Workbook where I backup data, looks for the first open cell in column A and pastes just the values. then closes the backup Workbook and deletes the Worksheet where I started.

    When I run the macro I get and Error 1004 Range Class Failed

    Debuging highlights the following code.
    Code:
    ActiveCell.PasteSpecial (xlPasteValues)
    If I change nothing and end Debuging, close the backkup Workbook (the macro had opened it) and run the macro again it works perfectly.
    This sequence happens every time.

    What is wrong?

  2. Best Answer
    Posted by Doug60M

    "Was given this code at another forum that works great

    Code:
    Sub DeleteSheet()
     '
     ' DeleteSheet Macro
     '
     Dim src As Variant
     
    src = ActiveSheet.Range("AV1:BB1").Value
     
    Workbooks.Open ("E:\Temp\Static.xlsx")
     ActiveSheet.Unprotect
     
    For Each Cell In Columns(1).Cells
         If IsEmpty(Cell) = True Then Cell.Select: Exit For
     Next Cell
     
        Selection.Resize(, 7).Select
         Selection.Value = src
     
    ActiveCell.Offset(1, 0).Select
     
    ActiveSheet.Protect
     
    ActiveWorkbook.Close savechanges:=True
     
    ActiveWindow.SelectedSheets.Delete
     
    End Sub
    "


  3. #2
    Join Date
    May 2014
    Posts
    35
    Provided Answers: 2

    Solved

    Was given this code at another forum that works great

    Code:
    Sub DeleteSheet()
     '
     ' DeleteSheet Macro
     '
     Dim src As Variant
     
    src = ActiveSheet.Range("AV1:BB1").Value
     
    Workbooks.Open ("E:\Temp\Static.xlsx")
     ActiveSheet.Unprotect
     
    For Each Cell In Columns(1).Cells
         If IsEmpty(Cell) = True Then Cell.Select: Exit For
     Next Cell
     
        Selection.Resize(, 7).Select
         Selection.Value = src
     
    ActiveCell.Offset(1, 0).Select
     
    ActiveSheet.Protect
     
    ActiveWorkbook.Close savechanges:=True
     
    ActiveWindow.SelectedSheets.Delete
     
    End Sub

  4. #3
    Join Date
    Jul 2015
    Posts
    1
    i am new here this is a good forum

Posting Permissions

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