ARRGGH! Stupid xlPasteAll problem!!!

[ClimoC]

I am trying to do a very simple "PasteAll" PasteSpecial between workbooks.

The problem is that when I do, it pastes the data as an image file! WTF?!

Unfortunately, no other kind of 'PasteSpecial' will do, because different parts of the text in each cell has varying colours to them. A normal GUI paste takes care of this, and doesn't do it as a picture.

The best I've gotten it to is to stop pasting as an image by simply typing:

destinationcell.PasteSpecial "Unicode Text"

But this won't bring across the various text colours that part-make up each cell.

Current code is this...

SRange = SBook.Sheets("By Type").Range("A1:A" & SBook.Sheets("By Type").Range("A65000").End(xlUp).Row).Find(what:=ConsolC, _
                                        LookIn:=xlValues, lookat:=xlWhole).Address
                    Debug.Print SBook.Name
                    SBook.Sheets("By Type").Range(SBook.Sheets("By Type").Range(SRange).Offset(8, 10)).Copy
                    Debug.Print OutPut.Sheets("By Type").Range("B65000").End(xlUp).Row + 1
                    
                    If OutPut.Sheets("By Type").Range("A65000").End(xlUp).Row > 1 Then
                            OutPut.Sheets("By Type").Range("A" & OutPut.Sheets("By Type").Range("A65000").End(xlUp).Row + 9).PasteSpecial _
                                "Unicode Text"
                    Else
                            OutPut.Sheets("By Type").Range("A" & OutPut.Sheets("By Type").Range("A65000").End(xlUp).Row + 1).PasteSpecial _
                                "Unicode Text"
                    End If

I also tried "PasteSpecial Format:="Microsoft Excel 8.0" blah blah or whatever it is, doesn't work.

I'm going out of my mind here...

The only thing I thought that might have been 'force converting' the data into an image is because there's a jpeg Shape-object within the range. But even when I delete all the shapes in the sheet first, and then copy and paste, it still comes out as an image object!!1

Please help, I'm going out of my freakin' mind with this...

 

[shg]

Try this:

    Dim rInp        As Range
    Dim rOut        As Range
 
    Set rInp = SBook.Worksheets("By Type").Columns("A").Find(What:=ConsolC, _
                                                             LookIn:=xlValues, _
                                                             LookAt:=xlWhole).Address
    Set rOut = OutPut.Worksheets("By Type").Range("A65000").End(xlUp)
    If rOut.Row > 1 Then Set rOut = rOut.Offset(8)
 
    rInp.Offset(8, 10).Copy rOut

 

[ClimoC]

Thanks, though something I forgot to mention is that I made a mistake when posting the code and editing for security reasons. What I'm actually copying and pasting is 49 cells - 7 x 7 grids, that all begin at an offset of (2,3) from their 'title' which are the only non-blank cells in Col A.

So I'm trying to get the whole grid across. When I do it via the GUI, it works fine. VBA turns it into a picture.

I might just have to loop it through (though I don't want to, it's another 5 seconds on top of what's already 20 seconds to copy, paste, and transpose twice)

Thanks though, I'll give what you said a go

But if anyone knows what causes this behaviour, or how to specifically declare that it needs to stay as is, I'd love to know!!! Stupid Range object :S
C