Hi all

I am trying to copy cells from one sheet and paste in another using the
below. The theory is that there is a new row in both sheet 1 and 2 each day
and each day I want it to find the last row in sheet 1, copy the data in
cells B-M into the last row in sheet 2, columns F-Q. However it keeps
erroring! Does anyone have any ideas what I'm doing wrong????

Sheets("Sheet1").Select
Cells(Rows.Count, "A").End(xlUp).Select
Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
Sheets("Sheet2").Select
Cells(Rows.Count, "C").End(xlUp).Select
Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste


Thanks in advance for any help

--
James.

On Nov 16, 12:10*pm, James <Ja...@discussions.microsoft.com> wrote:
> Hi all
>
> I am trying to copy cells from one sheet and paste in another using the
> below. The theory is that there is a new row in both sheet 1 and 2 each day
> and each day I want it to find the last row in sheet 1, copy the data in
> cells B-M into the last row in sheet 2, columns F-Q. However it keeps
> erroring! Does anyone have any ideas what I'm doing wrong????
>
> Sheets("Sheet1").Select
> Cells(Rows.Count, "A").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
> Sheets("Sheet2").Select
> Cells(Rows.Count, "C").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste
>
> Thanks in advance for any help
>
> --
> James.

excel commands are buggy when programmed. use do loops.

On Nov 16, 12:10*pm, James <Ja...@discussions.microsoft.com> wrote:
> Hi all
>
> I am trying to copy cells from one sheet and paste in another using the
> below. The theory is that there is a new row in both sheet 1 and 2 each day
> and each day I want it to find the last row in sheet 1, copy the data in
> cells B-M into the last row in sheet 2, columns F-Q. However it keeps
> erroring! Does anyone have any ideas what I'm doing wrong????
>
> Sheets("Sheet1").Select
> Cells(Rows.Count, "A").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
> Sheets("Sheet2").Select
> Cells(Rows.Count, "C").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste
>
> Thanks in advance for any help
>
> --
> James.

see post in group to "excel database" question.

James, I re-wrote your code to eliminate the select keyword and to use a
different syntax for the copy/paste function. This puts the target row in a
variable for the copy and then puts the next empty row in sheet2 in a
variable for the paste function. The copy to destination syntax does not
use the Paste or PasteSpecial command, Paste is implied by the syntax
structure. Put the Dim statements at the top of your macro and give it a
try.

rw = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row
rw2 = Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1).Row
Range("B" & rw & ":M" & rw).Copy _
Sheets("Sheet2").Range("F" & rw2)

'remainder commented out as unneeded.
'Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
'Sheets("Sheet2").Select
'Cells(Rows.Count, "C").End(xlUp).Select
'Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste





"James" <(E-Mail Removed)> wrote in message
news:741B9167-F92F-406C-97B0-(E-Mail Removed)...
> Hi all
>
> I am trying to copy cells from one sheet and paste in another using the
> below. The theory is that there is a new row in both sheet 1 and 2 each
> day
> and each day I want it to find the last row in sheet 1, copy the data in
> cells B-M into the last row in sheet 2, columns F-Q. However it keeps
> erroring! Does anyone have any ideas what I'm doing wrong????
>
> Sheets("Sheet1").Select
> Cells(Rows.Count, "A").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
> Sheets("Sheet2").Select
> Cells(Rows.Count, "C").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste
>
>
> Thanks in advance for any help
>
> --
> James.

Hi,

Try it like this

lastrow1 = Sheets("Sheet1").Cells(Cells.Rows.Count, "B").End(xlUp).Row
lastrow2 = Sheets("Sheet2").Cells(Cells.Rows.Count, "F").End(xlUp).Row + 1
Sheets("Sheet1").Range("B" & lastrow1 & ":M" & lastrow1).Copy _
Destination:=Sheets("Sheet2").Range("F" & lastrow2)

"James" wrote:

> Hi all
>
> I am trying to copy cells from one sheet and paste in another using the
> below. The theory is that there is a new row in both sheet 1 and 2 each day
> and each day I want it to find the last row in sheet 1, copy the data in
> cells B-M into the last row in sheet 2, columns F-Q. However it keeps
> erroring! Does anyone have any ideas what I'm doing wrong????
>
> Sheets("Sheet1").Select
> Cells(Rows.Count, "A").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
> Sheets("Sheet2").Select
> Cells(Rows.Count, "C").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste
>
>
> Thanks in advance for any help
>
> --
> James.

Try this

Option Explicit
Sub test()
Dim myWS As Excel.Worksheet
Dim myWS1 As Excel.Worksheet
Dim lRow As Long
Dim lRow1 As Long
Dim myRange As Excel.Range

Set myWS = Sheets("Sheet1")
lRow = myWS.Cells(myWS.Rows.Count, "A").End(xlUp).Row
Set myRange = myWS.Range("B" & lRow & ":M" & lRow)

Set myWS1 = Worksheets("Sheet2")
lRow1 = myWS1.Cells(myWS1.Rows.Count, "C").End(xlUp).Row

myRange.Copy Destination:=myWS1.Range("F" & lRow1)


End Sub

--
HTH,

Barb Reinhardt



"James" wrote:

> Hi all
>
> I am trying to copy cells from one sheet and paste in another using the
> below. The theory is that there is a new row in both sheet 1 and 2 each day
> and each day I want it to find the last row in sheet 1, copy the data in
> cells B-M into the last row in sheet 2, columns F-Q. However it keeps
> erroring! Does anyone have any ideas what I'm doing wrong????
>
> Sheets("Sheet1").Select
> Cells(Rows.Count, "A").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
> Sheets("Sheet2").Select
> Cells(Rows.Count, "C").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste
>
>
> Thanks in advance for any help
>
> --
> James.

Try the below

Sub Macro()
Dim lngRowSource As Long, lngRowDest As Long
lngRowSource = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row
lngRowDest = Sheets("Sheet2").Cells(Rows.Count, "F").End(xlUp).Row

Sheets("Sheet1").Range("B" & lngRowSource).Resize(, 12).Copy _
Sheets("Sheet2").Range("F" & lngRowDest + 1).Resize(, 12)
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"James" wrote:

> Hi all
>
> I am trying to copy cells from one sheet and paste in another using the
> below. The theory is that there is a new row in both sheet 1 and 2 each day
> and each day I want it to find the last row in sheet 1, copy the data in
> cells B-M into the last row in sheet 2, columns F-Q. However it keeps
> erroring! Does anyone have any ideas what I'm doing wrong????
>
> Sheets("Sheet1").Select
> Cells(Rows.Count, "A").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("B:M")).Copy
> Sheets("Sheet2").Select
> Cells(Rows.Count, "C").End(xlUp).Select
> Intersect(ActiveCell.EntireRow, Range("F:Q")).Paste
>
>
> Thanks in advance for any help
>
> --
> James.