Print Dynamic Report: Macro

[Lightjag]

1) I have a report in sheet 1 that is populated by data in another sheet
using vlookup.
2) I have a range name = "list" (in sheet 1 as well, which lists ticker
symbol)
3) I would like the macro to loop through all the names in the "list" range
and print the reports.

 

[Lightjag]

Also for purposes of this ex/ I would like each name copied to cell A1, as
the vlookup keys off A1 to update the report, cycling through the list and
printing each report. -- Thanks

 

[Lightjag]

Ok the macro below cycles throught the list but only prints when it gets to
bottom of list. What should I change to get it to print each time is cycles
throught the list? Tks


Sub printinfo()
Dim n As Integer
n = 1
Do
Application.Goto Reference:="list"
ActiveCell.Offset(n + 1, 0).Select
Selection.copy
Application.Goto Reference:="ticker"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.PrintOut
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

 

[Lightjag]

Any ideas?

Ok the macro below cycles throught the list but only prints when it gets to
bottom of list. What should I change to get it to print each time is cycles
throught the list? Tks


Sub printinfo()
Dim n As Integer
n = 1
Do
Application.Goto Reference:="list"
ActiveCell.Offset(n + 1, 0).Select
Selection.copy
Application.Goto Reference:="ticker"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.PrintOut
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

 

[paul.robinson]

Hi
In first sub n is not being incremented. Put in
n = n+1
after the printout line.
Don't know what you want in your second question. What is a name? What
does "vlookup keys off A1" mean?
regards
Paul

 

[Lightjag]

Ok I made the change, but it still only prints the last item in the list
(instead of printing each item in the list). Vlookup is just not the issue
here, as it is just updating the report based on the value in cell A1.

 

[paul.robinson]

Hi
It is pasting everything to one place "ticker" so you only see the
last one. You probably need to use offset like
Range("ticker").offset(n,1) to print n rows below "ticker".
regards
Paul

 

[Lightjag]

Ok, I added the code, but I still have the same problem. Just to recap, I
have a "list" in col 1a:a3, I want to copy the cell a1 to location ticker and
print report, then copy a2 to ticker and print report and then copy a3 to
location ticker and print report. the report updates based on the value
pasted into location ticker. The macro below cycles through a1:a3, but only
prints once (i.e. when it gets to end of list, a3). It should be printing
each time a value is pasted into location "ticker". Below is what the
current code looks like.

Sub printinfo()
Dim n As Integer
n = 1
Do
Application.Goto Reference:="list"
ActiveCell.Offset(n + 1, 0).Select
Selection.copy
Application.Goto Reference:="ticker"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("ticker").Offset(n, 1).Select
ActiveWindow.SelectedSheets.PrintOut
n = n + 1
Loop Until IsEmpty(ActiveCell.Offset(0, 1))
End Sub

 

[Lightjag]

Ok now it works: cycles through each item in list, copies to a location,
prints report during each cycle. I changed:

A) "Loop Until IsEmpty(ActiveCell.Offset(0, 1))" to "Loop Until
IsEmpty(ActiveCell.Offset(0, 0))"
and
B) "ActiveCell.Offset(n +1 , 0).Select" to "ActiveCell.Offset(n,
0).Select"......as it was skipping the first item in the list.

All changes:

Sub printinfo()
Dim n As Integer
n = 0
Do
Application.Goto Reference:="list"
ActiveCell.Offset(n, 0).Select
Selection.copy
Application.Goto Reference:="ticker"
' Pastes value to location "ticker"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SelectedSheets.PrintOut
n = n + 1
Loop Until IsEmpty(ActiveCell.Offset(0, 0))
End Sub







The only issue I have is it print a blank at the end. Ex/ is I have 3 items
in a list it prints all 3 plus copies and pastes a blank and then prints it,
or if there are 5 items it prints a 6th rpt (ie. blank).

Any suggestions on how to get it to stop so it does not print the last rpt
which is blank (i.e. gets to end of "list")?

 

[Lightjag]

Problem solved: mcro stops when it finds an empty cell, by adding " If
IsEmpty(ActiveCell) Then End" .....I added it below row "ActiveCell.Offset(n,
0).Select"