Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
Hello..
I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
Not sure why you would want to save to the 1st row.
The procedure that you listed, finds the last used row, then inserts the new data to the row offset by 1 row. If you have no data, the procedure will use the 1st row. If its that you want to insert Column headings for your data, you could use With historyWks.Range("A1:F1") .Value = VBA.Array("Title1", "Title2", "Title3", "Title4", "Title5", "Title6") .Font.Bold = True End With HTH "kyoshirou" wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
Firstly, if new record has been inserterd, i feel that is better to save on
the 1st row for easy reference. Steve, so is it possible to finds the last used row, then inserts the new data to the first row? "steve_doc" wrote: Not sure why you would want to save to the 1st row. The procedure that you listed, finds the last used row, then inserts the new data to the row offset by 1 row. If you have no data, the procedure will use the 1st row. If its that you want to insert Column headings for your data, you could use With historyWks.Range("A1:F1") .Value = VBA.Array("Title1", "Title2", "Title3", "Title4", "Title5", "Title6") .Font.Bold = True End With HTH "kyoshirou" wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
Theoretically you wouldn't need to find the last row if you plan to insert on
the first row The following will insert a new row after your column headings(assuming you have Column headings) With ws.Range("A1:J1") ..Offset(1, 0).Rows.Insert End With HTH "kyoshirou" wrote: Firstly, if new record has been inserterd, i feel that is better to save on the 1st row for easy reference. Steve, so is it possible to finds the last used row, then inserts the new data to the first row? "steve_doc" wrote: Not sure why you would want to save to the 1st row. The procedure that you listed, finds the last used row, then inserts the new data to the row offset by 1 row. If you have no data, the procedure will use the 1st row. If its that you want to insert Column headings for your data, you could use With historyWks.Range("A1:F1") .Value = VBA.Array("Title1", "Title2", "Title3", "Title4", "Title5", "Title6") .Font.Bold = True End With HTH "kyoshirou" wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
u'r right. Everything need to search from top to bottom.
I can have it search from top to bottom also. But is it possible to have the new values on top? That what i trying to create. "steve_doc" wrote: Theoretically you wouldn't need to find the last row if you plan to insert on the first row The following will insert a new row after your column headings(assuming you have Column headings) With ws.Range("A1:J1") .Offset(1, 0).Rows.Insert End With HTH "kyoshirou" wrote: Firstly, if new record has been inserterd, i feel that is better to save on the 1st row for easy reference. Steve, so is it possible to finds the last used row, then inserts the new data to the first row? "steve_doc" wrote: Not sure why you would want to save to the 1st row. The procedure that you listed, finds the last used row, then inserts the new data to the row offset by 1 row. If you have no data, the procedure will use the 1st row. If its that you want to insert Column headings for your data, you could use With historyWks.Range("A1:F1") .Value = VBA.Array("Title1", "Title2", "Title3", "Title4", "Title5", "Title6") .Font.Bold = True End With HTH "kyoshirou" wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
I dont understand
Your question was how to insert new data in the top row as opposed to finding the last used row and inserting the data in 1 row after the last row. with the previous method that I posted your most recent data will always be in the top most column, ergo newest oldest order. Perhaps a more detailed explanation of what you are trying to achieve? "kyoshirou" wrote: u'r right. Everything need to search from top to bottom. I can have it search from top to bottom also. But is it possible to have the new values on top? That what i trying to create. "steve_doc" wrote: Theoretically you wouldn't need to find the last row if you plan to insert on the first row The following will insert a new row after your column headings(assuming you have Column headings) With ws.Range("A1:J1") .Offset(1, 0).Rows.Insert End With HTH "kyoshirou" wrote: Firstly, if new record has been inserterd, i feel that is better to save on the 1st row for easy reference. Steve, so is it possible to finds the last used row, then inserts the new data to the first row? "steve_doc" wrote: Not sure why you would want to save to the 1st row. The procedure that you listed, finds the last used row, then inserts the new data to the row offset by 1 row. If you have no data, the procedure will use the 1st row. If its that you want to insert Column headings for your data, you could use With historyWks.Range("A1:F1") .Value = VBA.Array("Title1", "Title2", "Title3", "Title4", "Title5", "Title6") .Font.Bold = True End With HTH "kyoshirou" wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
Why not just put the new entry at the bottom and then sort the history sheet by
the date/time (column A). Record a macro that sorts that worksheet, then add it to the bottom of the code (or just run it when you need it sorted). kyoshirou wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
how should the macro goes?
"Dave Peterson" wrote: Why not just put the new entry at the bottom and then sort the history sheet by the date/time (column A). Record a macro that sorts that worksheet, then add it to the bottom of the code (or just run it when you need it sorted). kyoshirou wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
What did you get when you recorded it when you did it manually?
kyoshirou wrote: how should the macro goes? "Dave Peterson" wrote: Why not just put the new entry at the bottom and then sort the history sheet by the date/time (column A). Record a macro that sorts that worksheet, then add it to the bottom of the code (or just run it when you need it sorted). kyoshirou wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
what do u mean?
(the new entry data still save inside the last row?) What did you get when you recorded it when you did it manually? kyoshirou wrote: how should the macro goes? "Dave Peterson" wrote: Why not just put the new entry at the bottom and then sort the history sheet by the date/time (column A). Record a macro that sorts that worksheet, then add it to the bottom of the code (or just run it when you need it sorted). kyoshirou wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With -- Dave Peterson -- Dave Peterson |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
I mean turn on the macro recorder and sort that worksheet.
Then stop the macro recorder. Then look at the code that you generated. kyoshirou wrote: what do u mean? (the new entry data still save inside the last row?) What did you get when you recorded it when you did it manually? kyoshirou wrote: how should the macro goes? "Dave Peterson" wrote: Why not just put the new entry at the bottom and then sort the history sheet by the date/time (column A). Record a macro that sorts that worksheet, then add it to the bottom of the code (or just run it when you need it sorted). kyoshirou wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
Do u mean Assign Macro?
From there, i can choose which macro to use? "Dave Peterson" wrote: I mean turn on the macro recorder and sort that worksheet. Then stop the macro recorder. Then look at the code that you generated. kyoshirou wrote: what do u mean? (the new entry data still save inside the last row?) What did you get when you recorded it when you did it manually? kyoshirou wrote: how should the macro goes? "Dave Peterson" wrote: Why not just put the new entry at the bottom and then sort the history sheet by the date/time (column A). Record a macro that sorts that worksheet, then add it to the bottom of the code (or just run it when you need it sorted). kyoshirou wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
InsertFirst
Nope. It's got nothing to do with assigning a macro.
Tools|Macro|Record New Macro Is where you'd find it. After you turn this on, your actions will be recorded. You'll be able to stop the recording and inspect that code. kyoshirou wrote: Do u mean Assign Macro? From there, i can choose which macro to use? "Dave Peterson" wrote: I mean turn on the macro recorder and sort that worksheet. Then stop the macro recorder. Then look at the code that you generated. kyoshirou wrote: what do u mean? (the new entry data still save inside the last row?) What did you get when you recorded it when you did it manually? kyoshirou wrote: how should the macro goes? "Dave Peterson" wrote: Why not just put the new entry at the bottom and then sort the history sheet by the date/time (column A). Record a macro that sorts that worksheet, then add it to the bottom of the code (or just run it when you need it sorted). kyoshirou wrote: Hello.. I have this code: As new data is save at the last row, how do i make it to save on the first row? Do i use FirstRow? Any website to teach? Thanks! Set inputWks = Worksheets("MAIN") Set historyWks = Worksheets("Data") With historyWks nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row End With With inputWks Set myRng = .Range(myCopy) If Application.CountA(myRng) < myRng.Cells.Count Then MsgBox "Please fill in all the cells!" Exit Sub End If End With -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|