I have looked thoroughly at the current answers for this problem and none of them have fixed mine.
The operation is simply copying a selection of a sheet and copying to a new book called budget.
Again I have tried multiple different ways of doing the same thing and none of them seem to change this error. The select method works,it only breaks when I try to paste.
Code:
Range("B3").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs FileName:=ThisWorkbook.path & "\" & "Budget.xlsx"
wk.Activate
wk.Unprotect
wk.Worksheets("Sheet1").Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Thanks,
YowE3K
23.9k7 gold badges26 silver badges40 bronze badges
asked Jul 19, 2017 at 16:12
2
If you try exactly the steps in the code manually, you will find it also fails. The issue is that inbetween copying the cells and trying to paste them, you are creating a new workbook and saving it. This cancels copy/paste mode (i.e. the «marching ants» around the copied range disappear), so there is nothing to paste.
The solution is to not use Selection at all. In general any time you find yourself writing .Select in VBA you’re doing it wrong (see this question for detail). Here is how I would re-write your code:
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs Filename:=ThisWorkbook.Path & "\" & "Budget.xlsx"
ThisWorkbook.Range("B3").CurrentRegion.Copy
wk.Worksheets("Sheet1").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
answered Jul 19, 2017 at 16:26
aucupariaaucuparia
2,02120 silver badges27 bronze badges
Try the code below (explanation inside the code as comments):
Dim wk As Workbook
Set wk = Workbooks.Add
wk.SaveAs Filename:=ThisWorkbook.Path & "\" & "Budget.xlsx"
wk.Activate
wk.Unprotect
' have the Copy>>Paste section together
Dim LastCol As Long
Dim LastRow As Long
' you never mentioned which sheet to copy from, I used the first index
With ThisWorkbook.Sheets(1)
LastCol = .Range("B3").End(xlToRight).Column
LastRow = .Range("B3").End(xlDown).Row
.Range("B3", .Cells(LastRow, LastCol)).Copy ' <-- Copy without Select
End With
' Paste without Select
wk.Worksheets("Sheet1").Range("B3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
answered Jul 19, 2017 at 16:30
Shai RadoShai Rado
33.1k6 gold badges29 silver badges51 bronze badges
1
|
pasteSpecial из worksheet завершен неверно (Excel 2016) |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
||||||||
Ответить |
Как минимум ничего не потеряли. Тогда надо видеть файлы. только неI’ve been looking at this for ages now and I know its something simple.
the code below is throwing me an error «Method ‘PasteSpecial’ of object ‘Range’ failed» is the error
With xlWB.Sheets("Lookups")
'Copy lookups tab to word test tables
.Cells.Copy
ThisWorkbook.Sheets("Word_Test_Tables").Cells.PasteSpecial
Application.CutCopyMode = False
End With
The error occurs on the pastespecial.
Once this error has happened the code will continue to f5 through but nothing will actually happen, I can not select any tabs or workbooks and have to end task the application.
Putting a break point on the error line I can get a range from
?ThisWorkbook.Sheets("Word_Test_Tables").Cells
I have tried activating the sheet, unhiding the sheet. I can confirm xlWB is valid and open, .cells.copy looks to be working as you get the highlighting round the sheet.
Any ideas?
asked May 28, 2015 at 15:23
I don’t really know why you would copy all the cells rather than just the used range:
xlWB.Sheets("Lookups").Usedrange.copy _
Destination:=ThisWorkbook.Sheets("Word_Test_Tables").Range("A1")
answered May 28, 2015 at 15:41
RoryRory
32.8k5 gold badges32 silver badges35 bronze badges
8
You need to come out of your ‘with’ to paste the values in the other workbook:
With xlWB.Sheets("Lookups")
'Copy lookups tab to word test tables
.Cells.Copy
End With
ThisWorkbook.Sheets("Word_Test_Tables").Cells.PasteSpecial
answered May 28, 2015 at 15:32
Uberzen1Uberzen1
4156 silver badges18 bronze badges
4
You may have worksheets with different sizes depending on the source workbooks. Try copying a smaller Range.
Besides, you probably want ActiveWorkbook instead of ThisWorkbook.
answered May 28, 2015 at 16:09
1
- Remove From My Forums
-
Question
-
I have a macro which copies and pastes images when a cell value is changed.
Stepping through the code works fine but running by addressing the cell gives the following error:
Run-Time error ‘1004’:
RunPasteSpecial method of Range class failed.
When I debug it goes to this line:
ActiveSheet.Shapes("Signature").Copy Sheets("Quote").Range("B35").PasteSpecialI’ve tried changing Paste to PasteSpecial and copying in different ways but to no avail.
Annoyingly the macro runs fine on certain computers but gives the above error on others.
Could it be a concurrency error or something to do with the clipboard?
I’ve searched through loads of forums and found nothing so any help would be greatly appreciated.
Thanks in advance.
-
Moved by
Monday, January 30, 2012 8:55 AM
Moving to a more appropriate forum (From:Excel IT Pro Discussions)
-
Moved by
Answers
-
I have found the cause of this problem.
It was caused by the Paste operation being run before the compiler had finished copying the image.
It was resolved by putting
Do Events
between the Copy and Paste actions.
-
Marked as answer by
evoandy
Wednesday, February 6, 2013 3:54 PM
-
Marked as answer by
| Цитата |
|---|
| kuklp написал: RoMuLX , это еще модеры Вашего никнейма не видели. |
Вообще-то модераторы уже видели мой никнейм и даже помогали без всяких замечаний, за что им низкий поклон.
Это вы бы могли и сами заметить,если б внимательнее прочли мой пост.
Вообще воспринимайте мой никнейм как торговую марку с утвержденным фиксированным регистром уже более 15 лет (нигде проблем не было).
Вы свой-то никнейм видели?!) Это ж бессмысленным набором символов))
| Цитата |
|---|
| kuklp написал: И то что Вы вторглись в чужую тему со своим вопросом им тоже вряд ли понравится. |
И не вторгся, а продолжил ту же тему. Я наступал на те же грабли, что и описаны выше.
Обсуждения в данной теме уже 22 Мар 2017 15:58:53 было закончено, соответственно мой пост не вносит путаницу.
Если заметили, уже однажды до меня (22 Мар 2017 12:28:10) тема так же поднималась и ничего, никого не заблокировали за «нарушение».
И вы, наверно, плохо сами прочитали правила форума:
| Цитата |
|---|
| 3. Запрещено: 3.7. Создавать темы с вопросами, ответы на которые даны в Приемах или прошлых темах форума. 4. Не рекомендуется: |
Не вижу у вас надписи «модератор»- поэтому либо не флудите\оффтопте, либо помогите.
Я нашел
поиском
последнюю открытую и продолжил, т.к. в моем случае есть нюансы.
И я не пишу как халявщик, я полностью все оформил в соответствии с правилами — описал и дал пример, и даже свой код не до конца правильный выложил.
И проблему рваного копирования ячеек мной была решена немного по-другому, но не до конца (только в одну сторону).