Copy Method in Excel 97 Doesn't work

[tigerbob]

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

 

[dsi]

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.

 

[tigerbob]

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.

 

[jproj]

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&quot.Range("A1&quot.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

 

[dsi]

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.