Reference Activecell in another workbook tab

[senators40]

Hi,

I would like to determine if the activecell in another workbook is blank and do a procedure if it is.

I can do it if I know which cell I am referencing but I would like to determine the activecell as the number of rows may change.

Below is what I can do if I want to find a specific cell in excel.

I am in Sheet2 tab

These are 2 types of procedures that worked but it references specific cells

If Sheets("Sheet1").Range("A2") <> "" Then

or

If Sheets("Sheet1").Cells(2, 1).Value <> "" Then

so if my activecell is in cell A2 on Sheet1 then how do I reference the activecell in that sheet.

Thanks in advance

Jeff

 

[SkipVought]

Hi,

You really ought not to use Active ANYTHING, because depending on a selection is iffy.

Reference the cell in the other sheet explicitly. You ought to know exactly where the reference is via your row & column variables.

If thats just the way you want to do it, then Activate the other sheet.



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Technically, there is only 1 activecell and that is the one in the current active workbook

As Skip says, to test the "active" cell in another workbook, make that workbook active and check it, then return to your main workbook - if that is the one that holds your code, then you can always get back to it by using "Thisworkbook"


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[senators40]

Thanks for the input. I was able to figure out how to reference a cell in the other tab by using this code.


Sheets("Sheet1").Select
Sheet1Row = ActiveCell.Row
Sheet1Column = ActiveCell.Column

Sheets("Sheet2").Select


If Sheets("Sheet1").Cells(Sheet1Row, Sheet1Column).Value = "" Then

ActiveCell.Value = "Empty"

Else

ActiveCell.Value = "Not Empty"


End If

I have to do it this way because I am not always sure the end row.

I also wanted to compare the information in one tab with another tab (ie if they were equal), but since I can reference the cell in the non active tab then I can make the If then statement more sophisticated.

Thanks for your work

Jeff

 

[xlbo]

If you want to find the last row of data in a specified sheet, you don't need to activate anything...

Dim lRow as long

lrow = sheets("Sheet1").cells(65536,1).end(xlup).row

msgbox "Range: " & sheets("Sheet1").range("A" & lRow).address & " is the last row of data in sheet 1"


That will work for column A - for a different column, amend the bolded characters to tbe appropriate column

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question