Adds values of 2 cells into 2 columns (usually below them).
I used these small macros a lot to add a daily of semi-daily logs to my daily spreadsheets, like daily weight, exercise logs, and other habits I want to track.
This macro helped in adding the new record at bottom of list then sort the list to make the newly added record at top.
You may change the order, and where to use. Just assign it to a command and enjoy.
Sub ANmAdd_ThenSort_2Columns(Column1, Column2, FromRow, ToRow, Optional Shee = "Active", Optional WB = "This", Optional ThenSort = 0, Optiona SortByCol = 1, Optional SortOrder = 1)
' Add row of two columns from C6:D6 to below area, then sort ascending or descendingly
' Column1 and Column2 has to be next to each other
' Example call: ANmAdd_ThenSort_2Columns "C", "D", 6, 7, "Sheet1", "MyWb.xlsm", 1, 1, 1)
' Needs CountColumnCells, SortArea1
'
If WB = "This" Then WB = ThisWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(WB).ActiveSheet.Name
NewRR = CountColumnCells(Column1, WB, Shee, FromRow) + FromRow ' NewRR = CountColumnCells("C", WB, Shee)
Workbooks(WB).Worksheets(Shee).Range(Column1 & NewRR).Value = Workbooks(WB).Worksheets(Shee).Range(Column1 & FromRow).Value
Workbooks(WB).Worksheets(Shee).Range(Column2 & NewRR).Value = Workbooks(WB).Worksheets(Shee).Range(Column2 & FromRow).Value
If ThenSort = 1 Then
' sort it
SortArea1 Column1 & ToRow & ":" & Column2 & NewRR, Column1 & ToRow, SortByCol, SortOrder, Shee ' SortArea1 "C7:D" & NewRR, "C7", 2, Shee
End If
DoEvents
Workbooks(WB).Worksheets(Shee).Range(Column1 & FromRow & ":" & Column2 & FromRow).ClearContents ' Workbooks(WB).Worksheets(Shee).Range("C6:D6").ClearContents
End Sub
' Add row of two columns from C6:D6 to below area, then sort ascending or descendingly
' Column1 and Column2 has to be next to each other
' Example call: ANmAdd_ThenSort_2Columns "C", "D", 6, 7, "Sheet1", "MyWb.xlsm", 1, 1, 1)
' Needs CountColumnCells, SortArea1
'
If WB = "This" Then WB = ThisWorkbook.Name
If Shee = "Active" Then Shee = Workbooks(WB).ActiveSheet.Name
NewRR = CountColumnCells(Column1, WB, Shee, FromRow) + FromRow ' NewRR = CountColumnCells("C", WB, Shee)
Workbooks(WB).Worksheets(Shee).Range(Column1 & NewRR).Value = Workbooks(WB).Worksheets(Shee).Range(Column1 & FromRow).Value
Workbooks(WB).Worksheets(Shee).Range(Column2 & NewRR).Value = Workbooks(WB).Worksheets(Shee).Range(Column2 & FromRow).Value
If ThenSort = 1 Then
' sort it
SortArea1 Column1 & ToRow & ":" & Column2 & NewRR, Column1 & ToRow, SortByCol, SortOrder, Shee ' SortArea1 "C7:D" & NewRR, "C7", 2, Shee
End If
DoEvents
Workbooks(WB).Worksheets(Shee).Range(Column1 & FromRow & ":" & Column2 & FromRow).ClearContents ' Workbooks(WB).Worksheets(Shee).Range("C6:D6").ClearContents
End Sub
Column1, Column2, FromRow, ToRow, Optional Shee = "Active", Optional WB = "This", Optional ThenSort = 0, Optiona SortByCol = 1, Optional SortOrder = 1
Example call: ANmAdd_ThenSort_2Columns "C", "D", 6, 7, "Sheet1", "MyWb.xlsm", 1, 1, 1)
Views 145
Downloads 41
CodeID
DB ID