VBA Excel 2010 - Runtime 1004 error when using range

Question

I am having an issue referencing ranges in my vba program. The following snippet of code shows my original code:

With Worksheets("Overall 6 mo")
    .Columns("A:G").ColumnWidth = 13.57
    .Range("A1:Z100").Rows.RowHeight = 15

    .Columns("F:G").NumberFormat = "0.00%"

    .Range("B3:G3") = Worksheets("TEMPLATE").Range("A3:F3").Value
    .Range("F4:G100") = Worksheets("TEMPLATE").Range("E4:F100").Formula

    .Range("A1").NumberFormat = "@"
    .Range("A1") = .Name
End With

This would throw the "runtime 1004 application-defined or object-defined error" after going through line 3. So then, I changed

.Range("A1:Z100").Rows.RowHeight = 15

to

.Rows.RowHeight = 15

The point was to make the cells that i need to use have a height of 15 so the change didn't hurt my program. And now, it will allow that but then throw the same error at the next line, where I reference a range again. So I'm trying to figure out why it won't allow me to use .range ? Or at least how I can fix it?

UPDATE: I have come to realize that I cannot use the .Range method anywhere in my workbook (not just in the instance above). What would disable me to use .Range everywhere?

UPDATE2: It will now no longer let me use the .Columns method in the second line. I haven't done anything but step through it a couple times. What is wrong with this thing?

UPDATE3: It seems that when i restart excel, it will allow me to run the worksheet "Overall 6 mo" code once, and then starts throwing the error every time after that. I've included the code for the rest of the sheet.

Option Explicit

Private Sub Worksheet_Activate()

    Application.ScreenUpdating = False

    Dim shIndex As Integer
    Dim rowIndex As Integer
    Dim myLastRow As Integer
    Dim shLastRow As Integer
    Dim col As Integer

    myLastRow = Worksheets("Overall 6 mo").Cells(65536, 1).End(xlUp).Row

    ' Format Worksheet
    Sheets("Overall 6 mo").Cells.Clear
    With Worksheets("Overall 6 mo")
        .Columns.ColumnWidth = 13.57
        .Rows.RowHeight = 15

        .Columns("F:G").NumberFormat = "0.00%"

        .Range("B3:G3") = Worksheets("TEMPLATE").Range("A3:F3").Value
        .Range("F4:G100") = Worksheets("TEMPLATE").Range("E4:F100").Formula

        .Range("A1").NumberFormat = "@"
        .Range("A1") = .Name
    End With

    ' Clear current sheet data
    myLastRow = Worksheets("Overall 6 mo").Cells(65536, 2).End(xlUp).Row
    Worksheets("Overall 6 mo").Range(Cells(4, 1), Cells(myLastRow, 7)).Clear

    ' Compile data from last six months and add to and display on "Overall 6 mo" sheet
    For shIndex = Worksheets.Count - 5 To Worksheets.Count
        Worksheets(shIndex).Activate

        myLastRow = Worksheets("Overall 6 mo").Cells(65536, 2).End(xlUp).Row
        shLastRow = Worksheets(shIndex).Cells(65536, 1).End(xlUp).Row

        Worksheets("Overall 6 mo").Cells(myLastRow + 1, 1).Value _
            = MonthName(Month(CDate(Worksheets(shIndex).Name)), False)

        Worksheets(shIndex).Range("A4:D" & shLastRow) _
            .Copy (Worksheets("Overall 6 mo").Cells(myLastRow + 1, 2))

    Next shIndex

    ' Call UpdateChart to clear and re-add Quality and Cost charts to wks
    Call UpdateCharts(Worksheets("Overall 6 mo").Index)

    Worksheets("Overall 6 mo").Activate
    Application.ScreenUpdating = True

End Sub

PatricK PatricK · Accepted Answer · 2013-09-02T23:48:39

You can do row height changes with:

.Range("A1:Z100").RowHeight = 15

And can you use Range Copy method

Worksheets("TEMPLATE").Range("A3:F3").Copy .Range("B3")
Worksheets("TEMPLATE").Range("E4:F100").Copy .Range("F4")

UPDATE:

Option Explicit

Private Sub Worksheet_Activate()    
    Dim oSh As Worksheet
    Dim shIndex As Long
    Dim rowIndex As Long
    Dim myLastRow As Long
    Dim shLastRow As Long

    Application.ScreenUpdating = False
    Set oSh = ThisWorkbook.Worksheets("Overall 6 mo")

    ' Format Worksheet
    With oSh
        .Cells.Clear
        .Columns.ColumnWidth = 13.57
        .Rows.RowHeight = 15
        .Columns("F:G").NumberFormat = "0.00%"
        .Range("A1").NumberFormat = "@"
        .Range("A1") = .Name

        .Range("B3:G3") = Worksheets("TEMPLATE").Range("A3:F3").Value
        .Range("F4:G100") = Worksheets("TEMPLATE").Range("E4:F100").Formula
    End With

    ' Clear current sheet data
    oSh.Range(oSh.Cells(4, 1), oSh.Cells(GetLastRow(oSh, 2), 7)).Clear

    ' Compile data from last six months and add to and display on "Overall 6 mo" sheet
    For shIndex = Worksheets.Count - 5 To Worksheets.Count
        'Worksheets(shIndex).Activate

        myLastRow = GetLastRow(oSh, 2)
        shLastRow = GetLastRow(Worksheets(shIndex), 1)

        oSh.Cells(myLastRow + 1, 1).Value = MonthName(Month(CDate(Worksheets(shIndex).Name)), False)
        Worksheets(shIndex).Range("A4:D" & shLastRow).Copy oSh.Cells(myLastRow + 1, 2)

    Next shIndex

    ' Call UpdateChart to clear and re-add Quality and Cost charts to wks
    Call UpdateCharts(oSh.Index)

    oSh.Activate
    Set oSh = Nothing
    Application.ScreenUpdating = True
End Sub

Private Function GetLastRow(oSheet As Worksheet, lngColumn As Long) As Long
    GetLastRow = oSheet.Cells(oSheet.UsedRange.SpecialCells(xlLastCell).Row + 1, lngColumn).End(xlUp).Row
End Function

Is "TEMPLATE" in the same workbook with Index 1 (or less than Worksheets.Count - 5)? I have comment out Worksheets(shIndex).Activate as seems no need to run this sub every time in the For loop.

VBA Excel 2010 - Runtime 1004 error when using range

2 Answers