Pull data from inactive worksheet
 

Hi, all.

I've perused the threads and found nothing on the following (likely because
it's evident to everyone else!), and would like your help.

I have two worksheets, "Employees" and "Summary", in the same workbook.
When a user selects the Summary sheet and clicks a refresh button, I need to
grab the employee number from the row they had selected on the Employees
sheet. I cannot use ActiveCell, since Summary is now the active sheet.

What am I missing?

Thanks!

Pull data from inactive worksheet
 

Try this...

Dim wks1 As Worksheet
Dim wks2 As Worksheet
Dim varVal1 As Variant

'Suppress screen updating
'Assign worksheets to variables
'Activate the Summary sheet, capture the activecells value
'Activate the Employee sheet and post the captured value
'to the active cell

Set wks1 = ActiveWorkbook.Worksheets("Employee")
Set wks2 = ActiveWorkbook.Worksheets("Summary")

Application.ScreenUpdating = False
wks2.Activate
varVal1 = ActiveCell.Value
wks1.Activate
ActiveCell.Value = varVal1

Set wks1 = Nothing
Set wks2 = Nothing

--
Kevin Backmann


"Pontificateur" wrote:

Hi, all.

I've perused the threads and found nothing on the following (likely because
it's evident to everyone else!), and would like your help.

I have two worksheets, "Employees" and "Summary", in the same workbook.
When a user selects the Summary sheet and clicks a refresh button, I need to
grab the employee number from the row they had selected on the Employees
sheet. I cannot use ActiveCell, since Summary is now the active sheet.

What am I missing?

Thanks!

Pull data from inactive worksheet
 

application.ScreenUpdating = False
worksheets("employees").Activate
set rng = ActiveCell
Worksheets("Summary").Activate
Application.ScreenUpdating = True
' option: display the cell address
'msgbox rng.Address(external:=True)

--
Regards,
Tom Ogilvy

"Pontificateur" wrote in message
...
Hi, all.

I've perused the threads and found nothing on the following (likely
because
it's evident to everyone else!), and would like your help.

I have two worksheets, "Employees" and "Summary", in the same workbook.
When a user selects the Summary sheet and clicks a refresh button, I need
to
grab the employee number from the row they had selected on the Employees
sheet. I cannot use ActiveCell, since Summary is now the active sheet.

What am I missing?

Thanks!

Pull data from inactive worksheet
 

Thanks, Kevin and Tom!

You both pointed out the missing concept for me... namely that the code
should take control and activate the (inactive) worksheet momentarily to get
the cell!

I greatly appreciate your responses!



"Tom Ogilvy" wrote:

application.ScreenUpdating = False
worksheets("employees").Activate
set rng = ActiveCell
Worksheets("Summary").Activate
Application.ScreenUpdating = True
' option: display the cell address
'msgbox rng.Address(external:=True)

--
Regards,
Tom Ogilvy

"Pontificateur" wrote in message
...
Hi, all.

I've perused the threads and found nothing on the following (likely
because
it's evident to everyone else!), and would like your help.

I have two worksheets, "Employees" and "Summary", in the same workbook.
When a user selects the Summary sheet and clicks a refresh button, I need
to
grab the employee number from the row they had selected on the Employees
sheet. I cannot use ActiveCell, since Summary is now the active sheet.

What am I missing?

Thanks!