How to call cells in a sheet

[jtb1492]

Can somebody explain Sheets.Activate and Sheets.Select to me? WHen to use, what they do, etc?

And when can I use Cells(1,1) and when do I have to use Sheets(1).Cells(1,1)? This is confusing me.

I wrote this code, and it worked...
Sub doit()
Sheets(1).Activate
If Cells(1, 1) = 1 Then
Sheets(2).Cells(12, 12) = Sheets(1).Cells(9, 9)
End If
End Sub

The I added another if and it didn't work...
Sub doit()
Sheets(1).Activate
If Cells(1, 1) = 1 Then
If Cells(3, 3) = 2 Then
Sheets(2).Cells(12, 12) = Sheets(1).Cells(9, 9)
End If
End If
End Sub

But then I added a Sheets(1). to ONE of the if statements and it worked (it doesn't seem to matter which IF I add it to)
Sub doit()
Sheets(1).Activate
If Sheets(1).Cells(1, 1) = 1 Then
If Cells(3, 3) = 2 Then
Sheets(2).Cells(12, 12) = Sheets(1).Cells(9, 9)
End If
End If
End Sub



Thanks!

 

[kpetursson]

see this FAQ on the use of .activate

http://www.tek-tips.com/faqs.cfm?fid=4105

Kevin Petursson

 

[SkipVought]

Hi,

I'm the guy who wrote the FAQ that Kevin referred you to.

Look at help on Cells and Range. Cells is a range property. Each range object has a Worksheet as a parent object. If no Worksheet is explicitly specified, the ActiveSheet is assumed.

So, in your example above...

Sub doit()
   Sheets(1).Activate
   If Sheets(1).Cells(1, 1) = 1 Then
      Sheets(2).Cells(12, 12) = Sheets(1).Cells(9, 9)
   End If
End Sub

Using the With...End With construct makes for more efficient code as well...

Sub doit()
   With Sheets(1)
      .Activate
      If .Cells(1, 1) = 1 Then
         Sheets(2).Cells(12, 12) = .Cells(9, 9)
      End If
   End With
End Sub

BUT....
it's not really necessary to ACTIVATE Sheet(1), unless, that's where you want to be when the procedure is over...

Sub doit()
   With Sheets(1)
      If .Cells(1, 1) = 1 Then
         Sheets(2).Cells(12, 12) = .Cells(9, 9)
      End If
   End With
End Sub

Hope this helps.

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}