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

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

2 replies

Best answer
Posts
2451
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
March 21, 2019
411
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

Say "Thank you" 1

A few words of thanks would be greatly appreciated. Add comment

CCM 3382 users have said thank you to us 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"?
Posts
2451
Registration date
Sunday September 12, 2010
Status
Contributor
Last seen
March 21, 2019
411
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!