vba excel copy to another sheet

Question

I would like to get some help with following 2 question:

First,

I have Sheet1 which loops and gets different values from website for cells

B68, B69, B67, A61, D85 and B73, I would like to write VBA to copy these values in Sheet2 in order

A1, B1, C1, D1, E1, F1

but with my current code if Sheet1 any cell is empty it will shift copied cell (up(End(xlUP), I need to create with every set of data next row, regardless of value, even some copied cells are empty...

Second,

Add condition that every time D85 in Sheet1 is empty then it will copy value from cell D86, regardles the value of D86 ...

 DoEvents

    'Copy to Another sheet

    With Worksheets("Sheet2")
        .Range("A" & Rows.Count).End(xlUp).Offset(1, 0) = ws.Range("B68")
        .Range("B" & Rows.Count).End(xlUp).Offset(1, 0) = ws.Range("B69")
        .Range("C" & Rows.Count).End(xlUp).Offset(1, 0) = ws.Range("B67")
        .Range("D" & Rows.Count).End(xlUp).Offset(1, 0) = ws.Range("A61")
        .Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = ws.Range("D85")
        .Range("F" & Rows.Count).End(xlUp).Offset(1, 0) = ws.Range("B73")


    End With

Shai Rado Shai Rado · Accepted Answer · 2016-07-16T06:09:42

The code looks for the last row of UsedRange in Sheet2, even if some cells are empty. Then it copies the data from ws sheet to Sheet2.

Dim des_ws As Excel.Worksheet

Set des_ws = ActiveWorkbook.Worksheets("Sheet2")

LastRow = des_ws.UsedRange.Rows(des_ws.UsedRange.Rows.count).row + 1

With des_ws
    .Range("A" & LastRow) = ws.Range("B68")
    .Range("B" & LastRow) = ws.Range("B69")
    .Range("C" & LastRow) = ws.Range("B67")
    .Range("D" & LastRow) = ws.Range("A61")
    If ws.Range("D85") = "" Then
        .Range("E" & LastRow) = ws.Range("D86")
    Else
        .Range("E" & LastRow) = ws.Range("D85")
    End If
    .Range("F" & LastRow) = ws.Range("B73")
End With

vba excel copy to another sheet

1 Answers