Help with referencing a specific sheet

[WinterCoast]

I have the following code assigned to a list box. The code works perfectly when the sheet 'Employees' is the active sheet. However, when another sheet is the active sheet, the User Form pulls all of its information from the active sheet instead of 'Employees'. I don't understand why this is happening since I have a With statement around the entire macro specifying the correct worksheet.

Please Login or Register  to view this content.

What am I doing wrong?

Scott

[Leith Ross]

Hello Scott,

nesting With statements can be misleading. You need to preface the With Range statement with Worksheets("Employees"). Once the compiler sees a with it uses that objects properties and events. Since you state With Range, the default system object is the ActiveWorksheet.

Code Change:
With Worksheets("Employees").Range(EmpFound.Address)

Sincerely,
Leith Ross

[WinterCoast]

Thanks so much. That works perfectly.

However, using the same logic, I still cannot fix the following code:

Please Login or Register  to view this content.

It pastes the information from the userform to the active sheet, and not employees. This is not a nested With statement, so I am unsure why it is ignoring the With statement which specifically points to the employees worksheet.

Thanks,
Scott

[Leith Ross]

Hello Scott,

You left out the period before Cells. See the below...

You Wrote:
With Worksheets("Employees")
FinalRow = Cells(Rows.Count, "A").End(xlUp).Row

Should Be:
With Worksheets("Employees")
FinalRow = .Cells(Rows.Count, "A").End(xlUp).Row

The period indicates that the property or method belongs to the object specified by the With statement. Otherwise VBA will use the default object for Cell, the ActiveWorksheet.

Sincerely,
Leith Ross

[WinterCoast]

I knew it had to be something small. Thank you!