Consulting

Results 1 to 10 of 10

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

  1. #1

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

    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

  2. #2
    VBAX Master Killian's Avatar
    Joined
    Nov 2004
    Location
    London
    Posts
    1,132
    Location
    Hi and welcome to VBAX

    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 example[VBA]Sub 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[/VBA]
    K :-)

  3. #3

    thanks

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

  4. #4
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  5. #5


    amazing dear lucas Sir,
    u r great
    thank you very much


  6. #6
    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

  7. #7
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    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.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  8. #8
    Moderator VBAX Wizard lucas's Avatar
    Joined
    Jun 2004
    Location
    Tulsa, Oklahoma
    Posts
    7,323
    Location
    Further testing....If you delete the first 2 rows on each sheet it works correctly which confirms what I posted earlier.
    Steve
    "Nearly all men can stand adversity, but if you want to test a man's character, give him power."
    -Abraham Lincoln

  9. #9
    Quote Originally Posted by lucas
    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

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •