Copy cell values from one sheet and paste it in another sheet of the same workbook

Hello

Please find the excel file attached below. I have assigned a macro in "Save" button in Sheet1. How can I copy cells one by one from sheet1 and paste it in Sheet2. Sheet2 will have the values in same order as in Sheet1.

Regards

Maybe the below is what you want!

Please Login or Register  to view this content.

Thank you so much for your quick response. But, While debugging I came across "Run-time error '1004'." Method 'Range' of object '_Worksheet' failed. How to overcome it.

Runs fine in the attached worksheet. Make sure that there is data in some rows on Sheet 1. Use break and the immediate window to check that the rows are correct.

Thanks once again. But after spending lot of time, I managed to come up with the job I want my macro to do it for me. Therefore, I am sharing it with all the members of the forum. Here is the code....... I would also love to hear it from you EssoExplJoe.

Private Sub CommandButton1_Click()
Dim lastcol As Long, lastrow As Long, lastrow2 As Long, ecol As Long, i As Integer, x As Integer

lastcol = Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column
lastrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
lastrow2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Row + 1

For x = 2 To lastrow
For i = 1 To lastcol
Sheet1.Cells(x, i).Copy
ecol = Sheet2.Cells(x, i).End(xlUp).Offset(1, 0).Column
Sheet1.Paste Destination:=Sheet2.Cells(lastrow2, ecol)
Next i
lastrow2 = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Next x


Application.CutCopyMode = False
Sheet1.Select
Sheet1.Rows("2:" & Rows.Count).ClearContents

End Sub