Gillian has attended:
Excel VBA Introduction course
PasteSpecial Method of Range class failed
Hi,
I'm trying to copy some cells from one worksheet and then transpose paste them onto a worksheet in another workbook, but I'm getting this message:
pastespecial method of range class failed
That's my first problem. My second one is after i've opened the first workbook and copied and pasted into the active workbook, I then need to do the same again, but a different workbook and pasting one column on.
This is what i've written so far - but it doesn't address my 2nd problem. I can open the relevant workbooks and copy from them, but I can't figure out how to paste one column on each time.
For wk = 1 To 2
BARB = 2448 + wk
Workbooks.Open Filename:= _
"Y:\Gillian\2016 Mix\Device Status\Inputfiles\BIO_ihrs_set_TOTAL_PANEL_" & BARB & ".CSV"
Range("AR19:BA19").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveWorkbook.Close
Windows("Template.xlsm").Activate
Range("f6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
Next wk
End Sub
So questions are:
How can I get it to paste?
How can I get the 2nd paste to be in G6, 3rd in H6 etc?
Thank you for any help you can give!
RE: PasteSpecial Method of Range class failed
I've worked out why I was getting the message regarding the paste special. It was because I was closing the workbook I had copied from before pasting so had an empty clipboard.
I just need help getting the paste in the correct place now.
RE: PasteSpecial Method of Range class failed
And I've figured that out too now.
No help required anymore
RE: PasteSpecial Method of Range class failed
Hi Gillian
Well done solving your own query!
You beat me too it. Just in case you want ot see my answer, here it is ...
Sub TransposeData()
For wk = 1 To 2
BARB = 2448 + wk
Workbooks.Open Filename:= _
"Y:\Gillian\2016 Mix\Device Status\Inputfiles\BIO_ihrs_set_TOTAL_PANEL_" & BARB & ".CSV"
Range("AR19:BA19").Select
Selection.Copy
' ActiveWorkbook.Close
Windows("Template.xlsm").Activate
Cells(6, wk + 5).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Windows("BIO_Total" & BARB & ".CSV").Activate
ActiveWorkbook.Close
Application.CutCopyMode = False
Next wk
End Sub
Changes
I took out the line
Range(Selection, Selection.End(xlToRight)).Select
As you said, close the workbook after transposing rather than before.
Added instead of Range("F6").Select
Cells(6, wk + 5).Select
Regards
Doug
Best STL
