Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 10th 06, 10:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 296
Default Copy PasteSpecial not working

I've just written a new Excel application which works fine when run on
my local PC, and also when I save a copy on a central network server,
and run it over the network.

However when another user who's testing it, runs it from the same
network server, or on his local PC, the code trips out at the
following line of code:

Range("A1").PasteSpecial (xlPasteValues)

this follows an earlier line where I have copied a named range, i.e.

Range("template").Copy


As far as I can tell we both have the same version of Excel, viz:
2002 SP3.

What's more puzzling is that there are several other applications on
the server with similar Copy Pastespecial instructions, which work
perfectly as expected.

Can anyone suggest what might be causing this problem?

Usual TIA



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________

  #2   Report Post  
Old August 10th 06, 11:08 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 14
Default Copy PasteSpecial not working

Richard

Maybe a memory issue. Do you have less RAM on one computer? Maybe try
breaking the named range into two parts and try the paste special with a
smaller range.

Dave

"Richard Buttrey" wrote in
message ...
I've just written a new Excel application which works fine when run on
my local PC, and also when I save a copy on a central network server,
and run it over the network.

However when another user who's testing it, runs it from the same
network server, or on his local PC, the code trips out at the
following line of code:

Range("A1").PasteSpecial (xlPasteValues)

this follows an earlier line where I have copied a named range, i.e.

Range("template").Copy


As far as I can tell we both have the same version of Excel, viz:
2002 SP3.

What's more puzzling is that there are several other applications on
the server with similar Copy Pastespecial instructions, which work
perfectly as expected.

Can anyone suggest what might be causing this problem?

Usual TIA



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________



  #3   Report Post  
Old August 10th 06, 11:38 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 296
Default Copy PasteSpecial not working

Dave,

Thanks for the prompt response.

It doesn't appear to be a memory problem. I've temporarily changed the
copied range to be a single cell and it's still falling over at the
same PasteSpecial Values code.

ISTR that a few years ago I had a similar problem with a PasteSpecial
Column widths, and the solution was to use a constant. Something like
PasteSpecial:=8 - or similar.

Do you happen to knwo fo there's a similar constant for the Values
option?

Rgds



On Thu, 10 Aug 2006 16:08:45 -0600, "Dave"
wrote:

Richard

Maybe a memory issue. Do you have less RAM on one computer? Maybe try
breaking the named range into two parts and try the paste special with a
smaller range.

Dave

"Richard Buttrey" wrote in
message ...
I've just written a new Excel application which works fine when run on
my local PC, and also when I save a copy on a central network server,
and run it over the network.

However when another user who's testing it, runs it from the same
network server, or on his local PC, the code trips out at the
following line of code:

Range("A1").PasteSpecial (xlPasteValues)

this follows an earlier line where I have copied a named range, i.e.

Range("template").Copy


As far as I can tell we both have the same version of Excel, viz:
2002 SP3.

What's more puzzling is that there are several other applications on
the server with similar Copy Pastespecial instructions, which work
perfectly as expected.

Can anyone suggest what might be causing this problem?

Usual TIA



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
  #4   Report Post  
Old August 11th 06, 12:23 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default Copy PasteSpecial not working

Richard,

Have you checked for MISSING references in the VBIDE (ToolsReferences)?
Clear down any you find.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard Buttrey" wrote in
message ...
Dave,

Thanks for the prompt response.

It doesn't appear to be a memory problem. I've temporarily changed the
copied range to be a single cell and it's still falling over at the
same PasteSpecial Values code.

ISTR that a few years ago I had a similar problem with a PasteSpecial
Column widths, and the solution was to use a constant. Something like
PasteSpecial:=8 - or similar.

Do you happen to knwo fo there's a similar constant for the Values
option?

Rgds



On Thu, 10 Aug 2006 16:08:45 -0600, "Dave"
wrote:

Richard

Maybe a memory issue. Do you have less RAM on one computer? Maybe try
breaking the named range into two parts and try the paste special with a
smaller range.

Dave

"Richard Buttrey" wrote in
message ...
I've just written a new Excel application which works fine when run on
my local PC, and also when I save a copy on a central network server,
and run it over the network.

However when another user who's testing it, runs it from the same
network server, or on his local PC, the code trips out at the
following line of code:

Range("A1").PasteSpecial (xlPasteValues)

this follows an earlier line where I have copied a named range, i.e.

Range("template").Copy


As far as I can tell we both have the same version of Excel, viz:
2002 SP3.

What's more puzzling is that there are several other applications on
the server with similar Copy Pastespecial instructions, which work
perfectly as expected.

Can anyone suggest what might be causing this problem?

Usual TIA



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________



  #5   Report Post  
Old August 11th 06, 09:51 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 296
Default Copy PasteSpecial not working

