Copy rows from another Excel-file

Solved/Closed
JeS - Oct 20, 2010 at 02:36 AM
 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!



2 replies

TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Oct 21, 2010 at 10:27 AM
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
1
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"?
0
TrowaD
Posts
2900
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
September 12, 2022
523
Oct 26, 2010 at 09:01 AM
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
0
ok, this was very helpful! Many thanks Trowa!
0