Simple array loop copy and paste

Question

I'm new to VBA. I use a one column array for the variable data. Starting at the first cell (A1) I want to copy the text value in A1, paste to Sheet2,in A5, go back to the array and do it all over again, until I get to an empty cell. Easy right?

Here is the code that I have, I can not copy the value and paste it.

Thank you, for your suggestions!!!

Sub copylist()
    ' copylist Macro
    Worksheets("ID nbr").Select
    Range("B3").Select
    For Each c In Worksheets("ID nbr").Range("B3:B20").Cells
        If c.Value <> "" Then
            Sheets("ID nbr").Select
            Dim rgCopy As Range

            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False

            Range("B4:G4").Select
            Application.CutCopyMode = False
            Selection.Copy
            Sheets("Findings").Select
            Range("B4").Select
            Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
                False, Transpose:=False
        End If
    Next
End Sub

If the answer below was correct, please mark it as so by clicking the check mark by the answer. It is something only you can do. — Scott Craner
– Scott Craner, Commented May 23, 2016 at 17:07

Scott Craner · Accepted Answer · 2016-05-22 15:16:45Z

2

You have made a great attempt using the macro recorder. Now let's clean it up:

I moved all the sheets into variables to limit the amount of typing.
I removed all the .Select and .Activate, these just slow the code down and if referenced properly they are not needed.
When only values are wanted, then assigning them directly is quicker than using the clipboard. We can do this as one block of cells.
I used a counter to move down one row on the target sheet for every row found in the original sheet.

The code:

  Sub copylist()
    Dim ows As Worksheet
    Dim tws As Worksheet
    Dim c As Range
    Dim i As Long

    Set ows = Sheets("ID nbr") 'Original sheet
    Set tws = Sheets("Findings") 'Target sheet
    i = 4 'this is the first row in the target sheet

    With ows
        For Each c In .Range("B3:B20").Cells
              If c.Value <> "" Then
                  tws.Range(tws.Cells(i, "B"), tws.Cells(i, "G")).Value = .Range(.Cells(c.Row, "B"), .Cells(c.Row, "G")).Value
                  i = i + 1
              End If
        Next c
    End With

    End Sub

edited May 22, 2016 at 15:16

answered May 22, 2016 at 15:08

Scott Craner

153k10 gold badges52 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Adarsh Madrecha Over a year ago

Good notes for the code. Will help new programmers like me.

Collectives™ on Stack Overflow

Simple array loop copy and paste

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related