Find solution
How to update row in gridview?

EXCEL Need Help with a macro that will start data on new row?

  • How do i create a macro that will copy the last row in a data table and then paste it onto the next empty row? This way when i "close week" in my program.. I automatically have a new row that will pull in the new weeks attendence numbers.. And this data table can grow a new row each week. The data currently spans B4 to I51 (however row is going to change week by week.. I guess that is where i get lost... I dont want to have to update macro each week to what the new last row is.. ) Anyway the macro I have is Sheets("Attend").Select Range("I51").Select Selection.Copy Range("B4").Select Range("B4:I51").End(xlUp).Offset(2, 0).Activate ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False I came up with this but clearly it doesnt work. And clearly wont work in the future because I51 wont be the end of my table .. it'll be constantly changing. Not sure I know what i'm doing up above... it probably doesnt make any sense. Could really use some help! Thanks! SO SO MUCH My job would really appreciate this!

  • Answer:

    Try something like this: Dim LastRow LastRow = Range("I" & Rows.Count).End(xlUp).Row To select the last row in column I: Range("I" & LastRow).Select To select the range B4 to the last row in column I: Range("B4:I" & LastRow).Select

John D at Yahoo! Answers Visit the source

Was this solution helpful to you?

Other answers

Dim sht1 As Worksheet, sht2 As Worksheet Set sht1 = Sheets("Attend") Set sht2 = Sheets("Some other sheet") sht1.Rows(sht1.[I65536].End(xlUp). Row).Copy Destination:=sht2. Rows(sht2.[B65536]. End(xlUp).Row + 1)

if I understand what you are asking, you want to copy the last row in your worksheet, and paste it on the next row? if that is the case here's what I'd do. Range(Cells(ActiveCell.SpecialCells(xl… 2), Cells(ActiveCell.SpecialCells(xlLastCell… 9)).Select Selection.Copy Cells(ActiveCell.SpecialCells(xlLastCe… + 1, 2).Select Selection.Paste

From what I can tell from your code and what you've said, it looks like you are just trying to copy the last row from your table and just copy it down to the next row in your worksheet. If that is the case, then here's a good way to do it: Sub Macro4() Dim MyLastRow As Long With Sheets("Attend") MyLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1 .Rows(MyLastRow).Copy Destination:=.Cells(MyLastRow + 1, 1) End With End Sub This copies the last row and pastes it to the next row in the same worksheet. If you are copying from one sheet to another here is how you'd do that: Sub Macro4() Dim MyLastRow As Long Dim MyLastRow2 As Long With Sheets("Attend") MyLastRow = .UsedRange.Row + .UsedRange.Rows.Count - 1 .Rows(MyLastRow).Copy Destination:=.Cells(MyLastRow + 1, 1) End With With Sheets("Sheet1") MyLastRow2 = .UsedRange.Row + .UsedRange.Rows.Count .Rows(MyLastRow).Copy Destination:=.Cells(MyLastRow2, 1) End With End Sub This second macro will take the last row from the Attend sheet and copy it and paste it to the first empty row below all of your data on the Sheet1 sheet.

Related Q & A:

Just Added Q & A:

Find solution

For every problem there is a solution! Proved by Solucija.

  • Got an issue and looking for advice?

  • Ask Solucija to search every corner of the Web for help.

  • Get workable solutions and helpful tips in a moment.

Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.