I have been trying to run this macro but continue to get a "PasteSpecia
Method of Range Class Failed" error. Any ideas?


Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Su

--
Message posted from http://www.ExcelForum.com

Maybe the xlPasteValuesAndNumberFormats call is generating
an error because some of the values are not numbers
-----Original Message-----
I have been trying to run this macro but continue to get
a "PasteSpecial
Method of Range Class Failed" error. Any ideas?


Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpec ial _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub


---
Message posted from http://www.ExcelForum.com/

.

I suggested earlier that it was because the target worksheet was not
selected. Did you try that?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"sowetoddid " wrote in message
...
I have been trying to run this macro but continue to get a "PasteSpecial
Method of Range Class Failed" error. Any ideas?


Sub testIt()
Dim ThisWB As Workbook, OpenedWB As Workbook, _
OpenFileName As Variant
Set ThisWB = ThisWorkbook
OpenFileName = Application.GetOpenFilename()
If LCase(TypeName(OpenFileName)) = "boolean" Then
Else
Set OpenedWB = Workbooks.Open(OpenFileName)
OpenedWB.Sheets(1).Range("A1:Z100").Copy
ThisWB.Worksheets("sheet1").Range("b1").PasteSpeci al _
xlPasteValuesAndNumberFormats
OpenedWB.Close False
End If
End Sub


---
Message posted from http://www.ExcelForum.com/

I removed the AndNumberFormats and the code worked. You are right,
also have a little bit of text in the copied file

--
Message posted from http://www.ExcelForum.com