Copy and pasting ranges with VBA

[JenniferJones93]

Hello all!

I don't have any sample data to upload at the moment but the question is fairly straightforward I believe. I put a table below to help visualize what I'm looking for.

I have one worksheet with many ranges of the same size and I need a way to move one range to another range with a drop downs and a button.

So on sheet one I have data like this (The headers are merged so User One in this example would be A1:A2, etc.)

[TABLE="width: 500"]
<tbody>[TR]
[TD]User One[/TD]
[TD][/TD]
[TD]User Two[/TD]
[TD][/TD]
[TD]User Three[/TD]
[TD][/TD]
[TD]User Four[/TD]
[TD][/TD]
[TD]User Five[/TD]
[TD][/TD]
[TD]User Six[/TD]
[TD][/TD]
[TD]UserSeven[/TD]
[TD][/TD]
[TD]UserEight[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So on my other worksheet I wanted to select which user's data to move and which spot to move it to.

Example:
Move From: User 8
Move To: User 3

And then by running the macro O2:P10 would be cut or copied to E2:F10.

I could set each as a named range or something but I was having trouble figuring out how to call it from the cell's value to do what I want

 

[JenniferJones93]

Oops-- looks like I need data in the cells for the table to show.

[TABLE="width: 500"]
<tbody>[TR]
[TD]User 1[/TD]
[TD][/TD]
[TD]User 2[/TD]
[TD][/TD]
[TD]User 3[/TD]
[TD][/TD]
[TD]User 4[/TD]
[TD][/TD]
[TD]User 5[/TD]
[TD][/TD]
[TD]User 6[/TD]
[TD][/TD]
[TD]User 7[/TD]
[TD][/TD]
[TD]User 8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
[TR]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[TD]Data[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

Try this.

Run the macro, capture the user From, press OK, capture the user To and press Ok.

Sub Copy_and_pasting_ranges()
  Dim usr1 As Variant, usr2 As Variant, f As Range, uc As Long
  Dim colFrom As Long, colTo As Long
  usr1 = InputBox("Enter User From", "COPY RANGES")
  If usr1 = "" Then Exit Sub
  usr2 = InputBox("Enter User To", "COPY RANGES")
  If usr2 = "" Then Exit Sub
  Set f = Rows(1).Find(usr1, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox usr1 & " User does noes not exists", vbCritical
    Exit Sub
  Else
    colFrom = f.Column
  End If
  Set f = Rows(1).Find(usr2, , xlValues, xlWhole)
  If f Is Nothing Then
    MsgBox usr2 & " User does noes not exists", vbCritical
    Exit Sub
  Else
    colTo = f.Column
  End If
  '
  uc = Cells(1, Columns.Count).End(xlToLeft).Column + 3
  Columns(colFrom).Resize(, 2).Copy Columns(uc)
  Columns(colTo).Resize(, 2).Copy Columns(colFrom)
  Columns(uc).Resize(, 2).Copy Columns(colTo)
  Columns(uc).Resize(, 2).Clear
End Sub