asked on

Problem to select

Hi,
I get

due to Select line (last 4th line) below. Why?

Windows(Window0).Activate
Dim RowID1 As Integer
RowID1 = 2
Do While True
If Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 2).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 4).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 5).Value) = "" And Trim(Worksheets("Utilization-PO-List").Cells(RowID1, 6).Value) = "" Then
Exit Do
End If

RowID1 = RowID1 + 1
Loop

If RowID1 > 1 Then
Worksheets("Utilization-PO-List").Unprotect Password:="hkg1317"
Worksheets("Utilization-PO-List").Cells(RowID1, 1).Select

Windows(Current_Book).Activate
Worksheets("Utilization-PO-List").Unprotect Password:="hkg1317"
Worksheets("Utilization-PO-List").Range(Range0).Copy
...

Wayne Taylor (webtubbs)

What is the value of RowID1 when you get the error? You can check easily by clicking "Debug" in the error message, then placing the cursor over the variable.

crystal (strive4peace) - Microsoft MVP, Access

my guess would be that is is not the active sheet -- that must be done first

Peter Chan

ASKER

Wayne,
The value of RowID1 is 5, when the error happens.

Peter Chan

ASKER

Crystal,
Any advice to adjust the codes in above?

Subodh Tiwari (Neeraj)

When that error occurs, that means the correct workbook is not your active workbook from which you want to select a range.
To check this, use Debug.Print to see the state of the activeworkbook in the Immediate Window.

Run the code by adding following extra lines and when you get and error, debug and press Ctrl+G to open the immediate window and see if you are on the correct workbook and the Range0 is a valid range you are trying to select.

Windows(Current_Book).Activate

Debug.Print ActiveWorkbook.Name    'To see which workbook is active at present
Debug.Print Range(Range0).Address  'To know if Range0 is a valid range

Worksheets("Utilization-PO-List").Unprotect Password:="hkg1317"
Worksheets("Utilization-PO-List").Range(Range0).Copy

Open in new window

Peter Chan

ASKER

But the error is due to this line

Worksheets("Utilization-PO-List").Cells(RowID1, 1).Select

Open in new window

Peter Chan

ASKER

I put this

Debug.Print ActiveWorkbook.Name

Open in new window

to the line on which the error happens, and the Workbook is the current one which I expect

Peter Chan

ASKER

I put this

Debug.Print ActiveWorkbook.Name

Open in new window

before the line on which the error happens, and the Workbook is the current one which I expect

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wayne Taylor (webtubbs)

Change this...

Worksheets("Utilization-PO-List").Unprotect Password:="hkg1317"
Worksheets("Utilization-PO-List").Cells(RowID1, 1).Select

Open in new window

...to this...

With Worksheets("Utilization-PO-List")
    .Select
    .Unprotect Password:="hkg1317"
    .Cells(RowID1, 1).Select
End With

Open in new window

Subodh Tiwari (Neeraj)

And if this is the case, try to select the sheet first before selecting a cell on that sheet.

Worksheets("Utilization-PO-List").Select
Cells(RowID1, 1).Select