Cheating With Printing

  • Thread starter Thread starter Matthew
  • Start date Start date
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
 
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
 
Hi Matthew

Do you have a list in a excel sheet with all numbers or are they simple number 1 to 656
 
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
 
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
 
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
 
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
 
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
 
Back
Top