Move Data from one group of cells to another

[David Ryan]

Hi Guys

I have an excel spreadsheet that contains data in the following format.
row 5 has in column A a row heading, row 6 another column heading etc down
36 rows.

I want to be able to move data along the row leaving the row heading and
paste it into another row again leaving the row heading. ie cut and paste.

the problem is multiple people will be using this spreadsheet with limited
PC/Excel skills.

So i would like to be able to prompt the user to say move the data from row
5 and paste it into row 34 using an input box.

eg if you wanted to move the data from row 5 it would be B5:H8 then paste it
into row 34 into B34:H34. With the user just entering 5 for "row 5" in the
from input and entering 34 for "row34" for the to input, the field range is a
constant its just the row that changes.

hope that makes sense, and that someone can help

 

[Patrick Molloy]

in my example the FROM row number is in C1 and the TO row number is in C2.
Mytable starts in E and is 11 columns

Option Explicit

Sub MoveData()
Dim FROMrow As Long
Dim TOrow As Long

FROMrow = Range("C1")
TOrow = Range("C2")

Cells(FROMrow, "E").Resize(, 11).Copy
Cells(TOrow, "E").PasteSpecial xlPasteAll
Application.CutCopyMode = False

End Sub

 

[David Ryan]

Hi Patrick
Got a compile error.

although this look like it will work.

next question if i may, how do i get which row to apply this to, from a user
input box?

 

[Patrick Molloy]

I thought using tow cells would be easiest

amended:

Option Explicit
Sub MoveData()
Dim FROMrow As Long
Dim TOrow As Long

FROMrow = InputBox("FROM row:", "COPY DATA FROM...")
TOrow = InputBox("TO row:", "COPY DATA TO...")

Cells(FROMrow, "E").Resize(, 11).Copy
Cells(TOrow, "E").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Cells(FROMrow, "E").Resize(, 11).ClearContents
End Sub


This code should be fine - What is the compile error? My code comes from my
Excel 2003 test worksheet.

 

[David Ryan]

Hi Patrick worked a treat. Thanks

I thought using tow cells would be easiest

amended:

Option Explicit
Sub MoveData()
Dim FROMrow As Long
Dim TOrow As Long

FROMrow = InputBox("FROM row:", "COPY DATA FROM...")
TOrow = InputBox("TO row:", "COPY DATA TO...")

Cells(FROMrow, "E").Resize(, 11).Copy
Cells(TOrow, "E").PasteSpecial xlPasteAll
Application.CutCopyMode = False
Cells(FROMrow, "E").Resize(, 11).ClearContents
End Sub


This code should be fine - What is the compile error? My code comes from my
Excel 2003 test worksheet.