help with copying worksheet (excel 97 vs excel 2000)
 

Hi

It got the following code

Code:
--------------------
Sub viewtables()
'
Sheets("menu").Range("h3").Value = 1
Sheets("Tables(MAIN)").Copy after:=Sheets(Sheets.Count)
Sheets("Tables(MAIN) (2)").Select
Sheets("Tables(MAIN) (2)").Name = "Tables"
findshapes
Range("d1").Value = ""
Range("A1").Select
Sheets("menu").Range("h3").Value = ""
--------------------


If works perfect on my machine which has excel 2000 however if I go to
a machien with 97 on it comes up with the following error Runtime Error
1004 copy method of worksheet class failed. And breaks at the
"sheets.copy after " part.

Any idea why as I have recorded a macro on excel 97 and it come out
with exactly the same lines.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=545021

help with copying worksheet (excel 97 vs excel 2000)
 

Hi
Try inserting
Application.CutCopymode = False

before the copy. This clears the clipboard (I think) and I remember it
fixing something similar for me.

regards
Paul

help with copying worksheet (excel 97 vs excel 2000)
 

If you are running your code from an ActiveX CommandButton in XL97, change
its TakeFocusOnClick property from True to False.

Regards,
Peter T

"funkymonkUK"
wrote in message
...

Hi

It got the following code

Code:
--------------------
Sub viewtables()
'
Sheets("menu").Range("h3").Value = 1
Sheets("Tables(MAIN)").Copy after:=Sheets(Sheets.Count)
Sheets("Tables(MAIN) (2)").Select
Sheets("Tables(MAIN) (2)").Name = "Tables"
findshapes
Range("d1").Value = ""
Range("A1").Select
Sheets("menu").Range("h3").Value = ""
--------------------


If works perfect on my machine which has excel 2000 however if I go to
a machien with 97 on it comes up with the following error Runtime Error
1004 copy method of worksheet class failed. And breaks at the
"sheets.copy after " part.

Any idea why as I have recorded a macro on excel 97 and it come out
with exactly the same lines.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile:
http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=545021

help with copying worksheet (excel 97 vs excel 2000)
 

One other thought - is the workbook protected for structure?

--
Regards,
Tom Ogilvy


"funkymonkUK" wrote:


Hi

It got the following code

Code:
--------------------
Sub viewtables()
'
Sheets("menu").Range("h3").Value = 1
Sheets("Tables(MAIN)").Copy after:=Sheets(Sheets.Count)
Sheets("Tables(MAIN) (2)").Select
Sheets("Tables(MAIN) (2)").Name = "Tables"
findshapes
Range("d1").Value = ""
Range("A1").Select
Sheets("menu").Range("h3").Value = ""
--------------------


If works perfect on my machine which has excel 2000 however if I go to
a machien with 97 on it comes up with the following error Runtime Error
1004 copy method of worksheet class failed. And breaks at the
"sheets.copy after " part.

Any idea why as I have recorded a macro on excel 97 and it come out
with exactly the same lines.


--
funkymonkUK
------------------------------------------------------------------------
funkymonkUK's Profile: http://www.excelforum.com/member.php...o&userid=18135
View this thread: http://www.excelforum.com/showthread...hreadid=545021