Locate non-blanks in a column and copy certain data from the row

[GCC83]

I'm trying to create a piece of macro code that will pick up all populated cells in a column (E10-E84), and copy both the value in that cell and the value on the same row in column A. I'd like the info from column A to be pasted into column A in an entirely different workbook, and the corresponding data from column E from the original worksheet to go into column B of the new worksheet. Data from the same row in the first spreadsheet needs to remain on the same row in the second. There is also information beyond row E84 which I do not want.

Here is what I've cobbled together so far, but it currently doesn't pick up any data which is a fairly big flaw!:
Sub MoveEM()
Dim wb1 As Workbook
Dim wb2 As Workbook
Dim rng1 As Range
Dim rng2 As Range
Workbooks.Open Filename:="Z:\DPC MI Folder\Copy of RPHD 2013-2014.xls"
Set wb1 = Excel.ActiveWorkbook
Workbooks.Open Filename:="Z:\DPC MI Folder\Checking Workbook.xls"
Set wb2 = Excel.ActiveWorkbook
On Error Resume Next
Set rng1 = "wb1.Range(E10 - E84).SpecialCells(xlConstants)"
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub
Application.ScreenUpdating = False
Set rng2 = wb2.[b1]
rng1.Copy
rng2.PasteSpecial xlPasteValues
'copy column I to Output C2
rng1.Offset(0, 7).Copy
rng2.Offset(0, 1).PasteSpecial xlPasteValues
'copy column N to Output d2
rng1.Offset(0, 12).Copy
rng2.Offset(0, 2).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
End Sub

The 'offset' lines of code are lifted from another macro and I've not yet attempted to tailor them to for my needs until I'm in a position to test them, but you get the idea.

The workbooks open fine and it does not seem to fail at any particular point, but it's not doing what I want either. Can anybody confirm if I'm on the correct path with this or where I'm going wrong?

Cheers,
Gary

 

[Sky Tucker]

GCC83,

You have a couple of problems here:

1) The reason it doesn't seem to fail at a particular point is because you have employed "On Error Resume Next," which is a poor practice and should be avoided whenever possible. If that were not there, you would receive an error when the code stopped working. My recommendation would be to remove this line and implement a proper error trap.

2) This line:

Set rng1 = "wb1.Range(E10 - E84).SpecialCells(xlConstants)"

should be:

Set rng1 = wb1.Range(E10:E84).SpecialCells(xlConstants)

When you put quotes around something, it makes it a string and you are trying to assign a range object, not a string. Also, the colon should be used in place of where you have a hyphen. Hopefully, this will help!