×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Copy Method in Excel 97 Doesn't work

Copy Method in Excel 97 Doesn't work

Copy Method in Excel 97 Doesn't work

(OP)
I'm trying to develop a couple of spreadsheet templates for our sales team.  I'm trying to set up a few simple steps that would allow a salesmen to type in his information in one worksheet of a workbook, click a command button that has a macro to copy the enterred information into another sheet of the workbook.  The code works in any version of Excel later than 97 SR-2.  My computer is still running 97, and even if I use the exact code in the example from the Visual Basic Help menu (Code shown below), I get an error message (Shown below also).  Does anyone have any ideas?



Entered Code:

Worksheets("Sheet1").Range("A1:D4").Copy _
    destination:=Worksheets("Sheet2").Range("E5")


Error Message:

Run-time error '1004':
Copy method of Range class failed

RE: Copy Method in Excel 97 Doesn't work

It works in 97SR2 as well.

You could just rewrite the code for a stepwise approach.

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Set ws1 = Sheets("Sheet1")
    Set ws2 = Sheets("Sheet2")

    ws1.Range("A1:D4").Copy
    ws2.Activate
    ws2.Range("E5").Select
    ws2.Paste

or, without declaring objects

    Worksheets("Sheet1").Range("A1:D4").Copy
    Sheets("Sheet2").Activate
    Worksheets("Sheet2").Range("E5").Select
    Worksheets("Sheet2").Paste
    Sheets("Sheet1").Activate

Hope this helps...

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

RE: Copy Method in Excel 97 Doesn't work

(OP)
DSI,
   Thanks for the help.  Do you know the reason that the example code that Excel 97 gives in it's help menu doesn't work when you enter it?  The code from the original post is directly from the help menu, and it works in all later versions, yet it doesn't work in 97.  

RE: Copy Method in Excel 97 Doesn't work

I've had a similar problem when using the goalseek function from a command bar.  I believe the problem is that the command button has the focus when you try and execute your macro.  Since the command button doesn't have a range, VBA returns an error.  You should be able to use the same code with a single addition at the beginning of your code:

Worksheets("Sheet1").Range("A1").Select

I think there's a way to automatically transfer focus from the command button back to the worksheet, but I don't remember how to do it.  I'm sure you can find it if you browse the spreadsheet forum.

Hope this helps!

jproj

RE: Copy Method in Excel 97 Doesn't work

tigerbob:
I tried it using Excel 97 SR2 and did not have any problems. You may want to try removing the (_) and writing it on one line, although that's a long-shot.

jproj:
If you are running the macro from a command button located on a worksheet, simply to into Design Mode and view the Properties for the button. Change the value for TakeFocusOnClick to False. This will eliminate that problem. Another method would be to select a cell in the macro:

Private Sub SomeButton_Click()
    Range("A1").Select
    ...your code here
End Sub

DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close