Runtime error 1004 application-defined object-defined

[stevoDE]

Hi

I'm getting a runtime error 1004 message on the following code when trying to execute the line in bold and I don't know why. I have checked to see if the macro is in module as opposed to a sheet and it is so that's not what is causing the problem. It works fine if I am in the sheet I am trying to execute the code on but fails if I don't have that sheet activated i.e. the first line of code also fails unless I am in that worksheet.

New to VBA but I'm guessing it's something to do with the way I have declared Thisworkbook.

Option Explicit

Dim DataFilepath As String
Dim DataFile As Workbook
Dim GiltRV As Workbook

Sub ImportData()

Set GiltRV = ThisWorkbook

GiltRV.Sheets("CALCS2 ALL").Range("E14", Range("E14").End(xlDown)).clearcontents
GiltRV.Sheets("T-1 Data").Range("B8", Range("B8").End(xlDown)).clearcontents

Any help appreciated.

[Norie]

Try this.

Option Explicit

Dim DataFilepath As String
Dim DataFile As Workbook
Dim GiltRV As Workbook

Sub ImportData()

    Set GiltRV = ThisWorkbook

    With GiltRV

        With .Sheets("CALCS2 ALL")
            .Range("E14", .Range("E14").End(xlDown)).ClearContents
        End With

        With .Sheets("T-1 Data")
            .Range("B8", .Range("B8").End(xlDown)).ClearContents
        End With

    End With

[stevoDE]

Thank Norie. I'll try this in the morning & report back.

Out of interest, why do we have to use 'with' and why separate statements for each line of code? It seems quite longwinded. Is there actually any benefit in declaring a variable for thisworkbook in this instance as opppsed to just using Workbooks(.....)? Apologies for so many questions, still trying to get to grips with thisworkbook and when to use it.

[Norie]

You don't have to use With, here's an alternative way of doing things.

Set GiltRV = ThisWorkbook

GiltRV.Sheets("CALCS2 ALL").Range("E14", GiltRV.Sheets("CALCS2 ALL").Range("E14").End(xlDown)).ClearContents

GiltRV.Sheets("T-1 Data").Range("B8", GiltRV.Sheets("T-1 Data").Range("B8").End(xlDown)).ClearContents

As for using variables, again you don't have to, but it might help to use variables with an appropriate names to help identify the workbooks you are working with.

If you use Workbooks(...) instead of ThisWorkbook you would need to know the exact name of the workbook the code is in which probably won't change but you never know.

[stevoDE]

The above works Norie. Thanks for the info and your help.