Results 1 to 8 of 8

Thread: VBA code for moving rows from/to multiple sheets if meets condition

  1. #1
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    5
    Articles
    0
    Excel Version
    2013

    Question VBA code for moving rows from/to multiple sheets if meets condition



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm quite new to the VBA coding and I like it, so I would love to learn more.
    For serveral days I have been trying to figure out how to solve my problem. Unfortunately, the found answers could only respond to part of it, but never succeed in really adapting them.
    I have 5 databases in different sheets (A, B, C, D and E). The only common information is the column H in each one. And also this value is a unique one.
    On the first sheet I would like to move the entire row from one of these sheets if the value from B2 is found in column H. And after maybe changing some information to be able to move it back on the same sheet, but at the end of the list.

    Click image for larger version. 

Name:	VBA moving rows.jpg 
Views:	22 
Size:	75.9 KB 
ID:	8720


    This is what I was able to do so far (it is only copying the row - but I would like to move it - also I can't get it to search the B2 value in another sheet if is not found in sheet A):

    Code:
    Sub CopyRows()
    Dim c As Range
    Dim x AsInteger
    FindString= Sheets("Feuil1").Range("B2").Value
    CodeF= Sheets("A").Range("H"& Rows.Count).End(xlUp).Row: x =8
    ForEach c In Sheets("A").Range("H1:H"& CodeF)
        If c.Value = Sheets("Feuil1").Range("B2")Then
    c.EntireRow.Copy Worksheets("Feuil1").Range("A"& x)
    x= x +1
        EndIf
    Next c
    End Sub
    
    Hope my description is clear and that something like that can be done with the VBA coding.
    Many thanks in advance for your time!
    Kind regards.
    Sam

    PS: VLookup formula won't work, as I might change some values apart the column H in that specific row.
    Last edited by AliGW; 2018-12-14 at 01:09 PM. Reason: Code tags added.

  2. #2
    Seeker gue's Avatar
    Join Date
    Nov 2018
    Posts
    19
    Articles
    0
    Excel Version
    Office Professional Plus 2016

    Question

    would be easier if you also attach your file and a sample value with expected result.

    additional questions:
    • is there always only one value which can be found? - means you will only search unique values?
    • may the value be available on more than one sheet?
    • you always will modify found result(s)? should unchanged string also copied back?
    • how do you check if modification results in a new unique value?

  3. #3
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    5
    Articles
    0
    Excel Version
    2013
    Hi Gue,


    Thnaks a lot for your reply and sorry for the delayed feedback.


    Here are my answers to your questions:
    is there always only one value which can be found? - means you will only search unique values?
    => Yes, the value in the B2 cell always will be an unique one (changed as needed), it can be found only once in the whole worksheet.


    may the value be available on more than one sheet?
    => No, as it's a unique one, it can be found only in one of these sheets.


    you always will modify found result(s)?
    => Not necessarily, sometimes I might not have to change the information in that specific row.


    should unchanged string also copied back?
    => Yes. No matter what, I have to get it back to the sheet from where it came.


    how do you check if modification results in a new unique value?
    => There will never be changes in the unique value.


    Thanks a lot for your time.


    Cheers,
    Sam


    PS: I'm trying to upload an excel file here, so it can be easier to undertsand.

  4. #4
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    5
    Articles
    0
    Excel Version
    2013

    Smile

    Here's te file
    Attached Files Attached Files

  5. #5
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Does this do what you're after ?
    Attached Files Attached Files

  6. #6
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    5
    Articles
    0
    Excel Version
    2013
    Works perfectly!!!
    Thanks a lot!

    Cheers,
    Sam

  7. #7
    Seeker samdk's Avatar
    Join Date
    Dec 2018
    Location
    Paris
    Posts
    5
    Articles
    0
    Excel Version
    2013
    Happy New Year everyone!!!

    I adapted the codes to my file, it works as expected.
    Thanks again.

    I also would like to make a MsgBox appear in case no match is found for the B2 value. I did change something in this code, but the message appears 5 times (for each sheet). How can I make it appear only once?

    Cheers,
    Sam

  8. #8
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    769
    Articles
    0
    Excel Version
    Excel 2010 64bit
    Try this line between Next i and End Sub in the CopyRows macro
    Code:
        If i = 6 Then MsgBox "The value in B2 was not found."

Tags for this Thread

Posting Permissions

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