CODEWITHSUNDEEP
vbaexcel
santaaimonce
santaaimonce

Reputation: 144

Setting Range Object from a specific sheet

How would you set the value of a Range Object to a specific range from a specific sheet?

I have code right now that navigates and activates the correct sheet and cell, but when I set the value of the range, and copy, it ends up copying a range from Sheet 1 when I want it to take a value from another sheet.

Here is the code I have right now when trying to set the Range.

    Dim NewValuesRange As Range
    Dim ActiveCellRowNumber As Integer
    Sheets(err).Activate
    Set NewValuesRange = Range(Cells(ActiveCellRowNumber - 1, 1), Cells(ActiveCellRowNumber - 1, 18))
    NewValuesRange.Copy
    NewValuesRange.Insert Shift:=xlDown

I've tried setting line four as:

    Set NewValuesRange = Sheets(err).Range(Cells(ActiveCellRowNumber - 1, 1), Cells(ActiveCellRowNumber - 1, 18))

and 

    Set NewValuesRange = Worksheet(sheetname).Range(Cells(ActiveCellRowNumber - 1, 1), Cells(ActiveCellRowNumber - 1, 18))

However, those two give me an 1004 error. Any idea's on how to tackle this?

Thanks!

EDIT: err is an integer already set, activecellrownumber is an integer already set, and sheetname is a string that has been set.

Upvotes: 0

Views: 837

Answers (3)

Rory
Rory

Reputation: 34075

You need to qualify both the Range and Cells calls with a Worksheet object:

Dim NewValuesRange As Range
Dim ActiveCellRowNumber As Integer
With Sheets(err)
Set NewValuesRange = .Range(.Cells(ActiveCellRowNumber - 1, 1), .Cells(ActiveCellRowNumber - 1, 18))
End With
NewValuesRange.Copy
NewValuesRange.Insert Shift:=xlDown

Note the use of both .Range and .Cells rather than just Range and Cells. I would assume your code is in a worksheet code module, in which case any reference to Range or Cells that doesn't specify a sheet explicitly refers to the sheet containing the code, not the active sheet. (i.e. it defaults to Me.Range rather than Application.Range in this case)

Upvotes: 3

Alex P
Alex P

Reputation: 12487

Try something like this:

Sub CopyAndInsert()

Dim NewValuesRange As Range
Dim ActiveCellRowNumber As Integer

    With Worksheets(err)
        Set NewValuesRange = .Range(Cells(ActiveCellRowNumber - 1, 1), Cells(ActiveCellRowNumber - 1, 18))

        NewValuesRange.Copy
        NewValuesRange.Insert Shift:=xlDown
    End With
End Sub

Upvotes: 0

Gary's Student
Gary's Student

Reputation: 96771

You must put some value in ActiveCellRowNumber

Sub fhskadjfhs()
    Dim NewValuesRange As Range
    Dim ActiveCellRowNumber As Integer
    Dim Errr As String

    Errr = "Sheet2"
    Sheets(Errr).Activate
    ActiveCellRowNumber = ActiveCell.Row
    Set NewValuesRange = Range(Cells(ActiveCellRowNumber - 1, 1), Cells(ActiveCellRowNumber - 1, 18))
    NewValuesRange.Copy
    NewValuesRange.Insert Shift:=xlDown
End Sub

Upvotes: 0

Related Questions