Not thisworkbook, but the other one...

  • Thread starter Thread starter George J
  • Start date Start date
G

George J

When i press CTRL+N with the first workbook active it will
copy rows 4 to 13 which is what i want.
When i press CTRL+N with the second workbook active it
will copy rows 4 to 13 instead of 3 to 35 if the first
workbook is still open.

This seems to be because the macros use thisworkbook
instead of activeworkbook

Closing down the first workbook may create problems as
other users will be using these templates and may have
made changes they do not want saved.

And of course as these files will be made from templates,
the names will always be different.

Can't see any way around this as i cannot activate the
second macro that refers to CTRL+N if the first workbook
is still open.

I can stop the macro firing if thisworkbook is not
activeworkbook, but how do i then get it to change what
thisworkbook is (ie to the 2nd one).

If this makes sense to anyone, please help.
 
George

Post the relevant portions of the code you are using. Where is the code,
e.g. standard module, userform, etc. In which workbook is the code? Can
you just preface everything with ActiveWorkbook so that it always uses the
active one?
 
Both are almost identical. They do the same thing to the
same sheets, but the area that's copied is different.

The code is basically the same for both, just the area
that is being copied is different.

The first workbook's code:
Sub Paste_Relief_Workings()
'
' Paste_Relief_Workings Macro
' Macro recorded 29/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+n
'
Run ("Calculate_Manual")
Application.ScreenUpdating = False


Sheets("Settled RV").Select
Range("A4:P13").Select
Selection.Copy

LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

If LastRow < 14 Then
NextRow = 14
Else
NextRow = Range("A65536").End(xlUp).Offset(5, 0).Row
Range("A" & NextRow).Select
End If

Range("A" & NextRow).Select

ActiveWindow.LargeScroll ToRight:=-1
ActiveSheet.Paste

'<<snipped>>

Application.ScreenUpdating = True

End Sub



The second workbooks code:

Sub Paste_Relief_Workings_Inc()
'
' Paste_Relief_Workings Macro
' Macro recorded 29/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+n
'

Run ("Calculate_Manual")
Application.ScreenUpdating = False


Sheets("Settled RV").Select
Range("A3:q35").Select
Selection.Copy

LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

If LastRow < 14 Then
Nextrow = 14
Else
Nextrow = Range("A65536").End(xlUp).Offset(8, 0).Row
Range("A" & Nextrow).Select
End If

Range("A" & Nextrow).Select

ActiveWindow.LargeScroll ToRight:=-1
ActiveSheet.Paste

<<snipped>>

Application.ScreenUpdating = True

End Sub


It seems ineed a way of changing the value
of "thisworkbook" or getting the macro in the
activeworkbook to fire instead of thisworkbook.

help!
George
 
Forgot to mention, the code is in a standard module.

I guess what i am really after is a way to change
thisworkbook from workbook1 to the activeworkbook -
workbook2.

I have not found anything to change what thisworkbook
refers to.

can anyone help??
George
-----Original Message-----
Both are almost identical. They do the same thing to the
same sheets, but the area that's copied is different.

The code is basically the same for both, just the area
that is being copied is different.

The first workbook's code:
Sub Paste_Relief_Workings()
'
' Paste_Relief_Workings Macro
' Macro recorded 29/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+n
'
Run ("Calculate_Manual")
Application.ScreenUpdating = False


Sheets("Settled RV").Select
Range("A4:P13").Select
Selection.Copy

LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

If LastRow < 14 Then
NextRow = 14
Else
NextRow = Range("A65536").End(xlUp).Offset(5, 0).Row
Range("A" & NextRow).Select
End If

Range("A" & NextRow).Select

ActiveWindow.LargeScroll ToRight:=-1
ActiveSheet.Paste

'<<snipped>>

Application.ScreenUpdating = True

End Sub



The second workbooks code:

Sub Paste_Relief_Workings_Inc()
'
' Paste_Relief_Workings Macro
' Macro recorded 29/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+n
'

Run ("Calculate_Manual")
Application.ScreenUpdating = False


Sheets("Settled RV").Select
Range("A3:q35").Select
Selection.Copy

LastRow = Range("A65536").End(xlUp).Offset(1, 0).Row

If LastRow < 14 Then
Nextrow = 14
Else
Nextrow = Range("A65536").End(xlUp).Offset(8, 0).Row
Range("A" & Nextrow).Select
End If

Range("A" & Nextrow).Select

ActiveWindow.LargeScroll ToRight:=-1
ActiveSheet.Paste

<<snipped>>

Application.ScreenUpdating = True

End Sub


It seems ineed a way of changing the value
of "thisworkbook" or getting the macro in the
activeworkbook to fire instead of thisworkbook.

help!
George
Click to expand...
 
George

An example of an unqualified object reference is:
Sheets("Settled RV").Select
Click to expand...

It's unqualified because you don't specify which workbook. If you have two
workbooks and each of them has a sheet Settled RV, you can run into
problems. VBA uses default qualifications when you have unqualified object
references. The above line is actually interpreted as

