Cheating With Printing

M

Matthew

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
 
R

RJ

If you put the 656 shops into a list (in my example below they are in column
B starting at row 1) then the code below should accomplish the task. If you
put the list of shops in a different place, you will have to change the code
to refer to that location.

Sub PrintCheat
Dim iRow As Integer
iRow = 1
Do Until Cells(iRow, 2) = ""
Cells(1, 1).Value = Cells(iRow, 2).Value
ActiveWindow.SelectedSheets.PrintOut Copies:=1
iRow = iRow + 1
Loop
End Sub

Let me know if this helps or you have any additional questions.

Thanks,
Ray
 
R

Ron de Bruin

Hi Matthew

Do you have a list in a excel sheet with all numbers or are they simple number 1 to 656
 
G

Gord Dibben

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
 
R

Ron de Bruin

You can use this
I use a sheet named "ShopSheet" where we copy the value in and print

Sub test()
Dim cell As Range
For Each cell In Range("centres").SpecialCells(xlCellTypeConstants)
With Sheets("ShopSheet")
.Range("A1").Value = cell.Value
.Calculate
.PrintOut
End With
Next cell
End Sub
 
M

Matthew

You can use this
I use a sheet named "ShopSheet" where we copy the value in and print

Sub test()
Dim cell As Range
For Each cell In Range("centres").SpecialCells(xlCellTypeConstants)
With Sheets("ShopSheet")
.Range("A1").Value = cell.Value
.Calculate
.PrintOut
End With
Next cell
End Sub
Click to expand...

Ron,
You wiz, I must get a book on this, any recommendation for starter/
intermediate ?


Thanks It worked a Treat.

Matthew
 
M

Matthew

Ron,
You wiz, I must get a book on this, any recommendation for starter/
intermediate ?

Thanks It worked a Treat.

Matthew
Click to expand...

Now to be really cheaky....

If the 600 odd shops are divided into 'areas' is there a way of
selecting an area eg 882 (which could be a range name or other that
lists the 10 to 18 shops in that name).


the area numbers/names are 3 digit numbers but not consecutive ie
could be 881,882,890,891,........

So in all I guess I am looking for a sheet where i can select the area
and it will print the various shops/centers that are in that area.

Isn't it funny when you get what you were striving for you then want
more.

Matthew
 
R

Ron de Bruin

Can't you use AutoFilter to filter on the area

Or is there no column with the area ? in your data table

You can loop then through the visible cells then in your print macro
 
Top