forsbom
asked on
Excel : PasteSpecial question
I need to copy Formats and Values from one sheet to a new sheet (NOT formulas). There are several merged cells in the source sheet. First I copy and PasteSpecial - Formats and when I PasteSpecial - Values I get an error message telling me that the merged cells need to be indentically sized!
But when I PasteSpecial - Formats the cells in the target sheet and in the source sheet IS exactly the same size!
How do I solve this problem ??
Heres my code :
Cells.Copy ' Copy all cells
wbInputResultWBook.Activat
wshNewAssetSheet.Select ' Select target sheet
Cells.Select 'select all cell
' Paste Formats
Cells.PasteSpecial _
Paste:=xlPasteFormats _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
' Paste values
Cells.PasteSpecial _
Paste:=xlValues _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
I this question is probably easy, but if it's not I'll add some more poins!
Peter Forsbom
But when I PasteSpecial - Formats the cells in the target sheet and in the source sheet IS exactly the same size!
How do I solve this problem ??
Heres my code :
Cells.Copy ' Copy all cells
wbInputResultWBook.Activat
wshNewAssetSheet.Select ' Select target sheet
Cells.Select 'select all cell
' Paste Formats
Cells.PasteSpecial _
Paste:=xlPasteFormats _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
' Paste values
Cells.PasteSpecial _
Paste:=xlValues _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
I this question is probably easy, but if it's not I'll add some more poins!
Peter Forsbom
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
cri
BTW: 'Excel 2000 Merged' currently has 37 articles in the MSKB, so this in no solution either...
ASKER
Hi cri
I see in the link that Microsoft's status on this issue is : "Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000."
Well, it's still a problem because I'm using Excel 2000 (I forgot to mention that)!
Try it yourself if you have Excel 2000 installed.
It works fine if you just Copy and Paste - All!
You see cri, it's not me who designed the sheets, I'm just coding the VBA for the users.
It lookss like I have to do it the hard way..... :-)
Peter
I see in the link that Microsoft's status on this issue is : "Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem no longer occurs in Microsoft Excel 2000."
Well, it's still a problem because I'm using Excel 2000 (I forgot to mention that)!
Try it yourself if you have Excel 2000 installed.
It works fine if you just Copy and Paste - All!
You see cri, it's not me who designed the sheets, I'm just coding the VBA for the users.
It lookss like I have to do it the hard way..... :-)
Peter
ASKER
It's still a problem in Excel 2000
Check : http://support.microsoft.com/support/kb/articles/Q258/9/50.ASP?LN=NO&SD=gn&FR=0
Peter
Check : http://support.microsoft.com/support/kb/articles/Q258/9/50.ASP?LN=NO&SD=gn&FR=0
Peter
ASKER
I guess that I could get around this problem by first setting the calculation to manual and then copy all cells in the source sheet, then PasteSpecial - All to the target sheet. Then copy target sheet and paste values to target sheet.
This code should do it:
Application.Calculation = xlCalculationManual 'Set calculation to manual
Cells.Copy ' Copy all cells
wbInputResultWBook.Activat
wshNewAssetSheet.Select ' Select target sheet
Cells(1, 1).Select 'Select all cells
Cells.PasteSpecial _
Paste:=xlPasteAll _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
Cells.Copy
Cells.PasteSpecial _
Paste:=xlValues _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
I'm posting lots of comments to my own question here ..... :-)
I'll give cri the points for showing me that this is a known problem in Excel! I should have checked the MSKB !
Peter
This code should do it:
Application.Calculation = xlCalculationManual 'Set calculation to manual
Cells.Copy ' Copy all cells
wbInputResultWBook.Activat
wshNewAssetSheet.Select ' Select target sheet
Cells(1, 1).Select 'Select all cells
Cells.PasteSpecial _
Paste:=xlPasteAll _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
Cells.Copy
Cells.PasteSpecial _
Paste:=xlValues _
, Operation:=xlNone _
, SkipBlanks:=False _
, Transpose:=False
I'm posting lots of comments to my own question here ..... :-)
I'll give cri the points for showing me that this is a known problem in Excel! I should have checked the MSKB !
Peter
forsbom, not sure what the second Cells.Copy in the target sheet is good for....
I would loop through the cells (despite it being slow)
I would loop through the cells (despite it being slow)
forsbom, thAnks.