ActiveSheet.Paste Failing VBA Excel

Question

My VBA code is set to copy a value from one sheet (NB this value will change each time the sheet is open) and paste into a 'database' on the next available row.

Think I've got it right but the Paste method seems to fail, can anyone see why?

Windows("Invoice Program.xlsm").Activate
Range("B4").Select
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open ("C:\Users\Invoice Database.xlsx")
Windows("Invoice Database.xlsx").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.Paste

Please see [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/q/10714251/11683) — GSerg, Nov 20 '14 at 21:54
What do you mean "fail"? Do you get an error? Does the value not change? — Degustaf, Nov 20 '14 at 21:54

bilbo_strikes_back · Accepted Answer · 2014-11-20T23:22:07.940

0

Dim varTemp as Variant

Windows("Invoice Program.xlsm").Activate
Range("B4").Select
varTemp = ActiveCell.Value
Workbooks.Open ("C:\Users\Invoice Database.xlsx")
Windows("Invoice Database.xlsx").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
ActiveCell = varTemp

edited Nov 20 '14 at 23:22

answered Nov 20 '14 at 22:52

bilbo_strikes_back

591
3
10

Hey, cheers for the help but ActiveSheet is what I had originially,i get 'Paste method of Worksheet class failed' error 1004 – A Anderson Nov 20 '14 at 22:54
I used some "dummy" workbooks on my system and the code ran fine. Is it the last line of code that creates the error message? – bilbo_strikes_back Nov 20 '14 at 23:01
Yeah ActiveSheet.Paste is what flags up as the error – A Anderson Nov 20 '14 at 23:07
I edited the code to use a variant variable instead of using the clipboard. Maybe this will work. – bilbo_strikes_back Nov 20 '14 at 23:22

score 0 · Answer 2 · answered Mar 05 '15 at 22:05

0

Use ActiveSheet.Paste but in the line before use DoEvents.

DoEvents
ActiveSheet.Paste

answered Mar 05 '15 at 22:05

dasg7

46
8

ActiveSheet.Paste Failing VBA Excel

2 Answers2