Copy entire row

[2s7]

Hi,
How should I write a macro that will do something like this:
If value in column G of worksheetA is equal to value in column G of worksheetB then copy entire row (from worksheetA) to row (in worksheet2) where the same value appears.

[SamT]

How should I write a macro

First pick one sheet to act as the Master. I usually pick the one that will give the shortest loop, in this case, that would be the one with the fewest used Rows. Caveat: that sheet must be permanent, not one that will ever be replaced.

Since you don't want to be looping thru empty cells, you will need a LastRow function|code

Define the range to loop thru, probably Range("G1:G" & Cstr(LastRow)) and assign it to a Variable.

Inside the loop, (I would use "For Each Cel in VariableName,) You need to see if you can Find that value in the other sheet's Column("G:G").

If you Find a cell with the value, Copy the Cel.EntireRow to Found.EntireRow.Cells(1)
End the IF and loop again (Next Cel), otherwise just loop again

Instead of (Found.EntireRow.Cells(1),) you might use Sheets("B").Rows(Found.Row)

Your code goes in the Master Sheet's Code Page.

If you need detailed examples, let us know.

[2s7]

Well I'm totally noob in this whole VBA, I work mostly on macros that someone else's done, so if I could get some example how it should look like that would be great.

[MINCUS1308]

SamT,
I also assume that 2s7 will probably be replacing the non-master sheet occasionally.
How would you go about telling the macro which sheet in the workbook to check the master sheet against?

[Paul_Hossler]

The macro seems to logically follow what you would do manually

This is completely hard coded, and if it were to be distributed, then I'd add want to add more flexibility and error checking

Option Explicit

Sub MatchAndCopy()
    Dim wsFrom As Worksheet, wsTo As Worksheet
    Dim rFrom As Range, rTo As Range, rCell As Range
    Dim iFrom As Long
    
    Set wsFrom = ThisWorkbook.Worksheets("Sheet1")
    Set wsTo = ThisWorkbook.Worksheets("Sheet2")

    Set rTo = Nothing
    On Error Resume Next
    Set rTo = Intersect(wsTo.UsedRange, wsTo.Columns(7))
    On Error GoTo 0
    If rTo Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    For Each rCell In rTo.Cells
        iFrom = -1
        On Error Resume Next
        iFrom = Application.WorksheetFunction.Match(rCell.Value, wsFrom.Columns(7), 0)
        On Error GoTo 0
        
        If iFrom > 0 Then
            Call wsFrom.Rows(iFrom).Copy(rCell.EntireRow)
        End If
    
    Next
    Application.ScreenUpdating = True

End Sub

[mperrah]

Nice Paul,

I took a much longer route to achieve similar results on a previous project.
Not sure on the OP data, but if there are duplicate values, it uses the first row value for the second match.
I first tried string values and then numbers for the wsFrom to test.
It pulls the first match correctly but the second match it uses the first matches value to send to wsTo.
I would imagine the source would not likely have duplicates if the value is an id or unique value, so this observation is probably mute.

[Paul_Hossler]

Thanks

Not sure on the OP data, but if there are duplicate values, it uses the first row value for the second match.

If the 'From' has multiple lines of the 'To' this only copies the first

Easy enough to add a test if there's more that one matching value, and ask the user which one they want copied, but the original request was pretty simple, so I decided to worry about it another time

[mperrah]

True wisdom for sure.
I have a built in algorithm for making things more complicated then they need to be, just ask my wife...

[SamT]

SamT,
I also assume that 2s7 will probably be replacing the non-master sheet occasionally.
How would you go about telling the macro which sheet in the workbook to check the master sheet against?

2s7 stated that he was only using 2 sheets.

If you wanted to check various sheets, the simplest is by using an InputBox in your Procedure. No User typos allowed.

For a more User friendly method, I might call a UserForm with a (multiselect?) ComboBox that listed any possible sheets to compare.

For some excellent advice on design, read this series of articles by the Program Manager for Excel 5 and VBA: The Process of Designing a Product

Pun intended

[SamT]

Well I'm totally noob in this whole VBA, I work mostly on macros that someone else's done, so if I could get some example how it should look like that would be great.

This post refers to Pauls great example in post # 5

Option Explicit

This is so important, that if you don't have it at the top of all your code, put it there. Then, in VBA use the Tools menu >> Options >> Editor Tab and checkmark Auto Syntax Check. This will automatically insert that on all new code pages. I have all the boxes in the Code Settings Frame checked. I also prefer to use two as the Tab Width. YMMV.

Paul nicely used double line feeds to separate the different sections of code. In the top section, he has declared all the variables he uses.

The next two line section is where he has assigned the two sheets to variables.

In the third section, he is checking to make sure that the master sheet actually has a Column("G) AND assigning Column("G") to a variable OR stopping the Procedure.

Application.ScreenUpdating = False speeds up the Procedure by stopping Excel from rewriting the screen on every change in Cell Values. Note that you must always set Screen updating back to True before you exit the sub.

The next section, (For Each rCell,) is looking for Matching Values and setting iFrom to the row number of the matching value.

In the (If ifrom) section,... Well Paul is a better programmer than me, so you have to ask him why he is using Call in such an undocumented way.

"Next" starts the code over, (loops) at the "For Each" line.

[Paul_Hossler]

why he is using Call in such an undocumented way

Mostly habit / style / old dog<>new tricks


The keyword call really isn't undocumented -- see the screen shot from the Call online help

While 'Call' is optional, I find it easier to use and understand

Call Worksheets("Sheet1").Range("A1:D4").Copy (Worksheets("Sheet2").Range("E5"))

instead of

Worksheets("Sheet1").Range("A1:D4").Copy  destination:=Worksheets("Sheet2").Range("E5")

[SamT]

So you are calling the Copy Method (Procedure) of a Range Object? Correct?

[Paul_Hossler]

Correct, using the optional destination parameter of the method (screen shot)

Since I had the 'Call' keyword, I needed the ()'s

Call wsFrom.Rows(iFrom).Copy(rCell.EntireRow)

I probably could just do it this way (positional parameters)

wsFrom.Rows(iFrom).Copy rCell.EntireRow

or this way (named parameters)

wsFrom.Rows(iFrom).Copy destination:=rCell.EntireRow

IIRC there is some kind of internal difference between using Call and not, but I'm suffering from First Language Syndrome (FLS) -- that's the way my first computer language did it so I just got into the habit.


This works, but I find that the use of Call SubName(parm1, parm2) just seems more readable

        If iFrom > 0 Then
            wsFrom.Rows(iFrom).Copy rCell.EntireRow
        End If

[SamT]

That's a nice little treatise on Copy. And Call too.




IIRC, they kept Call around for older programmers.But yer a yung sprout. The first language I worked with only used 4 bits. Later, (much,) I took a Pascal class at college. That was just for personal development.

[Paul_Hossler]

You had 1's and 0's ???? You were lucky.

We only had 1's

[SamT]

I thought I was bad off when I only had 1's. Then I met a man who only had 0's

[mperrah]

My dad worked for IBM, he told me about a stack of punch cards that were the program needed to tell a computer to add a few numbers...
Getting more then half way through punching a card then making a miss punch - eesh, there's no white out for holes.
And then while feeding them into the machine,
a jam meant starting from the beginning. Its amazing how far we've come

[SamT]

In my life time, from a large basement full of one computer to a wrist watch with thousands of times the power.

Holy Dick Tracy!