Copy cell values with 'if' statement throws error

**Mayweed** · 02-19-2011, 04:05 PM

I'm trying to copy cell values from the next sheet to the currently active sheet but the 'if' condition throws the following error.
Any idea what I'm missing or how to define the variable to eliminate the error?
Perhaps there is a better way to do this, any help would be appreciated.
Run-time error 91
Object or with block variable not set

If ActiveSheet.Next.Range("B2") = "Name:" Then
ActiveSheet.Cells(2, 3) = ActiveSheet.Next.Range("C2")
ActiveSheet.Cells(3, 3) = ActiveSheet.Next.Range("C3")
ActiveSheet.Cells(4, 3) = ActiveSheet.Next.Range("C4")
ActiveSheet.Cells(5, 3) = ActiveSheet.Next.Range("C5")
ActiveSheet.Cells(5, 6) = ActiveSheet.Next.Range("F5")
End If

**mrice** · 02-19-2011, 04:44 PM

Try

If Sheets(ActiveSheet.index +1).range("B2") = "Name:" Then

**Mayweed** · 02-19-2011, 05:01 PM

Thanks for that but it does the same thing if I run the code on the last sheet.
What I'm trying to do is check for the presence of a sheet after this one, if this is the last sheet... do nothing but if there is a sheet after this one... copy the cells.

**watersev** · 02-19-2011, 05:59 PM

try to add another condition to avoid going beyond sheets number:

...
If ActiveSheet.Index < ActiveWorkbook.Sheets.Count Then
    If Sheets(ActiveSheet.Index + 1).Range("B2") = "Name:" Then
....

**Mayweed** · 02-19-2011, 10:27 PM

I used watersev's code to check the sheet status and it works fine.
Thanks to both of you,

Copy cell values with 'if' statement throws error

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Copy cell values with 'if' statement throws error

Re: Copy cell values with 'if' statement throws error

Re: Copy cell values with 'if' statement throws error

Re: Copy cell values with 'if' statement throws error

Re: Copy cell values with 'if' statement throws error

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions