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
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
Talk To Other Members
RE: Copy Method in Excel 97 Doesn't work
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
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
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
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.