Copy selected cells from one worksheet to selected cells in another worksheet

I have a database of names and addresses with header rows labeled
Last Name – First Name - Address 1 – Address 2 – Post Code - Telephone etc.
Starting at B1. The worksheet is called DB

What I am trying to achieve is to copy only certain cells from the row, these are

B2, D2, F2, I2, J2, K2, L2, M2

I want to copy the sells to another worksheet called RL (in the same workbook)
in this order.


B2 to E13
D2 to D13
F2 to F12
I2 to F13
J2 to F14
K2 to F15
L2 to G12
M2 to G14

I then want to move down the rows in DB copying the cells to their equivalent Cells in RL. So the second row of destinations in RL would be as follows.

B3 to E17
D3 to D17
F3 to F16
I3 to F17
J3 to F18
K3 to F19
L3 to G16
M3 to G18

I have achieved something like this using a lot of macros via the recorder for one line, but have been unable to write it correctly in VBA to move down the rows.

I am using Office 2011 for Mac with Yosemite 10.10.1

Can anyone help help?

Please Login or Register  to view this content.

Hi rcm

Thank you for your very fast response. I have now had a chance to run the code, it worked really well and was so fast. I did make a couple of tweaks as some data went to the wrong cells maybe not surprising with the confusing information I gave you. Anyway the code looks like this now
ub move()


Dim cr(10, 2), cx(10)
Dim db, rl As Worksheet

Set db = Sheets("DB")
Set rl = Sheets("RL")


cr(1, 1) = "e"
cr(1, 2) = "B"
cx(1) = 0

cr(2, 1) = "d"
cr(2, 2) = "d"
cx(2) = 0

cr(3, 1) = "f"
cr(3, 2) = "f"
cx(3) = -1

cr(4, 1) = "f" 'changed these round
cr(4, 2) = "i"
cx(4) = 0

cr(5, 1) = "f" 'changed these round
cr(5, 2) = "j"
cx(5) = 1

cr(6, 1) = "f" 'Changed these around
cr(6, 2) = "k"
cx(6) = 2

cr(7, 1) = "g" ' changed these round
cr(7, 2) = "l"
cx(7) = -1

cr(8, 1) = "g"
cr(8, 2) = "m"
cx(8) = 1 'changed from 2


'B2 to E13
'D2 to D13
'F2 to F12
'I2 to F13
'J2 to F14
'K2 to F15
'L2 to G12
'M2 to G14




r2 = 13

rmax = db.Range("B" & Rows.Count).End(xlUp).Row
For r = 2 To rmax
For j = 1 To 8
rl.Range(cr(j, 1) & r2 + cx(j)) = db.Range(cr(j, 2) & r)
Next j
r2 = r2 + 5
Next r



End Sub

I did not explain fully in my original question but I need to sort the database with selected rows first. The result when I run the code is to collect all the data from the top of the database until the last row of the selection. I couldn't figure out how change the coding to just copy the selected rows which I assume is to do with the line beginning rmax =. Would you be able to help me with this? Many thanks once again.

Peter

If a selection is all you need, try this..

place the selection range in rl.cells(1,1) "A1" and rl.cells(1,2) "B1"
introduce rmin to hold the starting row in db
rmax would hold the contents of rl.cells(1,2)

Please Login or Register  to view this content.

Hi rcm

I added the new code but it stops at the line For r = rmin To rmax. The message is Run-time error 13, Type mismatch.

Any ideas?

Ill work on it

Please Login or Register  to view this content.

I am still having the same problem with the new code also stopping at
For r = 2 To rmax
I thought it may be helpful to show a screen shot of each worksheet.

On the DB Worksheet I select the rows I need by marking them with a y in column A and sort ascending and it is these rows I need to copy to the RL Worksheet. I have given the database a Named Range call BHSDyn
Which refers to =OFFSET(DB!$B$1,1,0,COUNTA(DB!$B:$B)-1,13)


DB Screen Shot.pngRL Screen Shot.png