0

The code below works but does not copy over rows with blank cells, for example "Sheet1" has a total of 35 rows and 56 columns but certain cells are blank e.g. cell A28 & A31, this means the code will copy and paste 33 rows in "Sheet2" which excludes rows 28 and 31 instead of all 35 rows.

How can I change this to copy all 35 rows irrespective of whether it has blank cells or not?

My objective is to copy 10 specific columns for all 35 rows from "Sheet1" which has 56 columns to "Sheet2" which is currently blank and in different column order. Hope this makes sense.

Sub Transfer_Macro ()
'
Dim lastrow As Long, erow As Long

lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow

Worksheets("Sheet1").Cells(i, 1).Copy

erow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row

Worksheets("Sheet1").Paste Destination:=Worksheets("Sheet2").Cells(erow + 1, 1)

Worksheets("Sheet1").Cells(i, 3).Copy

Worksheets("Sheet1`").Paste Destination:=Worksheets("Sheet2").Cells(erow + 1, 3)

Next i

End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Analyst1
  • 19
  • 7
  • If Sheet1 has data in A35, then `lastrow` from `lastrow = Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row` will equal 35. – BigBen Oct 23 '19 at 13:47
  • ^ So "this means the code only copy and paste up until cell A28" doesn't make sense to me. – BigBen Oct 23 '19 at 13:53
  • 1
    hi. the problem is that op doesnt know witch column has more lines. he have to test all the columns and get the biggest lastrow – Luis Curado Oct 23 '19 at 13:53
  • Thanks @BigBen, you're right it copies up until A35 but minus the rows with the blank cells (A29 and A31), so rather than copying all 35 rows on column A and C, there are only 33 rows on sheet2, hope this makes sense, thanks. – Analyst1 Oct 23 '19 at 13:53
  • If you want to find the last effective row in a *sheet*, see the approach in the answer to [this question](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba). – BigBen Oct 23 '19 at 13:55
  • 2
    No need for a loop. Just transfer the entire range in one go, and the blanks will transfer from sheet1 to sheet2. – BigBen Oct 23 '19 at 13:59

1 Answers1

0

Assuming Luis Curado has diagnosed your problem correctly, you can find the larger of the last cell row in A and C.

Also you had some confused syntax here

Worksheets("Sheet1").Paste Destination:=Worksheets("Sheet2").Cells(erow + 1, 1)

Finally, you can transfer the values without involving the clipboard.

Sub Transfer_Macro()

Dim lastrow As Long, erow As Long

With Worksheets("Sheet1")
    lastrow = WorksheetFunction.Max(.Cells(Rows.Count, 1).End(xlUp).Row, .Cells(Rows.Count, 3).End(xlUp).Row) 'could use Find instead for this
    For i = 2 To lastrow
        erow = Worksheets("Sheet2").Cells(Rows.Count, 1).End(xlUp).Row
        Worksheets("Sheet2").Cells(erow + 1, 1).Value = .Cells(i, 1).Value
        Worksheets("Sheet2").Cells(erow + 1, 3).Value = .Cells(i, 3).Value
    Next i
End With

End Sub

Note that if you are copying blank cells, the last row will not update.

SJR
  • 22,986
  • 6
  • 18
  • 26
  • I think you can get rid of the loop here, and this will eliminate OP's problem with blanks. – BigBen Oct 23 '19 at 13:57
  • @BigBen - yes you're probably right but am going to leave for now as not completely sure what OP wants. – SJR Oct 23 '19 at 13:59
  • I think you've nailed the problem with "if you are copying blank cells, the last row will not update." – BigBen Oct 23 '19 at 14:00
  • Might the OP wanting to be eliminate the blanks. Otherwise it's a 1-liner as you suggest. – SJR Oct 23 '19 at 14:01
  • I was interpreting [this comment](https://stackoverflow.com/questions/58524312/how-to-copy-columns-with-blank-cells-and-paste-in-new-worksheet-vba#comment103373833_58524312), which seems to suggest OP still wants the blanks. – BigBen Oct 23 '19 at 14:03
  • ^ and I completely agree with you on that :) – BigBen Oct 23 '19 at 14:05
  • Thanks @SJR, unfortunately it errored with run time error "9", subscript out of range with this row highlighted in yellow. Worksheets("Sheet2").Cells(erow + 1, 1).Value = .Cells(i, 1).Value. – Analyst1 Oct 23 '19 at 14:06
  • Just to clarify, the issue is this: I have 35 employees on "sheet1" with many columns. I want to copy certain columns but in different order to "sheet2", the issue is that all columns have blank cells but on different rows including on column A which is the employee number. I want all employees to be listed even if their number, name or address cells are blank, hope this explains better. – Analyst1 Oct 23 '19 at 14:13
  • What's the value of erow when it errors, and what's in the cell being copied? – SJR Oct 23 '19 at 14:17
  • Why doesn't BigBen's suggestion to copy and paste in one go not work for you? There is no obvious need for your loop based on what you've shown here. – SJR Oct 23 '19 at 14:19
  • Depending on your version of Excel, PowerQuery/Get and Transform will do what you want quite nicely (export and reorder columns). – BigBen Oct 23 '19 at 14:22
  • 1
    @Analyst1 what do you mean by "different order"? your question or code does not represent what you just stated. Please update your question and provide an example of your start and finish product – GMalc Oct 23 '19 at 14:29