Error while running paste special macro (excel 2000)

Topic

New Reply

Hi again guys….

Sub Name()

‘name
Sheets(“GlobalKelas”).Select
Range(“b7:b48”).Select
Selection.Copy

Sheets(“All”).Select
ActiveSheet.Unprotect Password:=””
Range(“b7″).Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

End Sub

Above code always keeps getting error :

” Run Time Error 1004

PasteSpecial method of Range class failed ”

and highlight this :

” Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False ”

Why this thing occured and how can I overcome it..thanks a zillion

Reply | Quote

Replies

“Name” is not a good name for a macro, since it is a property of many things in VBA. The code can be shortened to this, avoiding selecting worksheets and cells:

Sub Test()
Sheets(“GlobalKelas”).Range(“B7:B48”).Copy
With Sheets(“All”)
.Unprotect Password:=””
.Range(“B7”).PasteSpecial Paste:=xlValues
End With
End Sub

I see no reason why it shouldn’t work – I don’t get an error when I run either your version or mine in a test workbook with worksheets GlobalKelas and All.

Reply | Quote

Thanks Hans…it works like a charm

Reply | Quote

Thanks Hans…it works like a charm

Reply | Quote