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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — Pᴇʜ, May 16 '19 at 06:11

Tim Williams · Answer 1 · 2019-05-16T00:47:19.930

0

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

edited May 16 '19 at 00:47

answered May 15 '19 at 21:51

Tim Williams

154,628
8
97
125

Awesome, thank you very much. I made a few small changes. I was wondering though, when it copies the value from the one worksheet and pastes into the other, is there a way to paste just the value and not overwrite the formula thats in the cell? – chase_work85 May 15 '19 at 22:17
@chase_work85 how would you paste a value and NOT overwrite the formula? A value would eliminate any formula currently in the cell? – pgSystemTester May 15 '19 at 22:22
I apologize I didnt explain correctly in my previous post, I have the number stored as text by adding a leading apostrophe, is there way that when it pastes the value it can paste it as a number stored as text? – chase_work85 May 15 '19 at 23:10
Thank you everybody!! So everything seems to be working now except once the macro goes through one iteration it doesn't advance down to the second cell in the c1:c range (c2, then c3, then c4, etc) . It just goes back to c1 and displays that value again over and over – chase_work85 May 16 '19 at 00:09
I assumed you were going to add code to print out each sheet, and then run the complete loop. Is that not what you're doing - you just want that each time you run this the next row gets chosen? – Tim Williams May 16 '19 at 00:44
Thank you for your help Tim. I wasnt able to paste the full code earlier. Here's what I have. https://pastebin.com/bxge2XVC same as above with exception of transposing the source and destination worksheet and added the print code. – chase_work85 May 16 '19 at 03:03
So I just changed the cells(.rows.count,3) to cells(.rows.count,4) and for whatever reason its cycling through correct, when it was three it would hit c2 and then go back to c1. not sure why – chase_work85 May 16 '19 at 03:17
That's odd - it should have gone up to the last-occupied cell in ColC... – Tim Williams May 16 '19 at 03:28

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

1 Answers1