Input Form, Excel, VBA to copy inputs and paste in another worksheet

Question

I am trying to reduce using .select and .paste when trying to perform a copy and paste. I know using loops are way easier. I'm trying to copy a range of cells from my worksheet "Entry" and paste it into a worksheet called "input". The problem I'm running into is that I want to paste the data into consecutive cells.

Here's what I have so far:

Dim i as Byte, y as Byte, x as integer 

For i = 3 to 3 
    For y = 3 to 17
      x = x +1 

      Sheets("input").Cells(29,x).value = Sheets("Entry").Cells(y,i).value

     Next y 
Next i

The cells that are copied have an empty cell in between like the this:

Entry Cells that are copied

When I paste them into the "input" tab at the range that I want it pastes it like this:

Input Paste

I'm trying to get it so that when pasted, it pastes consecutively into the "input" worksheet like this:

Consecutive Cells

Sorry, I forgot that line when I tried to copy and paste into the post. I've updated the post. It has the loop for Y in it now. So what VBA could I use to achieve that end result? — JamNalus

cjohnson221 cjohnson221 · Accepted Answer · 2019-02-11T21:18:46

Yes, you are correct that it is generally good practice to avoid .Select and .Paste within VBA code as they take longer to process and usually are not necessary. In your case, it may be easier to use a for loop that loops through a range of cells and copies them to another sheet. This allows you to check to see if the cell contains anything, and if it doesn't contain anything, it can be skipped. This is an example:

Sub CopyCells()

    Dim rng As Range, cell As Range, copyToCell As Range

    Set rng = ThisWorkbook.Sheets("Entry").Range("A1:A10") 'This is the range you want to copy from
    Set copyToCell = ThisWorkbook.Sheets("inputs").Range("A1") 'This is the first cell in the range you want to copy to

    For Each cell In rng
        If cell.Value <> "" Then 'Only copy from cells that are not blank
            copyToCell.Value = cell.Value
            Set copyToCell = copyToCell.Offset(0, 1) 'Move the copy to cell over by one column (or by row if you prefer)
        End If
    Next cell

End Sub

Input Form, Excel, VBA to copy inputs and paste in another worksheet

1 Answers