Hi

I enounter run-time error 1004 when i try to copy & paste my data to a
starting cell A8

Extract from visual basic
The information cannot be pasted because the copy area & the paste
area are not the same size & shape.


The code below works fine when i select an item BUT the error arises
when i choose to display all the items .

my code is residing in a worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
'allow user to choose which record to display

Dim x As Variant
Dim rng As Range

On Error Resume Next
x = Target.Validation.ErrorMessage

If Err.Number = 0 Then
On Error GoTo 0
Else
Call addval
End If

If Target.Row = 4 And Target.Column = 2 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("summary").Range("B4").Calculate

Worksheets("raw").Range("data") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("summary").Range("B3:B4"), _
CopyToRange:=Range("DG1:HJ1"), Unique:=False

'select the data to be transpose
Set rng = Range("DG1").CurrentRegion
rng.Copy

------ when i select all the items, the error will cause the line
below to be highlighted when i debug the code.
'transpose the data from rows to column
Range("A8").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Application.CutCopyMode = False


'get focus on the drop down list
Range("B4").Select

rng.clear

End If

End Sub


How do i solve this error? Any suggestions?

try recording a macro doing the same actions you want this to do, and
compare the code to see what you are doing differently.
granted, the recorded code will not be as efficient as what you've got
written here, but it's a good place to compare...........
:)
hth!
susan


On Jun 8, 3:18 am, kiwis wrote:
Hi

I enounter run-time error 1004 when i try to copy & paste my data to a
starting cell A8

Extract from visual basic
The information cannot be pasted because the copy area & the paste
area are not the same size & shape.

The code below works fine when i select an item BUT the error arises
when i choose to display all the items .

my code is residing in a worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
'allow user to choose which record to display

Dim x As Variant
Dim rng As Range

On Error Resume Next
x = Target.Validation.ErrorMessage

If Err.Number = 0 Then
On Error GoTo 0
Else
Call addval
End If

If Target.Row = 4 And Target.Column = 2 Then
'calculate criteria cell in case calculation mode is manual
Worksheets("summary").Range("B4").Calculate

Worksheets("raw").Range("data") _
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("summary").Range("B3:B4"), _
CopyToRange:=Range("DG1:HJ1"), Unique:=False

'select the data to be transpose
Set rng = Range("DG1").CurrentRegion
rng.Copy

------ when i select all the items, the error will cause the line
below to be highlighted when i debug the code.
'transpose the data from rows to column
Range("A8").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Application.CutCopyMode = False

'get focus on the drop down list
Range("B4").Select

rng.clear

End If

End Sub

How do i solve this error? Any suggestions?