Instantiated References in Excel

[John64]

Any help would be appreciated. I'm simply trying to copy a group of cells I've got selected in a separate workbook to the existing sheet.

This chunk of code works when I'm testing it in 2 sheets that are in the same file

Selection.Copy
With Sheets("testdump").Range("B" & y).End(xlToLeft).Offset(, 1)
     .PasteSpecial xlPasteFormats, Transpose:=True
     .PasteSpecial xlPasteValues, Transpose:=True
End With

I need to make it work while I'm pointing to a different file.
This Errors Out

Dim appXL As Excel.Application
Dim wbk As Excel.Workbook
Set appXL = New Excel.Application
Set wbk = appXL.Workbooks.Open(GrabIt)
...

appXL.Selection.Copy
With Sheets("Sheet1").Range("B2").End(xlToLeft).Offset(, 1)
     .PasteSpecial xlPasteFormats, Transpose:=True
     .PasteSpecial xlPasteValues, Transpose:=True
End With

The application.copy works. I don't know how to structure the 'Pastespecial'. I've used the code to reference other workbooks many times. I'm actually editing in a code that was working for a slightly different application. The sheet I want the information in is open and selected. The sheet it's coming from is open in an instantiated instance.

The error comes on the first .Pastespecial and says: error 1004 Pastespecial method of Range class failed

thx

 

[DavidAtWork]

Your code doesn't reference a second workbook, and where are you opening this second workbook. When you have 2 workbooks open, you then need to either make one the active workbook or reference each workbook with each command

wbk1.Selection.Copy
With wbk2.Sheets("Sheet1").Range("B2").End(xlToLeft).Offset(, 1)
    .PasteSpecial xlPasteFormats, Transpose:=True
    .PasteSpecial xlPasteValues, Transpose:=True
End With

David

 

[John64]

I've tried

Dim wbk2 As Excel.Workbook
Set wbk2 = ThisWorkbook
...
With wbk2.Sheets("sheet1").Range("B2").End(xlToLeft).Offset(, 1)
     .PasteSpecial xlPasteFormats, Transpose:=True
     .PasteSpecial xlPasteValues, Transpose:=True
End With

It errors out on the .Pastespecial, not the first line. I'm clueless at the moment.

 

[John64]

I've been messing with this a long time now. I've narrowed it down to an issue with pastespecial. I can get this to work fine:

DateR.Copy
Sheets("sheet1").Range("D2").Select
ActiveSheet.Paste

Any version of using a 'pastespecial' instead of a 'paste' throws error 1004. I'm wondering if it's an option like the 'CutCopyMode'?? Anyone ever experienced this problem before???

 

[John64]

Just found that this command:
ThisWorkbook.Sheets("Bakken").Range("B2").PasteSpecial Paste:=xlPasteAllExceptBorders

works fine as well, but if I attempt to add any parameters to it, such as 'Transpose", which is what I really need, it stops working

maybe its a reference library?

 

[Brianwarnock]

Give this a try


ThisWorkbook.Sheets("Bakken").Range("B2").PasteSpecial Paste:=xlPasteAllExceptBorders,,transpose:=true


Brian

 

[John64]

No, I couldn't get that to work. I've tried many similar versions. It doesn't let me do two commas after the "..tBorders,," With one comma it tries to run but errors on the line.

For now I've got it working very inefficiently by simply regular pasting into another spot on the page, copying again to do a pastespecial & transpose within that same page. Something with the referencing between workbooks just won't work for me.

Thanks for the suggestion

 

[John64]

I never did get this to work, but knowing that these threads can stay around for years, I thought I'd post what I ended up doing. Since my main objective was to transpose the numbers, I simple wrote a loop for my named paste range each time I would have used 'pastespecial'

It looks like this:

t = 0
For Each celly In WaterProdR
Sheets("Yellow").Range("B" & Z).Offset(0, t) = celly
t = t + 1
Next celly

WaterProdR is a named Range
Z is used for offsetting for my purposes
t is used to create the transpose by offsetting and copying upward

I reset t back to 0 every time I needed to do the next transposed paste

You should be able to get values or formulas only with this method as well. ie. celly.value

It's certainly not the most efficient, but it got the job done for me

Thanks for the help all

 

[Brianwarnock]

Found time to boot up the old PC today I'm on 2002 and coded the code below in a module in book2, book1 is open, and it ran no trouble.
It also worked with both books named when run from either.

Sub mymacro()
Workbooks("book1").Worksheets("sheet1").Range("a1:d1").Copy
  With ThisWorkbook.Worksheets("sheet1").Range("a1")
     .PasteSpecial Paste:=xlPasteAll, Transpose:=True
  End With
End Sub

Brian