Method 'Range' of object '_Worksheet' failed referencing another worksheet

Question

Here is my Code:

Sub showUnique()

Dim VAriable As String
Dim irange As Range
Dim car As Integer
Dim Source As Worksheet
Dim Target As Worksheet
Dim inew As Range

Set Source = ActiveSheet

Range("f2").Activate 'starting point

Do
    If ActiveCell.Value <> ActiveCell.Offset(1, 0).Value Then
        VAriable = ActiveCell.Value
        Set irange = Range("f1:f1000")
    car = -Application.CountIf(irange, VAriable) + 1
  Set inew = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(car, 0)).EntireRow

'inew.Select

Set Target = Worksheets.Add(after:=Sheets(Sheets.count)) ' after last sheet
Target.Name = VAriable
' 'header row
Source.Range("a1:h1").Copy Target.Range("a1")
'handle Vehicle sales
' Set Target = Worksheets.Add(after:=Source) 'after active shett

' 'copy data
Source.Range(inew).Copy Target.Range("a2")
Target.Range("a1").CurrentRegion.Columns.AutoFit
writeKPI
    End If
    Sheets("Sales").Activate
    ActiveCell.Offset(1, 0).Activate
Loop Until IsEmpty(ActiveCell)

End Sub

Getting the error "Method 'Range' of object '_Worksheet' failed" on the line of code

Source.Range(inew).Copy Target.Range("a2")

I am trying to reference a range on a different worksheet and copy that data to a new worksheet

score 0 · Answer 1 · edited May 23 '17 at 10:31

Your inew variable is a Range, so you should just use

inew.Copy Target.Range("a2")

You should, however, go through your code and:

get rid of the Activates (see How to avoid using Select in Excel VBA macros),

fully qualify your references to Ranges. For example, I think

Set inew = Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(car, 0)).EntireRow

is probably meant to be

Set inew = Sheets("Sales").Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(car, 0)).EntireRow

Method 'Range' of object '_Worksheet' failed referencing another worksheet

1 Answers1