You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- Macro to Cut and Paste from other sheet

Advertisement

QUESTION: Sorry Tom I have to resubmit this with a better illustration, please bear with me.

I have two sheets. The content of sheet 1 looks like this.

[A] [B] [C] [D]

[1] EmpID FName CaseID Count Case

[2] 416 Lorna 602172 12

[3] 412 Shaine 603107 6

[4] 654 Alice 603152 45

[5] 361 Roman 603082 23

[6] 412 Shaine 603118 45

and my second sheet looks like this.

[H] [I] [J] [K]

[12] EmpID FName CaseID Count Case

[13] 416 Lorna 602172 23

[14] 412 Shaine 603107 42

[15] 654 Alice 691152 13

I need a macro that will match the Emp ID and Case ID on both sheets. If found, the macro should CUT from Sheet 2 and paste in the next blank row of sheet 1, so for the example above, after running the macro the table on sheet 1 should look like this.

[A] [B] [C] [D]

[1]EmpID FName caseID Count Case

[2]416 Lorna 602172 12

[3]412 Shaine 603107 6

[4]654 Alice 603152 45

[5]361 Roman 603082 23

[6]412 Shaine 603118 45

[7]412 Shaine 603107 42

[8]416 Lorna 602172 23

And table on sheet2 should look like this.

[A] [B] [C] [D]

[12]EmpD FName CaseID Count Case

[13]654 Alice 691152 13

Last 2 rows on sheet on sheet1 and was removed in sheet 2 because they are a duplicate of the first 2 rows of sheet1.

lastly, how can I used this, sorry Tom I am not a vb savvy.

"as far as D12:D300 if you mean A12:D300, and the range is delimited by blank rows and columns, then you could do

set rSomething = Range("A12").CurrentRegion"

ANSWER: PJ,

I altered the macro so you can specify the sheet names and the 4 column multi row range on each sheet. (so that should make your second question about currentregion unnecessary)

Sub ABC()

Dim sh1 As Worksheet, sh2 As Worksheet

Dim r1 As Range, r2 As Range, r2e As Range, r2eRow As Range

Set sh1 = Worksheets("Sheet1") '<== specify 1st sheet name

Set sh2 = Worksheets("Sheet2") '<== specify 2nd sheet name

Set r2 = sh2.Range("H13:H15") '<== specify data on 2nd sheet; exclude headers

Set r1 = sh1.Range("A2:A6") '<== specify data on 1st sheet; exclude headers

r2.Offset(0, 4).Formula = "=if(Countifs(" & r1.Address(1, 1, xlA1, True) & "," & _

r2(1).Address(0, 0, xlA1, True) & "," & r1.Offset(0, 2).Address(1, 1, xlA1, True) & "," & _

r2(1).Offset(0, 2).Address(0, 0, xlA1, True) & ")>0,na(),"""")"

On Error Resume Next

Set r2e = r2.Offset(0, 4).SpecialCells(xlFormulas, xlErrors)

On Error GoTo 0

If Not r2e Is Nothing Then

Set r2eRow = r2e.EntireRow

Set r2eRow = Intersect(r2eRow, r2.Resize(1, 4).EntireColumn)

r2eRow.Copy r1(r1.Count).Offset(1, 0)

r2eRow.EntireRow.Delete

End If

r2.Offset(0, 4).ClearContents

End Sub

That was tested with your data and worked for me.

I delete entire rows so that could be a drawback if you have other data on the same row as a row being deleted (in the second sheet).

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: One last question Tom, what if I want to change the match criteria? instead of emp Id, I want to match it with Fname, basically from a different column.

I am trying to decipher your macro but its really hard for me.

What do I need to modify?

PJ,

You would need to change this formula.

r2.Offset(0, 4).Formula = "=if(Countifs(" & r1.Address(1, 1, xlA1, True) & "," & _

r2(1).Address(0, 0, xlA1, True) & "," & r1.Offset(0, 2).Address(1, 1, xlA1, True) & "," & _

r2(1).Offset(0, 2).Address(0, 0, xlA1, True) & ")>0,na(),"""")"

would be adjusted to identify the match using the specified columns. For Fname and CaseID:

r2.Offset(0, 4).Formula = "=if(Countifs(" & r1(1).Offset(0,1).Address(1, 1, xlA1, True) & "," & _

r2(1).Offset(0,1).Address(0, 0, xlA1, True) & "," & r1.Offset(0, 2).Address(1, 1, xlA1, True) & "," & _

r2(1).Offset(0, 2).Address(0, 0, xlA1, True) & ")>0,na(),"""")"

this builds a formula like

=IF(countifs(Sheet1!$B$2:$B$6,Sheet2!I13,Sheet1!$C$2:$C$6,Sheet2!J13)>0,na(),"")

in cell L13 and drag fills it down to L15

This looks for a match for Fname and CaseID. So when a match is found, this formula returns a #N/A and when not it returns a "" and the cell looks Blank. This allows the code to identify the matching rows by using column L and doing a Goto Special then choosinge formula and error value.

[this is untested, so I could have made a typo - but you should get the idea]

So you would design your formula to make the match you want.

--

Regards,

Tom Ogilvy

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.