Results 1 to 2 of 2

Thread: Run-time error '1004': Application-defined or object-defined error

  1. #1

    Run-time error '1004': Application-defined or object-defined error



    Register for a FREE account, and/
    or Log in to avoid these ads!

    When I run my code I get the following error "Run-time error '1004': Application-defined or object-defined error" with this part of the code:


    Code:
    ws4.Range("B3:CS74").Copy Destination:=ws5.Range("B3:CS74")

    Any help would be much appreciated

    Code:
    Sub AngliaUpdate()
    Dim wb1 As ThisWorkbook
    Dim wb2 As Workbook
    Dim wb3 As Workbook
     
    'ThisWorkbook is [Core Regional Template.xlsx
    Set wb1 = ThisWorkbook
    Set wb2 = Application.Workbooks("CN1 - CSM F15.xlsx")
    Set wb3 = Application.Workbooks("CN2 - AE F15.xlsx")
     
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim ws3 As Worksheet
    Dim ws4 As Worksheet
    Dim ws5 As Worksheet
    Dim ws6 As Worksheet
    Dim ws7 As Worksheet
    Dim ws8 As Worksheet
    Dim ws9 As Worksheet
    Dim ws10 As Worksheet
    Dim ws11 As Worksheet
    Dim ws12 As Worksheet
     
    Set ws1 = wb1.Sheets("Summary")
    Set ws2 = wb2.Sheets("Summary")
    Set ws3 = wb3.Sheets("Summary")
    Set ws4 = wb1.Sheets("Summary by Customer")
    Set ws5 = wb2.Sheets("Summary by Customer")
    Set ws6 = wb3.Sheets("Summary by Customer")
     
    'Unprotect workbooks
    wb2.Activate
    Call UnlockWs
    ws3.Activate
    Call UnlockWs
     
    'Copy Summary from Master to regional
        wb2.Activate
        ws2.Activate
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        ActiveSheet.Outline.ShowLevels ColumnLevels:=2
        wb3.Activate
        ws3.Activate
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        ActiveSheet.Outline.ShowLevels ColumnLevels:=2
        wb1.Activate
       
        ws1.Range("B6:W100").Copy Destination:=ws2.Range("B6:W100")
        ws1.Range("B6:W100").Copy Destination:=ws3.Range("B6:W100")
     
        wb2.Activate
        Cells.Replace What:="[Core Regional Template.xlsx]", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
        ActiveSheet.Outline.ShowLevels RowLevels:=1
        ActiveSheet.Outline.ShowLevels ColumnLevels:=1
        Calculate
        Call LockWs
        Range("A1").Select
        ActiveWindow.ScrollRow = ActiveCell.Row
       
    'Remove source file ref from formulas, close groups and protect worksheet
        wb3.Activate
        Cells.Replace What:="[Core Regional Template.xlsx]", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
        ActiveSheet.Outline.ShowLevels RowLevels:=1
        ActiveSheet.Outline.ShowLevels ColumnLevels:=1
        Calculate
        Call ProtectWs
        Range("A1").Select
        ActiveWindow.ScrollRow = ActiveCell.Row
       
    'Copy Summary By Customer from Master to Region
     
        ws5.Activate
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        ActiveSheet.Outline.ShowLevels ColumnLevels:=2
        ws6.Activate
        ActiveSheet.Outline.ShowLevels RowLevels:=2
        ActiveSheet.Outline.ShowLevels ColumnLevels:=2
     
        ws4.Range("B3:CS74").Copy
        ws5.Range("B3:CS74").PasteSpecial
     
        ws4.Range("B3:CS74").Copy Destination:=ws5.Range("B3:CS74")
        ws4.Range("B3:CS74").Copy Destination:=ws6.Range("B3:CS74")
     
    'Remove source file ref from formulas, close groups and protect worksheet
        wb2.Activate
        Cells.Replace What:="[Core Regional Template.xlsx]", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
        ActiveSheet.Outline.ShowLevels RowLevels:=1
        ActiveSheet.Outline.ShowLevels ColumnLevels:=1
        Calculate
        Call ProtectWs
        Range("A1").Select
        ActiveWindow.ScrollRow = ActiveCell.Row
       
        wb3.Activate
        Cells.Replace What:="[Core Regional Template.xlsx]", Replacement:="", _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:= _
        False, ReplaceFormat:=False
        ActiveSheet.Outline.ShowLevels RowLevels:=1
        ActiveSheet.Outline.ShowLevels ColumnLevels:=1
        Calculate
        Call LockWs
        Range("A1").Select
        ActiveWindow.ScrollRow = ActiveCell.Row
      
       
    End Sub
    This is part of my full code.

    Essentially what I'm trying to each is copy specific ranges of 5 sheets from the source workbook to 5 near-identical sheets (only a few static values differ) in 47 other workbooks, 235 sheets in total. Then copy the 5th sheet in each workbook 50 times.

    The layout of my sheets is something like this;

    1a, b, c, d, e, e, e, e, e....
    2a, b, c, d, e, e, e, e, e...
    3a, b, c, d, e, e, e, e, e..
    etc
    Last edited by robmythoughts; 2014-10-30 at 12:42 AM. Reason: Added code

  2. #2
    Please disregard this part of the code
    Code:
    ws4.Range("B3:CS74").Copy 
        ws5.Range("B3:CS74").PasteSpecial

Tags for this Thread

Posting Permissions

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