VBA runtime error - 1004 when trying to format or delete cells after pasting values

Question

I am trying to use VBA to make my life easier but I keep getting a problem which I can't work around. Basically what I want is to copy some values from several output csv files I've got, to a nice formatted excel file. Then according to some bases numbers delete values or format the cells.
However I keep getting the same error message Run-time error '1004' application-defined or object defined error. I am doing that using many output files and pasting values at the same table file but on different sheets (10.2a, 10.2b, 10.2c, ...) by having macros for each sheet. I run all the macros in one using another macro that contains all the other macros I looked a lot in other posts but don't understand where the error comes from. Any help would be much appreciated. The code I use for one sheet is below as an example.

Sub Table_10_2a()
        '
        ' Copy Data from one file to another
        '
        Dim Output As Workbook
        Dim Table As Workbook
        Dim i As Integer

        'Open workbooks
        Set Output = Workbooks.Open("O:\...\Output.csv")
        Set Table = Workbooks.Open("O:\...\Table.xlsx") 

        'Copy paste data from output file to Table
        Output.Sheets("Output1").Range("B3:E7").Copy
        Table.Sheets("10.2a").Range("B11").PasteSpecial xlValues

        Output.Sheets("Output1").Range("B9:E13").Copy
        Table.Sheets("10.2a").Range("B17").PasteSpecial xlValues

        Output.Sheets("Output1").Range("B15:E15").Copy
        Table.Sheets("10.2a").Range("B23").PasteSpecial xlValues

        Output.Sheets("Output1").Range("B17:E21").Copy
        Table.Sheets("10.2a").Range("B26").PasteSpecial xlValues

        Output.Sheets("Output1").Range("B23:E27").Copy
        Table.Sheets("10.2a").Range("B32").PasteSpecial xlValues

        Output.Sheets("Output1").Range("B29:E29").Copy
        Table.Sheets("10.2a").Range("B38").PasteSpecial xlValues

        Output.Sheets("Output1").Range("B30:E30").Copy
        Table.Sheets("10.2a").Range("B40").PasteSpecial xlValues

        For i = 2 To 5
        'Delete cells for values below 30
         If Table.Sheets("10.2a").Cells(40, i).Value < 30 Then
            Table.Sheets("10.2a").Range(Cells(26, i), Cells(36, i)).ClearContents
            Table.Sheets("10.2a").Cells(38, i).NumberFormat = """[""0""]"""
            Table.Sheets("10.2a").Cells(40, i).NumberFormat = """[""0""]"""
         End If

    'Format cells for values below 50
        If Table.Sheets("10.2a").Cells(40, i).Value < 50 And Table.Sheets("10.2a").Cells(40, i).Value > 30 Then
            Table.Sheets("10.2a").Range(Cells(26, i), Cells(38, i)).NumberFormat = """[""0.0""]"""
            Table.Sheets("10.2a").Cells(40, i).NumberFormat = """[""0""]"""
        End If

        Next i

        'Save file
            Table.Save

        'Close files
            Output.Close
            Table.Close

        End Sub

1004 could happen if `Table` is not the ActiveWorkbook at run-time (it appears to be the ActiveWorkbook). Otherwise 1004 can happen if the worksheet is protected (although I'd expect the `PasteSpecial` method would raise the error in that case. Let us know where the error happens and it will be easier to troubleshoot. — David Zemens, Jul 18 '14 at 16:25
@DavidZemens Hi it breaks here: Table.Sheets("10.2a").Range(Cells(26, i), Cells(36, i)).ClearContents I tried looking if the cells in the Table workbook have a strange format but they are all the same. Thank you – PetHnr 21 hours ago — PetGous, Jul 22 '14 at 07:35

score 1 · Accepted Answer · edited Jun 18 '15 at 14:30

This usage of Cells inside Range to build a block of cells commonly falls victim to an unqualified reference. In this case, you are using Table.Sheets("10.2a") to specify the sheet for Range but are not using the same qualifier on Cells. This means that Cells will use the default context available which varies with where the code is executing. Possibilities:

Inside a code module or ThisWorkbook, Cells refers to the ActiveSheet
Inside a Worksheet code behind, Cells refers to that Worksheet regardless of the ActiveSheet

Use Address to get around the different sheets

One approach is to follow the call to Cells with Address. This resolves the problem because Address returns the cell address without the sheet name. This is then interpreted by Range within its context which is Sheets("10.2a").

Range(Cells(26, i).Address, Cells(36, i).Address).ClearContents

Qualify the reference (generally preferred)

Another way to resolve this error is to qualify the reference by adding a sheet name before Cells: Table.Sheets("10.2a").Cells. Full line:

Range(Table.Sheets("10.2a").Cells(26, i), Table.Sheets("10.2a").Cells(36, i)).ClearContents

This type of code looks better within a With... End With block.

@Byron well the op accepted this, and technically my answer also solves the unqualified reference by coercing an address from the Cells object (I agree, this is not an optimal approach but for whatever reason, is what I came up) irrespective of the Activesheet. You're correct, though, that proper reference and qualifying objects is good programing and would've avoided the error altogether. Cheers, and feel free to either edit this answer or add a new one of your own. — David Zemens, Jun 17 '15 at 22:50
I edited this to reflect the unqualified reference. Feel free to roll back if you prefer a different wording... I always feel weird editing answers. — Byron Wall, Jun 18 '15 at 14:34

VBA runtime error - 1004 when trying to format or delete cells after pasting values

1 Answers1