How do I create a macro to transfer a row of information from "Data Input" sheet to "History" sheet

[charlies_angel]

Hi,
Here is my spreadsheet scenario:
(MS Office 2013)

"Data Input Sheet" has approx. 80 rows, each one is designated to individual pieces of rental equipment (for example; Row 6 is for Unit 202 info, Row 7 is for Unit 203 info, etc)
Once the required data is entered, completion is indicated by a green checkmark icon (as well as a "100")

I need a macro (button) that will ONLY allow a row to be transferred from "Data Input Sheet" to the "History Sheet" when that condition (green checkmark, or 100) is met, as well as only copy/paste the cell values (I do not want my formulas from the data input sheet to end up in the history) I also need the row on "Data Input Sheet" to clear so the next rental's info may be entered.

The "History Sheet" needs to accept any of the 80 rows from "Data Input Sheet" (as selected by user) and it is preferred if the most recent completions can be transferred into the top of the "History Sheet", continuously bumping the older ones downward.

It would also be great if I could double the cell with the green checkmark as my macro button location- but I'm not sure if that would cause the formula already within that cell to not function??


Thank you for any help you can offer!!

 

[charlies_angel]

Hi Can anyone correct this macro for me? I'm new to VBA, and hoped I would be able to add an 'IF' function to the beginning of the macro once it was recorded- but I get a runtime error '13' Type Mismatch (red text in the code below)
What I need it to do, is copy a row from "Current Rentals" sheet, into "Rental History" sheet, ONLY if the words "Rental Complete" are found in column 'AJ' (this column is also named "Completion_Status").
I also need to clear data from "Current Rentals" cells G:P and X:AI when finished so new data can be entered.

The code I have so far:




Sub Rental_Complete__Move_To_History()


'
' Rental_Complete__Move_To_History Macro
'


'
If Range("Completion_Status") = "Rental Complete" Then
ActiveWindow.SmallScroll Down:=-78
ActiveWindow.SmallScroll ToRight:=-2
ActiveWindow.SmallScroll Down:=-3
ActiveWindow.SmallScroll ToRight:=-21
Range("A6:AJ6").Select
Selection.Copy
Sheets("Rental History").Select
Range("A6").Select
ActiveCell.Range("A1,A3").Select
ActiveCell.Offset(2, 0).Range("A1").Activate
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveCell.Offset(0, 40).Range("A1").Select
Sheets("CURRENT RENTALS").Select
ActiveWindow.SmallScroll ToRight:=-28
Range("G6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = ""
Range("H6").Select
ActiveCell.FormulaR1C1 = ""
Range("I6").Select
ActiveCell.FormulaR1C1 = ""
Range("J6").Select
ActiveCell.FormulaR1C1 = ""
Range("K6").Select
ActiveCell.FormulaR1C1 = ""
Range("L6").Select
ActiveCell.FormulaR1C1 = ""
Range("M6").Select
ActiveCell.FormulaR1C1 = ""
Range("N6").Select
ActiveCell.FormulaR1C1 = ""
Range("O6").Select
ActiveCell.FormulaR1C1 = ""
Range("P6").Select
ActiveCell.FormulaR1C1 = ""
Range("X6").Select
ActiveWindow.SmallScroll ToRight:=8
ActiveCell.FormulaR1C1 = ""
Range("Y6").Select
ActiveCell.FormulaR1C1 = ""
Range("Z6").Select
ActiveCell.FormulaR1C1 = ""
Range("AA6").Select
ActiveCell.FormulaR1C1 = ""
Range("AB6").Select
ActiveCell.FormulaR1C1 = ""
Range("AC6").Select
ActiveCell.FormulaR1C1 = ""
Range("AD6").Select
ActiveCell.FormulaR1C1 = ""
Range("AE6").Select
ActiveCell.FormulaR1C1 = ""
Range("AF6").Select
ActiveCell.FormulaR1C1 = ""
Range("AG6").Select
ActiveCell.FormulaR1C1 = ""
Range("AH6").Select
ActiveCell.FormulaR1C1 = ""
Range("AI6").Select
ActiveCell.FormulaR1C1 = ""
Range("AJ6").Select
ActiveWindow.SmallScroll ToRight:=3
ActiveWorkbook.Save
Else
End If
End Sub