On Fri, 11 Aug 2006 00:23:48 +0100, "Bob Phillips"
wrote:

Richard,

Have you checked for MISSING references in the VBIDE (ToolsReferences)?
Clear down any you find.


Thanks Bob,

Yes I had checked for those, and I've just double checked.

What's weird about this problem is that I can open another workbook
over our network, which has exactly the same copy / paste special
code, and it's fine, it will output and save the appropriate range in
another workbook.

I've tried both forms of pastespecial i.e.

Range("A1").PasteSpecial Paste:=xlValues
Range("A1").PasteSpecial (xlPasteValues)

and both give the same error message in the new workbook.

Regards,


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________


  #6   Report Post  
Old August 11th 06, 10:09 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 296
Default Copy PasteSpecial not working

On Thu, 10 Aug 2006 16:08:45 -0600, "Dave"
wrote:

Richard

Maybe a memory issue. Do you have less RAM on one computer? Maybe try
breaking the named range into two parts and try the paste special with a
smaller range.

Dave


Dave,

You may be on to something.

Although as I mentioned to Bob just now I have another workbook with
the same code which works fine, I've just noticed that when I closed
the new network copy of the workbook, I got a message 'Out of memory'
- although I'd not seen that on other occasions.

Looks like I'll have to get our IT network gurus to investigate.

Regards



__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
  #7   Report Post  
Old August 11th 06, 01:14 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 296
Default Copy PasteSpecial not working

On Fri, 11 Aug 2006 00:23:48 +0100, "Bob Phillips"
wrote:

Richard,

Have you checked for MISSING references in the VBIDE (ToolsReferences)?
Clear down any you find.



Bob,

I've traced the cause to the following sub-section of code

If TempWb.Sheets.Count < 1 Then 'Deletes excess sheets if more than
one

For y = 1 To TempWb.Sheets.Count - 1
Worksheets(y).Delete
Next
End If

The Excel default for the number of sheets when a new workbook is
added, seems to be 3. I have this code mainly for presentation
purposes. I want to control the number of sheets added and don't want
two superfluous sheets in place when the macro has completed.

This code comes immediately after the Workbooks.Add and before the
PasteSpecial command.

When I comment it out, everything seems to work OK.

Any ideas why this may be causing problems?


In case it's of any help, the complete code up until the bit that
falls over is below
The mylist variable I'm passing is a range


Sub OutputFiles(mylist)
Dim x As Integer, y As Integer, z As Integer
Dim iRowNo As Integer
Dim First As Integer


Application.ScreenUpdating = False

Set MyWb = ActiveWorkbook
stShServRows =
MyWb.Worksheets("OHDTemplate").Range("shserv_rows" ).Address
Folder = Range("folder")
Application.DisplayAlerts = False
For x = 0 To mylist.Rows.Count - 1
If Menu.ListBox1.Selected(x) = True Then
Range("jobno") = mylist.Cells(x + 1, 1)
Filename = "OHD_" & Range("Jobno") & "_" &
Range("Periodno") & "_" & Range("yearno")
Range("template").Copy
stShName = Range("jobno")
stOHDTemplate = stShName
Workbooks.Add: Set TempWb = ActiveWorkbook
'ActiveSheet.Name = stShName

If TempWb.Sheets.Count < 1 Then 'Deletes excess sheets
if more than one
For y = 1 To TempWb.Sheets.Count - 1
Worksheets(y).Delete
Next
End If
ActiveSheet.Name = stShName

Range("A1").PasteSpecial (xlPasteValues):
Range("A1").PasteSpecial (xlPasteFormats)
Range("A1").PasteSpecial (xlPasteColumnWidths)

.....................
Regards
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
  #8   Report Post  
Old August 11th 06, 02:36 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default Copy PasteSpecial not working

Richard,

It is the delete that is clearing the clipboard, making the pastespecial
fail.

When you add a workbook, the number of sheets is governed by the setting in
ToolsOptionsGeneral. You can circumvent this by telling VBA what type of
workbook to create, such as this

Workbooks.Add template:=xlWBATWorksheet

which always creates a single sheet workbook. It does name it Sheet1, 2 etc
instead of Book1, 2 , etc.

Another way is to set the SheetsInNewWorkbook property, like so

Application.SheetsInNewWorkbook = 1
Workbooks.Add

The problem with this is that it changes the application, so all new
workbooks now have 1 sheet, so probably best to save the value and
re-instate it

cSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = cSheets


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard Buttrey" wrote in
message ...
On Fri, 11 Aug 2006 00:23:48 +0100, "Bob Phillips"
wrote:

Richard,

Have you checked for MISSING references in the VBIDE (ToolsReferences)?
Clear down any you find.



Bob,

I've traced the cause to the following sub-section of code

If TempWb.Sheets.Count < 1 Then 'Deletes excess sheets if more than
one

