Last empty row to last data row

[adamsm]

Hi anyone,

How could I change the following code so that it copies the cell content from active sheet to the sheet data's; starting from last empty row to last data row.

Sub Last()
Range("C1").Copy Destination:=Sheets("Dat").Range("B" & r & ":B")
End Sub

Any help on this would be appreciated.

 

[hiker95]

adamsm,


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Option Explicit
Sub Last()
' hiker95, 04/22/2011
' http://www.mrexcel.com/forum/showthread.php?t=545489
Dim r As Long
r = Sheets("Dat").Range("B" & Rows.Count).End(xlUp).Offset(1).Row
ActiveSheet.Range("C1").Copy Destination:=Sheets("Dat").Range("B" & r)
End Sub

 

[sijpie]

Not totally clear what you mean. But I am assuming you have a range on the active sheet of unknown number of rows, which you want to copy to the Sheet("data") below the last row. Or do you only want to copy one cell to the row below the last row?

Option 1 (larger number of rows attached to current selected cell in active sheet:

Sub Last()
   Dim lRows as long, lDest as long
 
   lDest = Sheets("Data").Range("B1").CurrentRegion.Rows.Count
   lRows = Activesheet.ActiveCell.CurrentRegion.Rows.Count
 
   with ActiveCell
       Range(Cells(.Row,.Column),Cells(.Row+lRows-1,.Column)).Copy _
            Destination:=Sheets("Data").Range("B" & lDest)
   end with
end Sub

Option 2 (Copy only current selected cell)

Sub Last()
   Dim lDest as long
 
   lDest = Sheets("Data").Range("B1").CurrentRegion.Rows.Count
   lRows = Activesheet.ActiveCell.CurrentRegion.Rows.Count
 
      ActiveCell.Copy _
            Destination:=Sheets("Data").Range("B" & lDest)
   end with
end Sub