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

samdk

New member
Joined
Dec 14, 2018
Messages
13
Reaction score
0
Points
1
Location
Paris
Excel Version(s)
O365
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.

VBA moving rows.jpg


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:
[COLOR=#242729][FONT=Arial]Sub CopyRows()[/FONT][/COLOR]
[COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] c [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Range
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Dim[/FONT][/COLOR][COLOR=#303336][FONT=inherit] x [/FONT][/COLOR][COLOR=#101094][FONT=inherit]As[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Integer[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
FindString[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Feuil1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"B2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value
CodeF[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"H"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Rows[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Count[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#303336][FONT=inherit]xlUp[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Row[/FONT][/COLOR][COLOR=#303336][FONT=inherit]:[/FONT][/COLOR][COLOR=#303336][FONT=inherit] x [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]8[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]For[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Each[/FONT][/COLOR][COLOR=#303336][FONT=inherit] c [/FONT][/COLOR][COLOR=#101094][FONT=inherit]In[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"H1:H"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] CodeF[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit] c[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Value [/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] Sheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Feuil1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"B2"[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Then[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
c[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]EntireRow[/FONT][/COLOR][COLOR=#303336][FONT=inherit].[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Copy Worksheets[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"Feuil1"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]).[/FONT][/COLOR][COLOR=#303336][FONT=inherit]Range[/FONT][/COLOR][COLOR=#303336][FONT=inherit]([/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]"A"[/FONT][/COLOR][COLOR=#303336][FONT=inherit]&[/FONT][/COLOR][COLOR=#303336][FONT=inherit] x[/FONT][/COLOR][COLOR=#303336][FONT=inherit])[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
x[/FONT][/COLOR][COLOR=#303336][FONT=inherit]=[/FONT][/COLOR][COLOR=#303336][FONT=inherit] x [/FONT][/COLOR][COLOR=#303336][FONT=inherit]+[/FONT][/COLOR][COLOR=#7D2727][FONT=inherit]1[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
    [/FONT][/COLOR][COLOR=#101094][FONT=inherit]End[/FONT][/COLOR][COLOR=#101094][FONT=inherit]If[/FONT][/COLOR][COLOR=#303336][FONT=inherit]
[/FONT][/COLOR][COLOR=#101094][FONT=inherit]Next[/FONT][/COLOR][COLOR=#303336][FONT=inherit] c
[/FONT][/COLOR][COLOR=#242729][FONT=Arial]End Sub
[/FONT][/COLOR]
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 a moderator:
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?
 
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.
 
Here's te file :)
 

Attachments

  • Test VBA coding.xlsm
    21.5 KB · Views: 19
Does this do what you're after ?
 

Attachments

  • samdk_Test VBA coding.xlsm
    30.5 KB · Views: 35
Works perfectly!!! :peace:
Thanks a lot!

Cheers,
Sam
 
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
 
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."
 
Back
Top