PasteSpecial giving error #1004

[BodiKlamph]

Hi,

I have a weird problem. I've created a workbook that dozens of sales
people use. One laptop, which is the exact same configuration as all
of the others, is having a problem running my code.

With Sheets("Order")
.Range("A2:V200").Delete

Sheets("OrderPrep").Range("B3:U29").Copy
.Range("B1").PasteSpecial xlPasteValues
.Range("B1").PasteSpecial xlPasteFormats
End With


It's not this specific piece of code; it happens with EVERY
pastespecial that this laptop runs. I can't figure it out!

any suggestions?

thx

 

[Gary''s Student]

Your code works. Make sure the tab names are spelled correctly.

 

[Gary Keramidas]

works here, i know you can get a 1004 error if the sheet's protected, but you
say it runs on other pc's.

 

[BodiKlamph]

Your code works.  Make sure the tab names are spelled correctly.
--
Gary''s Student - gsnu200802











- Show quoted text -

It's nothign wrong with the workbook, it's gotta be a setting
somewhere. It works fine on 11 other computers.

 

[Gary Keramidas]

what's the text of the 1004 error?

--


Gary

Your code works. Make sure the tab names are spelled correctly.
--
Gary''s Student - gsnu200802











- Show quoted text -

It's nothign wrong with the workbook, it's gotta be a setting
somewhere. It works fine on 11 other computers.

 

[Gary''s Student]

Might be that the personal.xls on one of the computers is somehow screwing
things up??

 

[BodiKlamph]

what's the text of the 1004 error?

--

Gary






It's nothign wrong with the workbook, it's gotta be a setting
somewhere.  It works fine on 11 other computers.- Hide quoted text -

- Show quoted text -

"Error #1004: Merged Cells must be identically sized"

 

[BodiKlamph]

Might be that the personal.xls on one of the computers is somehow screwing
things up??
--
Gary''s Student - gsnu200802





- Show quoted text -

Just chcked and there's nothing unusual being loaded from the
XLSTARTUP, etc folders.

Also, I tried to run the code as if I was doing it manually (selected
some cells, went to a new sheet, edit, paste special) and it gave me
the same error. So it's most definately not the code but rather
something in that particular instance of excel.

 

[Gary Keramidas]

there must be a merged cell on the order sheet. put a breakpoint on this line
Sheets("OrderPrep").Range("B3:U29").Copy

and see if there is a merged cell in the are you're trying to copy to.

--


Gary

Might be that the personal.xls on one of the computers is somehow screwing
things up??
--
Gary''s Student - gsnu200802





- Show quoted text -

Just chcked and there's nothing unusual being loaded from the
XLSTARTUP, etc folders.

Also, I tried to run the code as if I was doing it manually (selected
some cells, went to a new sheet, edit, paste special) and it gave me
the same error. So it's most definately not the code but rather
something in that particular instance of excel.

 

[Gary Keramidas]

or try this
.Range("A2:V200").Clear
instead of
..Range("A2:V200").Delete

--


Gary

Might be that the personal.xls on one of the computers is somehow screwing
things up??
--
Gary''s Student - gsnu200802





- Show quoted text -

Just chcked and there's nothing unusual being loaded from the
XLSTARTUP, etc folders.

Also, I tried to run the code as if I was doing it manually (selected
some cells, went to a new sheet, edit, paste special) and it gave me
the same error. So it's most definately not the code but rather
something in that particular instance of excel.

 

[BodiKlamph]

or try this
 .Range("A2:V200").Clear
instead of
.Range("A2:V200").Delete

--

Gary






Just chcked and there's nothing unusual being loaded from the
XLSTARTUP, etc folders.

Also, I tried to run the code as if I was doing it manually (selected
some cells, went to a new sheet, edit, paste special) and it gave me
the same error.  So it's most definately not the code but rather
something in that particular instance of excel.- Hide quoted text -

- Show quoted text -

I fixed it. The solution is simple, the reason who the hell knows.


Instead of using PasteSpecial, I just combined it into one command

with Sheets("OrderPrep")
..Range("B3:U29").Copy .Range("B1")
end with

 

[BodiKlamph]

I fixed it.  The solution is simple, the reason who the hell knows.

Instead of using PasteSpecial, I just combined it into one command

with Sheets("OrderPrep")
.Range("B3:U29").Copy .Range("B1")
end with- Hide quoted text -

- Show quoted text -

I'd like to re-open this thread...

My solution above isn't good. Using the .Copy method with the
destination passed along will change references and that's not cool,
as I need it to copy the values only.

So...Does anybody have any idea why a PasteSpecial would cause erro
1004 "merged cells must be identically sized" on one instance of
excel, but not another?

thx

 

[Gary Keramidas]

see if this, at least, works without errors.

Sub test()
With Sheets("Order")
.Range("A2:V200").Delete
With Sheets("OrderPrep").Range("B3:U29")
.MergeCells = False
.Copy
End With
.Range("B1").PasteSpecial xlPasteValues
.Range("B1").PasteSpecial xlPasteFormats
End With
End Sub


--


Gary

I fixed it. The solution is simple, the reason who the hell knows.

Instead of using PasteSpecial, I just combined it into one command

with Sheets("OrderPrep")
.Range("B3:U29").Copy .Range("B1")
end with- Hide quoted text -

- Show quoted text -

I'd like to re-open this thread...

My solution above isn't good. Using the .Copy method with the
destination passed along will change references and that's not cool,
as I need it to copy the values only.

So...Does anybody have any idea why a PasteSpecial would cause erro
1004 "merged cells must be identically sized" on one instance of
excel, but not another?

thx

 

[BodiKlamph]

see if this, at least, works without errors.

Sub test()
      With Sheets("Order")
            .Range("A2:V200").Delete
            With Sheets("OrderPrep").Range("B3:U29")
                  .MergeCells = False
                  .Copy
            End With
            .Range("B1").PasteSpecial xlPasteValues
            .Range("B1").PasteSpecial xlPasteFormats
      End With
End Sub

--

Gary








I'd like to re-open this thread...

My solution above isn't good.  Using the .Copy method with the
destination passed along will change references and that's not cool,
as I need it to copy the values only.

So...Does anybody have any idea why a PasteSpecial would cause erro
1004 "merged cells must be identically sized" on one instance of
excel, but not another?

thx- Hide quoted text -

- Show quoted text -

It didn't work. However, I do have something interesting to report.
When I run that code in a new book, it works. When I put that code
into the existing workbook, it fails.

That means it's something to do specifically with the laptop AND the
workbook.

Now I'm even more lost.