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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________

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
__________________________