0

For the purpose of this, I want to insert values into two separate worksheets within one workbook.

Why doesn't this work?

Sub TEST()

    Dim result1 As Worksheet
    Dim result2 As Worksheet

    Set result1 = ActiveWorkbook.Sheets("Sheet1")
    Set result2 = ActiveWorkbook.Sheets("Sheet2")

    result1.Range(Cells(1, 1), Cells(5, 5)).Value = "1"

    result2.Range(Cells(1, 1), Cells(5, 5)).Value = "2"


End Sub

I expected this to make the values in the range A1:E5 '1' on the first sheet and '2' on the second sheet. I know:

Range(Cells(1, 1), Cells(5, 5)).Value = "1"

works, but I'm not 100% confident the rest.

Max
  • 3
  • 2
  • 1
    You've declared `result` but are trying to use `result1`. That said, I'd just use `.Range("A1:E5")` instead of `Cells` calls within `Range`... – BigBen Apr 30 '19 at 19:59

1 Answers1

0

Replace:

result1.Range(Cells(1, 1), Cells(5, 5)).Value = "1"

with:

Range(result1.Cells(1, 1), result1.Cells(5, 5)).Value = "1"

(same with result2)

WHY:

The Range() function is very versatile in the kind of inputs it will accept. For example:

  1. a string
  2. a set of strings
  3. a set of other "smaller" ranges

It is the last form that requires caution. If you don't qualify the "little" ranges, Excel assumes you mean to use the cells on the Activesheet.

Gary's Student
  • 95,722
  • 10
  • 59
  • 99