Loop through column and copy/paste each value to another workbook

Question

I have to create a macro for work in excel and getting lost and was wondering if yall could assist.

So I have two workbooks. The first workbook (TEST) has a form that I need to print, the second workbook (Coin Test) has raw data. I have a cell in the form workbook where if I paste an item number from the raw data workbook it'll automatically pull all of the item information from the raw data sheet via a formula and fill in the form (Item Number, Item specifications, Item price, etc).

So what I need to do is automate going to the raw data sheet, copying the first item number from C1 and then go to the form workbook and pasting that item number into the cell J2 where it'll automatically fill out the form and then print that form and then go back to the raw data workbook and go down to the next item number and rinse and repeat. I was trying to piece together codes together, here is my monster so far. Thank you all for any assistance

Sub Macro4()

'Macro4 Macro

  Sheets("TESTING").Select
  Selection.Copy

  Sheets("COIN TESTING").Select
  ActiveSheet.Paste

  For Each Cell In Range("C$:C$")

    If Cell.Value = vbNullString Then Exit For

    Range("C$:C$").Value = Cell.Value
    Cell.Offset(1, 0).Value = Range("J2").Value

  Next

End Sub

You might benefit from reading How to avoid using Select in Excel VBA. — Pᴇʜ

Tim Williams Tim Williams · Accepted Answer · 2019-05-15T21:51:43

Untested:

Sub Macro4()

    Dim c As Range, rng As Range

    'get the range to loop over
    with Sheets("COIN TESTING")
        Set rng = .Range("C1:C" & .cells(.rows.count,3).end(xlUp).Row)
    end with

    'process each cell in turn
    For each c in rng.cells

        'is there a value to transfer?
        If Len(c.value) > 0 then
            'directly assign the value - no need for copy/paste
            Sheets("TESTING").Range("J2").Value = c.value

            'calculate and print out sheet
            Sheets("TESTING").UsedRange.Calculate 'refresh formulas
            Sheets("TESTING").PrintOut

        End if

    Next c

End Sub

Loop through column and copy/paste each value to another workbook

1 Answers