loop through range to print

[universal]

Ive currently got a macro that runs:

Sub printtables()

Range("r1").Select
ActiveCell.FormulaR1C1 = "User1"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Range("r1").Select
ActiveCell.FormulaR1C1 = "User2"
ActiveWindow.SelectedSheets.PrintOut Copies:=1

Range("r1").Select
ActiveCell.FormulaR1C1 = "User3"
ActiveWindow.SelectedSheets.PrintOut Copies:=1 etc

which prints off tables as R1C1 refreshes each time its changed. Thi
works fine for me.

Now, though it looks like I'm going to have to perform the same repor
print for up to 100 users. I'm trying to make a do..loop..until loo
to loop through contents of a column (colQ), refresh the table, print
copy and move to the next cell until it gets to the end, which I'v
designated as end (Q92="end").

Just cannot get it to work, any ideas?

Many thanks
E

 

[BrianB]

'------------------------------------------------------
For r = 1 To 100
ActiveCell.Value = "User" & r
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Next
'----------------------------------------------------

 

[universal]

Apologies, the user1 user2 notation was just that I wanted to make it
little anonymous.

The users are David, Dan, Sarah, etc. It is these names that are als
used to do lookups on other spreadsheets.

So far I've got:

Dim UserRange As Range
Dim UserCell As Range
Set UserRange = Range("q5:q90")
ActiveCell.FormulaR1C1 = Sheets("vlookups").Range("q5")
For Each UserCell In UserRange
ActiveCell.FormulaR1C1 = UserCell
Sheets("Table").PrintOut Copies:=1
Next

But this doesnt work either....

 

[BrianB]

Not knowing exactly how your data is set up, I have set some variable
in explicit format which you will need to edit (book and sheet names).

Hopefully this will do what you want :-

'----------------------------------------
Sub printtables()
Dim MyTable As Worksheet
Dim UserRange As Range
Dim UserName As String
'----------------------
Set MyTable = Workbooks("mybook.xls").Worksheets("Table")
Set UserRange
Workbooks("mybook.xls").Worksheets("mysheet").Range("q5:q90")
'----------------------
For Each c In UserRange.Cells
UserName = c.Value
MyTable.Range("A1").Value = UserName
MyTable.PrintOut Copies:=1
Next
End Sub
'---------------------------------------------

 

[universal]

Thanks Brian, I've got it working properly no