Thread: pastespecial method of range class failed

Hey, I am working on a program with multiple excel files, copying the same cells off each one, and adding the transpose of all of them to a table in access. The programming is in vba in access. However, the pasteSpecial method isn’t working for me, and I’m always getting a “pastespecial method of range class failed”. I tried a lot of things already but nothing seems to work.

So I tried something simpler, simply adding values to an excel document, copying and pasting the transpose two cells over with the pasteSpecial. But it’s STILL not working. When I run the program, the excel sheet opens and I see the data, I see it's copied, and the cell E1 is selected, but it's not pasting. I can go in and manually press pasteSpecial, transpose. But the coding isn't running this part of the program, and is consistently throwing the "pastespecial method of range class failed" error.

Can someone see what I’m doing wrong with this simple program, and hopefully I can apply it to the more complicated program?

Thanks a million.

Sub Testing ()

Set objAccess = CreateObject("Access.Application")
objAccess.OpenCurrentDatabase "C:\Database2.accdb"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add

objWorkbook.Sheets("Sheet1").Range("A2") = "A"
objWorkbook.Sheets("Sheet1").Range("A3") = "B"
objWorkbook.Sheets("Sheet1").Range("A4") = "C"
objWorkbook.Sheets("Sheet1").Range("B1") = "1"
objWorkbook.Sheets("Sheet1").Range("C1") = "2"
objWorkbook.Sheets("Sheet1").Range("D1") = "3"
objWorkbook.Sheets("Sheet1").Range("B2") = "x"
objWorkbook.Sheets("Sheet1").Range("C2") = "y"
objWorkbook.Sheets("Sheet1").Range("D2") = "z"
objWorkbook.Sheets("Sheet1").Range("B3") = "i"
objWorkbook.Sheets("Sheet1").Range("C3") = "j"
objWorkbook.Sheets("Sheet1").Range("D3") = "k"
objWorkbook.Sheets("Sheet1").Range("B4") = "5"
objWorkbook.Sheets("Sheet1").Range("C4") = "6"
objWorkbook.Sheets("Sheet1").Range("D4") = "7"

objWorkbook.Sheets("Sheet1").Range("A1:D4").Copy
objWorkbook.Sheets("Sheet1").Range("E1").PasteSpecial Paste:=xlFormats, Operation:=xlNone, _
     SkipBlanks:=True, Transpose:=False

End Sub

I also cross posted in the MrExcel forum, and they pointed out that I didn't have my reference to the Excel object library checked.
http://www.mrexcel.com/forum/showthread.php?t=601528
So this is solved.

Thank you for pointing out the flaws in my posts and habits. I have never used forum for help before, so it's been a good experience.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this, then please edit your post accordingly.

Romperstomper, I did what you said
Marked as solved.