For y = 1 To TempWb.Sheets.Count - 1
Worksheets(y).Delete
Next
End If

The Excel default for the number of sheets when a new workbook is
added, seems to be 3. I have this code mainly for presentation
purposes. I want to control the number of sheets added and don't want
two superfluous sheets in place when the macro has completed.

This code comes immediately after the Workbooks.Add and before the
PasteSpecial command.

When I comment it out, everything seems to work OK.

Any ideas why this may be causing problems?


In case it's of any help, the complete code up until the bit that
falls over is below
The mylist variable I'm passing is a range


Sub OutputFiles(mylist)
Dim x As Integer, y As Integer, z As Integer
Dim iRowNo As Integer
Dim First As Integer


Application.ScreenUpdating = False

Set MyWb = ActiveWorkbook
stShServRows =
MyWb.Worksheets("OHDTemplate").Range("shserv_rows" ).Address
Folder = Range("folder")
Application.DisplayAlerts = False
For x = 0 To mylist.Rows.Count - 1
If Menu.ListBox1.Selected(x) = True Then
Range("jobno") = mylist.Cells(x + 1, 1)
Filename = "OHD_" & Range("Jobno") & "_" &
Range("Periodno") & "_" & Range("yearno")
Range("template").Copy
stShName = Range("jobno")
stOHDTemplate = stShName
Workbooks.Add: Set TempWb = ActiveWorkbook
'ActiveSheet.Name = stShName

If TempWb.Sheets.Count < 1 Then 'Deletes excess sheets
if more than one
For y = 1 To TempWb.Sheets.Count - 1
Worksheets(y).Delete
Next
End If
ActiveSheet.Name = stShName

Range("A1").PasteSpecial (xlPasteValues):
Range("A1").PasteSpecial (xlPasteFormats)
Range("A1").PasteSpecial (xlPasteColumnWidths)

....................
Regards
__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________



  #9   Report Post  
Old August 11th 06, 03:31 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 296
Default Copy PasteSpecial not working

Excellent,

Thanks Bob.

I think I'll capture the value for the number of new sheets, and then
reinstate it when the macro finishes as you suggest.

Rgds


On Fri, 11 Aug 2006 14:36:10 +0100, "Bob Phillips"
wrote:

Richard,

It is the delete that is clearing the clipboard, making the pastespecial
fail.

When you add a workbook, the number of sheets is governed by the setting in
ToolsOptionsGeneral. You can circumvent this by telling VBA what type of
workbook to create, such as this

Workbooks.Add template:=xlWBATWorksheet

which always creates a single sheet workbook. It does name it Sheet1, 2 etc
instead of Book1, 2 , etc.

Another way is to set the SheetsInNewWorkbook property, like so

Application.SheetsInNewWorkbook = 1
Workbooks.Add

The problem with this is that it changes the application, so all new
workbooks now have 1 sheet, so probably best to save the value and
re-instate it

cSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = cSheets


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________
  #10   Report Post  
Old August 11th 06, 04:40 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 10,593
Default Copy PasteSpecial not working

That is what I do when I add new workbooks, don't know why but it seems the
'right' way.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Richard Buttrey" wrote in
message ...
Excellent,

Thanks Bob.

I think I'll capture the value for the number of new sheets, and then
reinstate it when the macro finishes as you suggest.

Rgds


On Fri, 11 Aug 2006 14:36:10 +0100, "Bob Phillips"
wrote:

Richard,

It is the delete that is clearing the clipboard, making the pastespecial
fail.

When you add a workbook, the number of sheets is governed by the setting

in
ToolsOptionsGeneral. You can circumvent this by telling VBA what type

of
workbook to create, such as this

Workbooks.Add template:=xlWBATWorksheet

which always creates a single sheet workbook. It does name it Sheet1, 2

etc
instead of Book1, 2 , etc.

Another way is to set the SheetsInNewWorkbook property, like so

Application.SheetsInNewWorkbook = 1
Workbooks.Add

The problem with this is that it changes the application, so all new
workbooks now have 1 sheet, so probably best to save the value and
re-instate it

cSheets = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Workbooks.Add
Application.SheetsInNewWorkbook = cSheets


__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Automate Copy / PasteSpecial carl Excel Worksheet Functions 1 March 29th 06 07:07 PM
copy between worksheets does not copy formulae just values [email protected] Oaks Excel Discussion (Misc queries) 0 March 19th 06 11:44 AM
Copy / Paste Warning wbmcse Excel Discussion (Misc queries) 0 March 3rd 06 07:04 PM
copy paste is not working Becky1810 Excel Discussion (Misc queries) 1 July 16th 05 02:12 PM
Copy without Hidden Cols - How abrogard Excel Discussion (Misc queries) 1 July 15th 05 07:54 AM


All times are GMT +1. The time now is 06:59 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright 2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017