You are here:

Excel/Macro to Cut and Paste from other sheet

Advertisement


Question
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?

Answer
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  
About Excel
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

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

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.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.