CODEWITHSUNDEEP
vbaexcel
trevor_bye
trevor_bye

Reputation: 31

VBA Error 1004 While Setting Ranges Equal

No matter what I do I can't get this error to go away. This current version of code is an effort to completely spell out each reference. I'm getting the error after the 'set range for BOM components' line. Most topics point out that using .Range(Cells()) without specifying where the cells are located will lead to this error, but I've done the complete overkill referencing so I'm not sure why it still errors out. Only one workbook here as well.

@Scott Craner @BruceWayne I figured it out finally; the Oracle report comes out in a pretty f****d up format, and the first sheet in the loop was causing the ranges to actually be unequal. As soon as I deleted that sheet it ran perfectly.

        Sub Macro3()
            '
            ' Macro3 Macro
            '
            ' Keyboard Shortcut: Ctrl+c
            '
            Dim ws As Worksheet
            Dim lastRow As Integer
            Dim summaryRow As Integer
            Dim currentSheetRows As Integer
            Dim i As Integer
            Dim j As Integer
            summaryRow = 2

                For Each ws In ActiveWorkbook.Worksheets
                    If ws.Range("L3") = "" Then
                        currentSheetRows = ws.Cells(ws.Rows.Count, "F").End(xlUp).Row
                        i = summaryRow
                        j = summaryRow

                        'set range for item # and item UOM'
                        For j = summaryRow To (summaryRow + (currentSheetRows - 3))
                        Sheets("Summary").Cells(j, 1).Value = ws.Cells(2, 1).Value
                        Next j

                        For i = summaryRow To (summaryRow + (currentSheetRows - 3))
                        Sheets("Summary").Cells(i, 2).Value = ws.Cells(2, 1).Value
                        Next i

                        'set range for BOM components'
                        ThisWorkbook.Worksheets("Summary").Range(ThisWorkbook.Worksheets("Summary").Cells(summaryRow, 3), ThisWorkbook.Worksheets("Summary").Cells((summaryRow + (currentSheetRows - 3)), 6)).Value = ThisWorkbook.Worksheets(ws.Name).Range(ThisWorkbook.Worksheets(ws.Name).Cells(3, 6), ThisWorkbook.Worksheets(ws.Name).Cells(currentSheetRows, 9)).Value
                        summaryRow = summaryRow + currentSheetRows
                    End If
                Next ws
            End Sub

Upvotes: 1

Views: 685

Answers (3)

user3598756
user3598756

Reputation: 29421

You're using 

ActiveWorkbook

To set the loop current sheet

and then 

ThisWorkbook

in the copy/paste values statement that errors

It may then be the workbook you're running the macro from (ThisWorkbook) differs from the currently active one (ActiveWorkbook) whose worksheets you're looping through

In such a case just change ThisWorkbook to ActiveWorkbook

Upvotes: 1

BruceWayne
BruceWayne

Reputation: 23283

Woah - first, check that the ranges are equal sizes. Then, I highly suggest using some variables for your sheet names and ranges:

Sub t()
Dim summaryWS As Worksheet
Dim otherWS As Worksheet

Set summaryWS = ThisWorkbook.Sheets("Summary")
Set otherWS = ThisWorkbook.Worksheets(ws.Name)

Dim copyRng As Range, pasteRng As Range

With summaryWS
    Set copyRng = .Range(.Cells(SummaryRow, 3), .Cells((SummaryRow + (currentSheetRows - 3)), 6))
End With

With otherWS
    Set pasteRng = .Range(.Cells(3, 6), .Cells(currentSheetRows, 9))
End With

pasteRng.Value = copyRng.Value

End Sub

Does that help your issue?

Upvotes: 1

grug.0
grug.0

Reputation: 355

The problem is here:

ThisWorkbook.Worksheets("Summary").Range(ThisWorkbook.Worksheets("Summary").Cells(summaryRow, 3), ThisWorkbook.Worksheets("Summary").Cells((summaryRow + (currentSheetRows - 3)), 6)).Value = ThisWorkbook.Worksheets(ws.Name).Range(ThisWorkbook.Worksheets(ws.Name).Cells(3, 6), ThisWorkbook.Worksheets(ws.Name).Cells(currentSheetRows, 9)).Value

the subscript is out of range.

Try

With Sheets(ws.name)
    .Cells({var},{var}).Value = {var}

Upvotes: 0

Related Questions