ActiveWorkbook.Sheets("Settled RV").Select

That may be what you want, but it's best not to rely on it. You should
fully qualify all of your references unless you have a good reason not to.
If you fully qualify your references, you may run into problems with
Selecting ranges and sheets. For instance, if WB1 is the ActiveWorkbook and
you want to work with a sheet in WB2, then

Workbooks("WB2").Sheets("Settled RV").Select

will fail, because you can't select a sheet on a workbook that's not active.
For this reason, and many others, it's best not to select objects that you
want to work with. Except in rare cases, you can work directly with the
object.

I've re-written the first macro below. Notice how it doesn't Select or
Activate anything. Also note that I qualify my objects, for instance
ThisWorkbook.Sheets("Settled RV")... I know which sheet I'm referencing
here because I've told it explicitly to use the Sheet named Settled RV in
the same workbook that contains the code. With this construct, it won't
matter which workbook is active.

Note that I qualify some references using a With block. The references
within the With block start with a period. This was used to make the code
more readable.

Last thing: I wouldn't rely on the scrolling that you used in your macro.
Instead, find the range to which you want to copy and call it explicitly. I
determined that a large scroll would put me at column T, so I used that.

Sub Paste_Relief_Workings()
'
' Paste_Relief_Workings Macro
' Macro recorded 29/01/2002 by gj
'
' Keyboard Shortcut: Ctrl+n
'

Dim LastRow As Long

Application.ScreenUpdating = False

With ThisWorkbook.Sheets("Settled RV")
LastRow = Application.Max(.Range("A65536").End(xlUp).Offset(5, 0).Row,
14)
.Range("A4:P13").Copy Destination:=.Range("T" & LastRow)
End With

'<<snipped>>

Application.ScreenUpdating = True

End Sub

I hope that helps you. If not, or you need more help, be sure to post back.
 
Thanks for the advice Dick, but still no joy.

Even after amending the macro to what you put, though T
shouldhave been A (my fault - using the macro recorder),in
workbook 1 the code works fine, in workbook 2 the code
adds another entry to workbook 1.

The problem seems to be that as both workbooks are open at
the same time and both will have the same shortcut,
thisworkbook.name will always be workbook1.
Even if workbook2 is the active workbook the macro that
will be fired is from workbook1 and not the active
workbook.
Is there a way to change the value of thisworkbook to
workbook2 without closing workbook1?

Hope this makes sense.
 
George

Even after amending the macro to what you put, though T
shouldhave been A (my fault - using the macro recorder),in
workbook 1 the code works fine, in workbook 2 the code
adds another entry to workbook 1.
Click to expand...

Did you also change the macro in workbook2? If not, then the problems that
existed before still exist.
The problem seems to be that as both workbooks are open at
the same time and both will have the same shortcut,
thisworkbook.name will always be workbook1.
Even if workbook2 is the active workbook the macro that
will be fired is from workbook1 and not the active
workbook.
Click to expand...

Are you saying that both macros exist in Workbook1, but that one acts on WB1
and the other acts on WB2? That's not a problem, but it does affect how you
write your macros.
Is there a way to change the value of thisworkbook to
workbook2 without closing workbook1?
Click to expand...

No. ThisWorkbook will always and forever reference the workbook that
contains the code. The only way to change that is to move the code, but
that would be just silly. I have a feeling that the answer for you will be
to call out the workbooks by name, i.e. don't use ThisWorkbook or
ActiveWorkbook, but rather

With Workbooks("MyWb1").Sheets("Select RV")

If you want to do this right, here's what I recommend: Email both workbooks
to me, let me fix them up. I'll include some comments for you to follow and
once you see it done properly, it will all come clear. If the workbooks
contain sensitive information such that you cannot send them to me, then
post the macro from workbooks2 (the one that's not working correctly,
right?) in its entirety and I'll walk you through how to change it.
 
The issue was that the macro in WB1 and WB2 used the same shortcut key.
Whichever workbook was opened first got the shortcut key assignment and if
the other WB was active, the wrong range was used.

The solution is to either use different shortcut keys for each workbook's
macro or to use identical code in each workbook and identify the range based
on which workbook was active.
 
Many thanks to Dick for that stroke of genious.

And for those of you wondering how to identify the range
and have 2 different workbook running with the same
shortcut, but different macros - this is what he said.


Keep your macros the same as they are now, but use a
different macro for
Control-n. Put this sub in each of your workbooks

Sub CallPasteRelief()

Application.Run ActiveWorkbook.Name & "!
Paste_Relief_Workings1"

End Sub

Go to Tools - Macros - Macros (Alt-F8) and use the Options
button to remove the shortcut from Paste_Relief_Workings1
and add it to CallPasteRelief.
With this, it won't matter which workbook gets the Control-
n, because the macro controlled by that shortcut will call
the proper macro in the ActiveWorkbook.


This meant that i could have 2 workbooks with a macro of
the same name doing different things.

Dick to the rescue yet again. :)

thanks again
George
 
Back
Top