XL97: PasteSpecial Method Activates Target Sheet (158360)
The information in this article applies to:
- Microsoft Excel 97 for Windows
This article was previously published under Q158360 SYMPTOMS
In Microsoft Excel 97, if you run a Visual Basic for Applications macro
that uses the PasteSpecial method, you may find that the wrong worksheet
becomes activated. This may cause problems if your macro assumes that a
particular sheet is still active.
CAUSE
This problem occurs because, in Microsoft Excel 97, the PasteSpecial method
activates the sheet into which you are pasting information (the "target
sheet"). In earlier versions of Microsoft Excel, the PasteSpecial method
does not activate the target sheet.
Note that this problem occurs only when information is pasted into a sheet
other than that from which it was copied.
WORKAROUNDMicrosoft provides programming examples for illustration only, without warranty either
expressed or implied, including, but not limited to, the implied warranties of
merchantability and/or fitness for a particular purpose. This article assumes
that you are familiar with the programming language being demonstrated and the
tools used to create and debug procedures. Microsoft support professionals can
help explain the functionality of a particular procedure, but they will not
modify these examples to provide added functionality or construct procedures to
meet your specific needs. If you have limited programming experience, you may
want to contact a Microsoft Certified Partner or the Microsoft fee-based
consulting line at (800) 936-5200. For more information about Microsoft Certified
Partners, please visit the following Microsoft Web site:
For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:
If you need your Visual Basic macro to copy information from one sheet,
PasteSpecial the information into another sheet, and keep the original
sheet active, you will need to add two lines of code to the macro. These
lines of code keep track of the original sheet and reactivate it after the
PasteSpecial method is complete.
For example, if your original macro looks like this
Sub CopyAndPasteSpecial1()
Sheets("Sheet1").Range("A1:B5").Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlValues
End Sub
you will need to add two lines to the macro:
Sub CopyAndPasteSpecial2()
'This new line remembers the name of the original sheet.
Set xOriginalSheet = ActiveSheet
Sheets("Sheet1").Range("A1:B5").Copy
Sheets("Sheet2").Range("A1").PasteSpecial xlValues
'This new line activates the original sheet.
xOriginalSheet.Activate
End Sub
The modified code will work in all versions of Microsoft Excel, including
Microsoft Excel 97.
STATUS
Microsoft has confirmed this to be a problem in the Microsoft products
listed at the beginning of this article. This problem no longer occurs in
Microsoft Excel 2000.
Modification Type: | Minor | Last Reviewed: | 10/11/2006 |
---|
Keywords: | kbbug kbpending kbProgramming KB158360 |
---|
|