PDA

View Full Version : Copy entire row



2s7
05-18-2015, 01:46 AM
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
05-18-2015, 03:55 AM
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
05-18-2015, 04:23 AM
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
05-18-2015, 05:21 AM
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
05-18-2015, 07:52 AM
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
05-18-2015, 01:14 PM
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
05-18-2015, 01:23 PM
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
05-18-2015, 01:32 PM
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
05-18-2015, 03:05 PM
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 (http://www.joelonsoftware.com/uibook/chapters/fog0000000065.html)

Pun intended :D

SamT
05-18-2015, 03:30 PM
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 ExplicitThis 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. :dunno

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

Paul_Hossler
05-18-2015, 04:58 PM
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
05-18-2015, 06:30 PM
So you are calling the Copy Method (Procedure) of a Range Object? Correct?

Paul_Hossler
05-18-2015, 09:24 PM
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 :rotlaugh: 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
05-19-2015, 06:53 AM
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. :D Later, (much,) I took a Pascal class at college. :) That was just for personal development.

Paul_Hossler
05-19-2015, 07:52 AM
You had 1's and 0's ???? You were lucky.

We only had 1's

SamT
05-19-2015, 08:06 AM
I thought I was bad off when I only had 1's. Then I met a man who only had 0's

mperrah
05-19-2015, 11:08 AM
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
05-19-2015, 01:08 PM
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!