Macro to populate cell and then print on loop

[EdGilmore]

Hi,

I want to populate a cell (I10) on one sheet (FORM) with data contained in another (REPORT, column A) which will then update other areas of the sheet using a vlookup function based on the value which is populated in I10 then print the FORM sheet before going back and repopulating I10 with the next value on REPORT column A and repeating the process until all this process has gone through all data in REPORT column A has been through this process.

Essentially, copy data from REPORT!A1 to FORM!I10 (which will populate the FORM sheet through vlookups), print FORM, clear data from FORM!10, copy data from REPORT!A12 to FORM!I10, print FORM and so on until this loop has run through all the data in REPORT Column A.

Help!

Thanks,

Ed.

 

[jbeaucaire]

So the values are in column A at 11 row intervals?

A1, A12, A23, A34, etc.... ?

If so, something like this:

Option Explicit

Sub DateToForm()
Dim MyRange As Range, MyVal As Long, LR As Long

LR = Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Sheets("Report").Range("A1:A" & LR)

    For MyVal = 1 To LR Step 11
        Sheets("Form").[I10].Value = Sheets("Report").Range("A" & MyVal).Value
        Sheets("Form").PrintOut Copies:=1
    Next MyVal

Sheets("Form").[I10].Value = ""

End Sub

 

[EdGilmore]

Thanks for the response. It was actually a typo, sorry! I'm looking for this to loop through A1, A2, A3 etc. until column A is exhausted.

Thanks.

 

[jbeaucaire]

So remove the Step 11 and it should work fine.

---------------
Or we can adjust the syntax slightly to:

Option Explicit

Sub DateToForm()
Dim MyRange As Range, MyVal As Range, LR As Long

LR = Sheets("Report").Range("A" & Rows.Count).End(xlUp).Row
Set MyRange = Sheets("Report").Range("A1:A" & LR)

    For Each MyVal In MyRange
        Sheets("Form").[I10].Value = MyVal.Value
        Sheets("Form").PrintOut Copies:=1
    Next MyVal

Sheets("Form").[I10].Value = ""

End Sub

 

[EdGilmore]

Brilliant, that's done the trick!

Thanks.