Copying Data from One sheet to another

  • Thread starter Thread starter James
  • Start date Start date
J

James

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
 
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
Click to expand...

excel commands are buggy when programmed. use do loops.
 
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
Click to expand...

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
 
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)
 
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
 
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
 
Back
Top