Copy rows from another Excel-file Solved/Closed

Question

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!



Configuration: Windows XP / Firefox 3.6.10

TrowaD · Answer

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

TrowaD · Answer

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

Copy rows from another Excel-file

2 responses

Subscribe To Our Newsletter!