Please help me Solve the macro

Closed
nksoni Posts 7 Registration date Thursday 8 July 2010 Status Member Last seen 19 July 2010 - 14 Jul 2010 à 01:55
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 - 18 Jul 2010 à 08:49
I have a sample copy of macro where i have different sheets named Data,High,Med,Low

In data i have sample regards with HIGH,MED,LOW in the colomn B1..and so on
What i wanted to achive is copy the data of High,Low or Med to thier respective sheets
im unable to fig out with this
The error states Invalid Invalid procedure call or argument (Error 5)
Im new to excel can someone please help me
Thanks




Sub Copyx()
Dim CompSelctor As Range
Dim i, j As Range
Dim DestSelector As Range
Sheets("Sheet").Select
Set CompSelctor = Range("B1", Range("B" & Rows.Count).End(xlUp))

For Each i In CompSelctor
If i.Value = "High" Then
i.EntireRow.Copy DestSelector
Sheets("High").Select
ActiveSheet.Paste Destination:=Worksheets("High").Range("A1:C3")
Set DestSelector = DestSelector.Offset(1)

ElseIf i.Value = "Med" Then
i.EntireRow.Copy DestSelector
Sheets("Med").Select
ActiveSheet.Paste Destination:=Worksheets("Med").Range("A1:C3")
Set DestSelector = DestSelector.Offset(1)

ElseIf i.Value = "Low" Then
i.EntireRow.Copy DestSelector
Sheets("Low").Select
ActiveSheet.Paste Destination:=Worksheets("Low").Range("A1:C3")
Set DestSelector = DestSelector.Offset(1)

End If
Next i

End Sub

2 responses

nksoni Posts 7 Registration date Thursday 8 July 2010 Status Member Last seen 19 July 2010
14 Jul 2010 à 01:56
Sorry "Sheet" the name instead of "Data"
rizvisa1 Posts 4478 Registration date Thursday 28 January 2010 Status Contributor Last seen 5 May 2022 766
18 Jul 2010 à 08:49
You can record a macro
What you need to do is filter the master sheet on the value and copy the visible rows and paste it to the sheet for which you filtered the data

There is a solution for this that was provided few weeks back, if you search you should be able to locate it