You want "all" 656 shops' data printed in 656 separate print jobs?

Assuming you have a list of shop numbers in column A of Sheet1 and the VLOOKUP
formulas on Sheet2 pointing to Sheet2!A1 as lookup value, this macro will place
a shop number into Sheet2!A1 and print Sheet2 then loop to next number.

Sub Print_Shops()
Dim mySheet1 As Worksheet
Dim mySheet2 As Worksheet
Dim myCell As Range
Set mySheet1 = Worksheets("Sheet1")
Set mySheet2 = Worksheets("Sheet2")
For Each myCell In mySheet1.Range("A1:A656")
mySheet2.Range("A1").Value = myCell.Value
mySheet2.PrintOut 'PrintPreview for testing
Next myCell
End Sub


Gord Dibben MS Excel MVP

On Tue, 31 Jul 2007 10:36:32 -0700, Matthew wrote:

What I want to do is;

I have a spread sheet with 656 different 'shops' each with different
budgets.

Using vlookup etc I have made a sheet where you type the shop id
number in a1 and it brings all the data up for that shop.

What i would love to be able to do is to have a little macro that
would print all the sheets by entering the shop id into a1 for me.

The shops id number are all 3 digit numbers.

Thanks in advance

Matthew