Pasting problem 1

[Stretchwickster]

Can anyone spot a problem with this code:

  Workbooks.Open (AFilename)
  ActiveWorkbook.Worksheets(1).Range("A1:E513").Select
  Selection.Copy
  ThisWorkbook.Activate
  Worksheets("DataSheet").Range("A1:K1").NumberFormat = "@"
  Worksheets("DataSheet").Range(ACellReference).Activate
  ActiveSheet.Paste

I'm getting an error on the last line: "Paste method of Worksheet class failed"

Anyone got any ideas?

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Loomah]

Hi
Setting the format (it appears) clears the clipboard of the info you want.
Could you move the format line to the end?
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[Stretchwickster]

I just found that out! Good call Loomah! Star 4 u!

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[Loomah]

BTW
You could avoid a lot of the code you have in this way

ActiveWorkbook.Worksheets(1).Range("A1:E513").Copy _
    Destination:=ThisWorkbook.Worksheets("DataSheet").Range("A1")
ThisWorkbook.Worksheets("DataSheet").Range("A1:K1").NumberFormat = "@"

;-)


If a man says something and there are no women there to hear him, is he still wrong?

 

[Stretchwickster]

Loomah,

Just another quick question. If I want to repeat the following code 4 times, how would I go about it? I've already tried to nest the loop below inside another for loop but it produces an "Overflow" error.

For i = 2 To 513
    Worksheets("ComparisonSheet").Cells(i, 1).Value = _
     (Worksheets("DataSheet").Cells(i, 2).Value - _
     Worksheets("DataSheet").Cells(i, 8).Value) / _
     Worksheets("DataSheet").Cells(i, 2).Value
Next i

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)

 

[xlbo]

What is i dimmed as ???
overflow generally means that a value attributed to a variable has gone over the limit set for the variable type ie trying to assign a value > 36000 to an integer....

Rgds
Geoff
Si hoc legere scis, nimis eruditionis habes

 

[Stretchwickster]

I initially thought the number of loop iterations were too great for VBA to handle. But I've realised it's because I was trying to divide by zero when

 Worksheets("DataSheet").Cells(i, 2).Value

happened to equal zero. Problem solved. Cheers guys for your assistance. Thanks Loomah for condensing my code.

Clive

http://www.kucu.co.uk

Ex nihilo, nihil fit (Out of nothing, nothing comes)