Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Data from one group of cells to another
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Data from one group of cells to another
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Data from one group of cells to another
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Data from one group of cells to another
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Move Data from one group of cells to another
Hi Patrick worked a treat. Thanks
"Patrick Molloy" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
If condition to move a group of cells based on status | Excel Discussion (Misc queries) | |||
move cells based on group levels | Excel Discussion (Misc queries) | |||
Copy Data from One Group of Cells to Another Group | Charts and Charting in Excel | |||
WHEN I GROUP IT ERRORS WITH 'CANNOT MOVE CELLS OFF WORKSHEET?? | Excel Worksheet Functions | |||
Have VBA delete a group of cells, move information over, then add | Excel Discussion (Misc queries) |