MrExcel.com - Your One Stop for Excel Tips & Solutions

Moving rows to a sheet

Posted by David McArthur on November 20, 2001 9:37 PM

I have one sheet where the user inputs data, I would like a macro to send that data to another sheet. Then the user may re-enter data and then send that to the sheet, incremented by 1 line. So in the end i have a sheet containing all the data the user enetered.

Posted by Lewis on November 21, 2001 3:49 AM


The following takes data from cells D4,D6, D8, G4 G6 etc through to G24 on the sheet called Form
and tranfers it into the next available row on the sheet called Records.
Clears the data entry cells then saves the workbook.
I am sure you will be able to modify this to suit your needs.

Good luck
Sub CopyToTraining()
'
' CopyToTraining Macro
' Macro recorded 12/11/2001 by Lewis Conquer
'
' CopyToTraining Macro
' Macro recorded 09/11/2001 by Lewis Conquer
'
' Keyboard Shortcut: Ctrl+t
'
Range("A1").Select

Sheets("Form").Select

Range("D4").Select
Selection.Copy
Sheets("Records").Select
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("D6").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("D8").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G4").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G6").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G8").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G10").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G12").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G14").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G16").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G18").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G20").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G22").Select
Selection.Copy
Sheets("Records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
Range("G24").Select
Selection.Copy
Sheets("records").Select
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=xlValues

Sheets("Form").Select
ActiveWorkbook.Save


Application.CutCopyMode = False
Range("D4").Select
Selection.ClearContents
Range("D6").Select
Selection.ClearContents
Range("D8").Select
Selection.ClearContents
Range("G4").Select
Selection.ClearContents
Range("G6").Select
Selection.ClearContents
Range("G8").Select
Selection.ClearContents
Range("G10").Select
Selection.ClearContents
Range("G12").Select
Selection.ClearContents
Range("G14").Select
Selection.ClearContents
Range("G16").Select
Selection.ClearContents
Range("G18").Select
Selection.ClearContents
Range("G20").Select
Selection.ClearContents
Range("G22").Select
Selection.ClearContents
Range("G24").Select
Selection.ClearContents


Sheets("Form").Select
ActiveWorkbook.Save

Range("D4").Select


End Sub


'



Lewis Conquer Consultancy Services