Trevor's code assumes that the row to be copied and the row to be pasted can
both be determined by looking at column A.
Can you pick out a column that always has data in it if that row is used? If
yes, then you could modify Trevor's code (or Gord's code) to use that column.
Option Explicit
Sub Testme01()
dim myCol as string
dim RngToCopy as range
dim DestCell as range
'what column can you use--I used X.
myCol = "X"
with worksheets("sheet1")
set rngtocopy = .cells(.rows.count,mycol).end(xlup).entirerow
end with
with worksheets("sheet2")
set destcell = .cells(.rows.count,mycol).end(xlup).offset(1,0) _
.entirerow.cells(1)
end with
rngtocopy.copy _
destination:=destcell
end sub
Carlee wrote:
>
> I there,
> the code you provided works, however, when i use it a second time to copy
> and paste a new row of data into sheet 2, my previous rows get over written
> with the new data.
>
> Is it possible for the code you provided to paste any new rows to the next
> available blank row, there by creating a list of pasted rows over time?
> --
> Carlee
>
> "Trevor Shuttleworth" wrote:
>
> > One way:
> >
> > Sub CopyBottomRow()
> >
> > Sheets("sheet1").Range("a65536").End(xlUp).EntireRow.Copy _
> > Sheets("sheet2").Range("a65536").End(xlUp).Offset(1, 0)
> >
> > End Sub
> >
> > Regards
> >
> > Trevor
> >
> >
> > "Carlee" <(E-Mail Removed)> wrote in message
> > news:CA35D00F-52EC-441A-B1F3-(E-Mail Removed)...
> > > Hi all,
> > > Can anyone provide some code that will:
> > >
> > > 1. Copy the last completed row in SHeet 1, and
> > > 2. Paste the copied row to the next available blank row in Sheet 2 of the
> > > same workbook?
> > >
> > > Many thanks.
> > > --
> > > Carlee
> >
> >
> >
--
Dave Peterson
|
Similar Threads
