Solved: based on Cell/Column content ,cut 1 sheet's row values and paste it in other sheet?

[mindpeace]

based on Cell/Column content ,cut one sheet's values and paste it in other sheet?

Dear All,
This is the problem haunting me for long time , i am very expert in VBA . hope some will give me solution

Have workbook with call status , with 15 columns 11th column give the status of the sales call .

I want to create automated macro which will copy row data based on status cell content to other sheet with same name as in status cell.
Suppose you have status ?pending? in cell , it should cut that cell and paste in worksheet named ?pending? , same in pending sheet status of complete row goes to complete sheet.

I have seen this working but I don?t remember the exact website.

Help will be well appreciated

[Killian]

Hi and welcome to VBAX :hi:

The Copy method for a range can optionally have a "Destination" where you can define the sheet & range it goes to.
I assumed you want to process a number of rows, so I've put this in a loop, processing the first 25 rows as an exampleSub ProcessRows()

Dim lngRowSource As Long
Dim lngRowTarget As Long
Dim strStatus As String

For lngRowSource = 1 To 25
strStatus = ActiveSheet.Cells(lngRowSource, 11).Value
If strStatus <> "" Then
lngRowTarget = TargetRow(Sheets(strStatus))
ActiveSheet.Range(Cells(lngRowSource, 1), Cells(lngRowSource, 15)).Copy _
Sheets(strStatus).Cells(lngRowTarget, 1)
End If
Next

End Sub

Function TargetRow(ws As Worksheet) As Long
'function to return the row index of the first empty row
Dim lngLastRow As Long
lngLastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
If IsEmpty(ws.Cells(lngLastRow, 1)) Then
TargetRow = 1
Else
TargetRow = lngLastRow + 1
End If
End Function

[mindpeace]

but can u explain how can i use this code ,.....
sorry but i am bit new to this ..
please help

[lucas]

try this workbook set up with killians code...I changed the targetrow funtion to look in column K in case you have no values in column 1 which is the way he has it set up in the code above.

[mindpeace]

:clap::clap:

amazing dear lucas (http://vbaexpress.com/forum/member.php?u=223) Sir,
u r great
thank you very much

:friends:

[mindpeace]

Dear Lucas sir ,
please refer to the attached file , i tried your code but it is not working please please help me


have great time

[lucas]

Hi mindpeace,
after only a quick look at the file I have to tell you that I think its the merged cells that are causing your problem.....I think the code is being confused by the change in the column structure. Its much better to right click on the cells, click on format and under the alignment tab on horizontal text alignment choose "center across selection" try that.

I see you changed the code some but I don't think what you did has anything to do with your current problem.

You need to thank Killian for his brilliant piece of code, not me.....I am glad to help you get it working though in killians absense.

[lucas]

Further testing....If you delete the first 2 rows on each sheet it works correctly which confirms what I posted earlier.

[mindpeace]

Further testing....If you delete the first 2 rows on each sheet it works correctly which confirms what I posted earlier.

hi,
thanks you and kalliance also ..i am trying .....want to learn these things can you refer any book
...

have great time

[mindpeace]

yes it works but i want to person to feel easy for data entry can it leave first 2 rows and copy other data