help code transfer data from sheet to another sheet

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010
hi guy i hope help me the code is somthing wrong i try to transfer data of invoice from sheet1 to sheet 2 but give me this mistake RED LINE
AND GIVE ME RUN TIME ERROR13




Option Explicit
Sub transferData()
Dim i As Long
Dim lastrow As Long
Dim erow As Long
lastrow = Sheets("SHEET1").Range("a" & Rows.Count).End(xlUp).Row
For i = 3 To lastrow
erow = Sheets("SHEET2").Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row
Range(Cells(i, 1), Cells(i, 6)).copy Destination:=SHEET2.Cells(erow, 1)
Next i
SHEET2.Cells(erow, 6) = WorksheetFunction.Sum(Worksheets(SHEET2).Range("e3:e35"))
End Sub
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Looks like you might need some quotation marks around SHEET2.
Code:
SHEET2.Cells(erow, 6) = WorksheetFunction.Sum(Worksheets([COLOR=#0000ff]"[/COLOR]SHEET2").Range("e3:e35"))
When you use it as code name SHEET2, you do not use the quotation marks, but when you use it with Sheets() or Worksheets() you do need them.
 
Last edited:

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010
You're right thank you but still problem it doesn't transfer all data some cells and ranges are missed
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Change this:
Code:
erow = Sheets("SHEET2").Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row
To this:
Code:
erow = Sheets("SHEET2").Range("A:F").Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Offset(1).Row
It will give you a more reliable row number to paste to. You could be overwriting some data by using the other method of getting the row number, if column A is not always the column with data in the last row.
 

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
12,979
Office Version
  1. 2013
Platform
  1. Windows
Now I am confused. Your code shows that you are trying to copy ranges from columns A through F, but the screen shots show all of your data begins in column E. If you have no data in columns A:F then that is why you are not gettihng anything copied over. I don't believe I can help any further with this, so I am dropping off of this thread.
Regards, JLG
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010
actually i need this column to fill some data but why not code doesn't work it supposing to work transfering cells not only value even colors supposed without any value in cells
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
995
Office Version
  1. 2010
by the way i changed the range (a:e) this is the same result don't change anything
 

Watch MrExcel Video

Forum statistics

Threads
1,129,559
Messages
5,637,056
Members
416,955
Latest member
Gohar hussain

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top