Two Open Workbooks
 

Hi,

I'm not sure I'm doing this right. I used this to open the other workbook
in another procedure and it worked fine. Now I'm getting an out of range
message. Also, am I identifying the two different workbook correctly in
determining the LusedRow?

Dim wbkCopyFrom As Workbook

Set wbkCopyFrom = Workbooks("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''"))

Application.ScreenUpdating = False

'get the last tenant's row in the From workbook
FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
'get the last tenant's row in the New workbook
NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row

--
Thanks for your help.
Karen53

Two Open Workbooks
 

Close...

Dim wbkCopyFrom As Workbook
on error resume next
Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I'm not sure I'm doing this right. I used this to open the other workbook
in another procedure and it worked fine. Now I'm getting an out of range
message. Also, am I identifying the two different workbook correctly in
determining the LusedRow?

Dim wbkCopyFrom As Workbook

Set wbkCopyFrom = Workbooks("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''"))

Application.ScreenUpdating = False

'get the last tenant's row in the From workbook
FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
'get the last tenant's row in the New workbook
NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row

--
Thanks for your help.
Karen53

Two Open Workbooks
 

Thanks, Jim,

I'll try it out tomorrow at work.

What does the On Error GoTo 0 do? I've never seen a 0 used before. I would
need a 0: before End Sub, right?

--
Thanks for your help.
Karen53


"Jim Thomlinson" wrote:

Close...

Dim wbkCopyFrom As Workbook
on error resume next
Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I'm not sure I'm doing this right. I used this to open the other workbook
in another procedure and it worked fine. Now I'm getting an out of range
message. Also, am I identifying the two different workbook correctly in
determining the LusedRow?

Dim wbkCopyFrom As Workbook

Set wbkCopyFrom = Workbooks("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''"))

Application.ScreenUpdating = False

'get the last tenant's row in the From workbook
FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
'get the last tenant's row in the New workbook
NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row

--
Thanks for your help.
Karen53

Two Open Workbooks
 

The 0 is not a label in this special case.

You're just telling excel to handle the next error it finds.

Karen53 wrote:

Thanks, Jim,

I'll try it out tomorrow at work.

What does the On Error GoTo 0 do? I've never seen a 0 used before. I would
need a 0: before End Sub, right?

--
Thanks for your help.
Karen53

"Jim Thomlinson" wrote:

Close...

Dim wbkCopyFrom As Workbook
on error resume next
Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I'm not sure I'm doing this right. I used this to open the other workbook
in another procedure and it worked fine. Now I'm getting an out of range
message. Also, am I identifying the two different workbook correctly in
determining the LusedRow?

Dim wbkCopyFrom As Workbook

Set wbkCopyFrom = Workbooks("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''"))

Application.ScreenUpdating = False

'get the last tenant's row in the From workbook
FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
'get the last tenant's row in the New workbook
NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row

--
Thanks for your help.
Karen53

--

Dave Peterson

Two Open Workbooks
 

Dave;
is that the same as Err.Clear

"Dave Peterson" wrote:

The 0 is not a label in this special case.

You're just telling excel to handle the next error it finds.

Karen53 wrote:

Thanks, Jim,

I'll try it out tomorrow at work.

What does the On Error GoTo 0 do? I've never seen a 0 used before. I would
need a 0: before End Sub, right?

--
Thanks for your help.
Karen53

"Jim Thomlinson" wrote:

Close...

Dim wbkCopyFrom As Workbook
on error resume next
Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I'm not sure I'm doing this right. I used this to open the other workbook
in another procedure and it worked fine. Now I'm getting an out of range
message. Also, am I identifying the two different workbook correctly in
determining the LusedRow?

Dim wbkCopyFrom As Workbook

Set wbkCopyFrom = Workbooks("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''"))

Application.ScreenUpdating = False

'get the last tenant's row in the From workbook
FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
'get the last tenant's row in the New workbook
NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row

--
Thanks for your help.
Karen53

--

Dave Peterson

Two Open Workbooks
 

No, On Error GoTo 0 turns off the current error handler... in this case, the
one that was turned on with On Error Resume Next.

Rick


"DownThePaint" wrote in message
...
Dave;
is that the same as Err.Clear

"Dave Peterson" wrote:

The 0 is not a label in this special case.

You're just telling excel to handle the next error it finds.

Karen53 wrote:

Thanks, Jim,

I'll try it out tomorrow at work.

What does the On Error GoTo 0 do? I've never seen a 0 used before. I
would
need a 0: before End Sub, right?

--
Thanks for your help.
Karen53

"Jim Thomlinson" wrote:

Close...

Dim wbkCopyFrom As Workbook
on error resume next
Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I'm not sure I'm doing this right. I used this to open the other
workbook
in another procedure and it worked fine. Now I'm getting an out of
range
message. Also, am I identifying the two different workbook
correctly in
determining the LusedRow?

Dim wbkCopyFrom As Workbook

Set wbkCopyFrom = Workbooks("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'",
"''"))

Application.ScreenUpdating = False

'get the last tenant's row in the From workbook
FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
'get the last tenant's row in the New workbook
NewLusedRow = MainPagepg.Cells(Rows.Count,
"F").End(xlUp).Row

--
Thanks for your help.
Karen53

--

Dave Peterson

Two Open Workbooks
 

Nope.

Err.clear
clears the current error flag.

On Error Resume Next
MsgBox 1 / 0
MsgBox Err.Number
Err.Clear
MsgBox Err.Number

On Error GoTo 0
MsgBox 1 / 0
MsgBox Err.Number
Err.Clear
MsgBox Err.Number

The top group will "work". The bottom group won't get by the first msgbox.

Chip Pearson explains error handling much better he
http://cpearson.com/excel/ErrorHandling.htm

DownThePaint wrote:

Dave;
is that the same as Err.Clear

"Dave Peterson" wrote:

The 0 is not a label in this special case.

You're just telling excel to handle the next error it finds.

Karen53 wrote:

Thanks, Jim,

I'll try it out tomorrow at work.

What does the On Error GoTo 0 do? I've never seen a 0 used before. I would
need a 0: before End Sub, right?

--
Thanks for your help.
Karen53

"Jim Thomlinson" wrote:

Close...

Dim wbkCopyFrom As Workbook
on error resume next
Set wbkCopyFrom = Workbooks("test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo 0
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

--
HTH...

Jim Thomlinson


"Karen53" wrote:

Hi,

I'm not sure I'm doing this right. I used this to open the other workbook
in another procedure and it worked fine. Now I'm getting an out of range
message. Also, am I identifying the two different workbook correctly in
determining the LusedRow?

Dim wbkCopyFrom As Workbook

Set wbkCopyFrom = Workbooks("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
If wbkCopyFrom Is Nothing Then
Set wbkCopyFrom = Workbooks.Open("C:\Documents and
Settings\Eileen\Desktop\New
Workbooks\test.xls")
On Error GoTo Done
If wbkCopyFrom Is Nothing Then
MsgBox "Cannot find originating file"
Else

Set ws = wbkCopyFrom.Sheets(Replace(MainPagepg.Name, "'", "''"))

Application.ScreenUpdating = False

'get the last tenant's row in the From workbook
FromLusedRow = ws.Cells(Rows.Count, "F").End(xlUp).Row
'get the last tenant's row in the New workbook
NewLusedRow = MainPagepg.Cells(Rows.Count, "F").End(xlUp).Row

--
Thanks for your help.
Karen53

--

Dave Peterson


--

Dave Peterson