PasteSpecial Issue

[davidam]

Hello Everyone,
I have the following code which I use to paste some data from the web onto a spreadsheet. (thisSheet is the working sheet, newSheet is a temporary book/sheet. I have copied the entire web page onto newSheet, then isolated the needed data with the "topRow, botRow" stuff.) It works fine except for one thing, it also pastes formatting and some pictures.

topRow = newSheet.Range("G1").End(xlDown).Row + 1
botRow = newSheet.Range("G500").End(xlUp).Row
Set copyRange = newSheet.Range("G" & topRow & ":" & "Z" & botRow)
pasteRow = thisSheet("Sheet1").Range("AA2999").End(xlUp).Row + 1
Set pasteCell = thisSheet.Range("AA" & pasteRow)
ThisWorkbook.Activate
copyRange.Copy pasteCell
newBook.Close False

I need to eliminate the formatting and pictures and just paste the values. I think I need to change the line:

copyRange.Copy pasteCell

but all my attempts have been somewhat disasterous. Any suggestions?
Thank you,
David

 

[VoG]

Try

copyRange.Copy
pasteCell.PasteSpecial Paste:=xlPasteValues

 

[njimack]

Does this do the trick?

copyRange.Copy
Range(pasteCell).PasteSpecial xlValues

 

[davidam]

Yes thanks, that works. One thing I notice is that each cell has the error message in the corner. When I click it it says that: the number is formattted as text (or preceeded by an apostrophe). With hundreds of cells, this is probably very inefficient. Is there a way to eliminate this?

 

[davidam]

OK, solved the text issue with "Text to Columns" as documented elsewhere in the forum. Thanks!