Copying Cells and Pasting into another workbook - 424 error

Question

I am trying to run a program that spits out data. at the end of the process i want to copy that data into another workbook. I have it set up and working to the point that it copies and pastes the data into the other workbook but i get a 424 error on the past step. Code Below.

sheet = Range("BL1") 
Range("BJ3:BM12").Copy 

Set wbk = Workbooks.Open("c:location")

x = 1
While wbk.Worksheets(sheet).Range("I" & x) <> ""
    x = x + 1
Wend
wbk.Sheets(sheet).Range("I" & x).PasteSpecial.Value   - Problem arises here

Application.CutCopyMode = False
ActiveWorkbook.Save
Call wbk.Close(False)

score 0 · Answer 1 · answered Feb 04 '14 at 18:59

0

change

wbk.Sheets(sheet).Range("I" & x).PasteSpecial.Value

to

wbk.Sheets(sheet).Range("I" & x).PasteSpecial xlPasteValues

answered Feb 04 '14 at 18:59

Dmitry Pavliv

35,333
13
79
80

Thank you simoco, I tried the suggestion but get "compile error. Variable not defined" do i need to define something? – user3272213 Feb 04 '14 at 20:04
Is the x1 x and 1(one) or x and l(L)? – user3272213 Feb 04 '14 at 20:22
what value in your `sheet = Range("BL1")` variable? – Dmitry Pavliv Feb 04 '14 at 20:28
wbk.Sheets(sheet).Range("I" & x).PasteSpecial Paste:=xlPasteValues – user3272213 Feb 04 '14 at 20:50
mm..good, but what are you going to say with your last comment? – Dmitry Pavliv Feb 04 '14 at 20:56

Amadea · Answer 2 · 2014-02-05T09:23:02.503

0

The problem arises when you try to paste from a workbook that is not the active workbook. You would think the workbook you are pasting into should be activated, but you couldn't be more wrong. It doesn't make sense, I know. ;)

Sub a()

Sheet = "sheet1" 'Range("BL1")  'changed this for the sake of simplicity

Set cwb = Application.ActiveWorkbook
Set wbk = Workbooks.Open("e:\b.xls")

x = 1
While wbk.Worksheets(Sheet).Range("I" & x) <> ""
    x = x + 1
Wend
Set PasteStart = wbk.Worksheets(Sheet).Range("I" & x) 
cwb.Activate  'Activate the workbook you want to copy data from
With Range("BJ3:BM12")
   .Copy  
   .PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   .Copy PasteStart
End With

Application.CutCopyMode = False
wbk.Activate
ActiveWorkbook.Save
Call wbk.Close(False)
End Sub

Beware, this code paste values to the original workbook to achieve the paste value effect, so you might want to add some code to close the original workbook without saving.

edited Feb 05 '14 at 09:23

answered Feb 05 '14 at 09:17

Amadea

51
1
1
10

`The problem arises when you try to paste from a workbook that is not the active workbook` - wrong. Read [this Q/A](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select). You should avoid using `Select` and `ActiveSth` statements in all cases when it's possible – Dmitry Pavliv Feb 06 '14 at 19:29
Thanks for the Q/A, very helpful. However, the problem user was encountering was because he was trying to paste from the sheet that was no longer the active sheet with no specified range or object after opening another file, so in effect the other file would become the active sheet. The fact that you can use Dim'd ranges and avoid setting active sheet as a better way is beside the point. – Amadea Feb 08 '14 at 05:06

Copying Cells and Pasting into another workbook - 424 error

2 Answers2