help code transfer data from sheet to another sheet

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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:
Upvote 0
You're right thank you but still problem it doesn't transfer all data some cells and ranges are missed
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
by the way i changed the range (a:e) this is the same result don't change anything
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

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