Copy rows from another Excel-file [Solved/Closed]

JeS - Oct 20, 2010 at 02:36 AM - Latest reply:  JeS
- Oct 26, 2010 at 09:12 AM
Hello,

I have two Excel files, one containing data and one blank. I'm creating a macro in blank Excel to copy certain rows from the data-Excel. I want to copy only rows containing a certain values in column "B", for example rows containing values of "25", "34 and "42" but not "125 or "340" etc.. Also, all the copied data should be pasted to blank Excel wihtout empty rows.

What would be the simplest code to do this?

Thanks!



See more 

4 replies

Best answer
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 21, 2010 at 10:27 AM
1
Thank you
Hi JeS,

Take a look at this code:

Sub test()
Set MyRange = Workbooks("jes data.xls").Sheets("blad1").Range("B1:B100")
For Each cell In MyRange
If cell.Value = 25 Or cell.Value = 34 Or cell.Value = 42 Then
    cell.EntireRow.Copy
    Workbooks("jes blank.xls").Sheets("Blad1").Activate
    Range("B" & Rows.Count).End(xlUp).Offset(1, 0).EntireRow.PasteSpecial
End If
    Next
Application.CutCopyMode = False
End Sub

Please adjust the workbook, sheet and data range to suit you situation.

Note: both workbooks must be opened for the code to work.

Is this simple enough?

Best regards,
Trowa

Thank you, TrowaD 1

Something to say? Add comment

CCM has helped 1706 users this month

Thank you very much Trowa! This looks quite simple, I just have to adjust my files for this but I will try this later.. what's the purpose of row "Application.CutCopyMode = False"?
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Oct 26, 2010 at 09:01 AM
0
Thank you
Hi JeS,

Application.CutCopyMode = False is used to clear the Excel's memory.
When you don't use this, the last row that is copied remains in Excel's memory and can be copied again.

There is no penalty for not using this line, so feel free to run the code without this line. The easiest way to do this is to place a ' in front of the line to make it a comment and exclude it from the code.

Best regards,
Trowa
ok, this was very helpful! Many thanks Trowa!