Runtime error 1004: Pastespecial method of range class failed 1

[Abi1]

Hi,

I keep getting the above error message when pasting values. What am I doing wrong?

The exact situation is that I'm trying to copy individual rows of data from one workbook to another. The code looks at the source workbook, records each team number in the variable import_team (Dim as Double), then it looks at the target workbook and pastes the values when the team numbers match. Here is the bit of code I'm using:


For Each cell In Range("A7:A40&quot

import_team = Left(cell.Value, 4)

Range(cell.Offset(0, 1), cell.Offset(0, 17)).Copy

Workbooks(model).Activate
Sheets("input&quot.Select

For Each c In Range("B3:B39&quot
If c.Offset(0, -1).Value = import_team Then
c.PasteSpecial xlPasteValues
Else
End If
Next c
Next cell


I have 2 questions on this:
1. why am I getting the error message? and
2. is there a better way of doing this. Since I've got nested loops I expect there is.

Thanks in advance

 

[xlbo]

Which line are you getting the error on ??
I can see a coupla potential problems
1:you are just providing the constant for pastespecial - generally, I always include the property as well eg
pastespecial PASTE:= xlvalues
2:you are activating your second workbook but not re-activating the 1st workbook after the loop has finished so excel is trying to use Cell within your "Model" workbook

Here is some sample code which should do the trick:

For Each cell In Range("A7:A40&quot

import_team = Left(cell.Value, 4)

Range(cell.Offset(0, 1), cell.Offset(0, 17)).Copy
with Workbooks(model).Sheets("input&quot.
set fCell = .range("A3:A39&quot.find(import_team,lookin:=xlvalues,lookat:=xlwhole)
if not fCell is nothing then
'team found
.range(fCell.address).offset(0,1).pastespecial paste:=xlvalues
else
msgbox "Team " & import_team & " not found"
Next cell
Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Abi1]

It works! And it's a much more cunning way of doing it!

Thank you Geoff!

 

[xlbo]

ooops - forgot the end if - glad it works tho Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur