Cheating With Printing
 

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

Cheating With Printing
 

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

"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

Cheating With Printing
 

Hi Matthew

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Matthew" wrote in message ps.com...
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

Cheating With Printing
 

On 31 Jul, 19:33, "Ron de Bruin" wrote:
Hi Matthew

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in glegroups.com...
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

They are in the workbook, in a range named centres

Cheating With Printing
 

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

Cheating With Printing
 

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




--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Matthew" wrote in message ups.com...
On 31 Jul, 19:33, "Ron de Bruin" wrote:
Hi Matthew

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in glegroups.com...
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

They are in the workbook, in a range named centres

Cheating With Printing
 

On 31 Jul, 19:51, "Ron de Bruin" wrote:
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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in oglegroups.com...
On 31 Jul, 19:33, "Ron de Bruin" wrote:
Hi Matthew

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in glegroups.com...
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

They are in the workbook, in a range named centres

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


Thanks It worked a Treat.

Matthew

Cheating With Printing
 

Hi Matthew

http://www.amazon.com/gp/product/0764540726
Or choose the 2007 version from John.

And visit the newsgroups and search in al old postings
You can use my Google add-in if you want
http://www.rondebruin.nl/Google.htm

Have fun


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Matthew" wrote in message ups.com...
On 31 Jul, 19:51, "Ron de Bruin" wrote:
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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in oglegroups.com...
On 31 Jul, 19:33, "Ron de Bruin" wrote:
Hi Matthew

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in glegroups.com...
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

They are in the workbook, in a range named centres

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


Thanks It worked a Treat.

Matthew

Cheating With Printing
 

On 31 Jul, 22:25, Matthew wrote:
On 31 Jul, 19:51, "Ron de Bruin" wrote:



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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in oglegroups.com...
On 31 Jul, 19:33, "Ron de Bruin" wrote:
Hi Matthew

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in glegroups.com...
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

They are in the workbook, in a range named centres

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

Thanks It worked a Treat.

Matthew

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

Cheating With Printing
 

On 31 Jul, 22:40, "Ron de Bruin" wrote:
Hi Matthew

http://www.amazon.com/gp/product/0764540726
Or choose the 2007 version from John.

And visit the newsgroups and search in al old postings
You can use my Google add-in if you wanthttp://www.rondebruin.nl/Google.htm

Have fun

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in oglegroups.com...
On 31 Jul, 19:51, "Ron de Bruin" wrote:
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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in ooglegroups.com...
On 31 Jul, 19:33, "Ron de Bruin" wrote:
Hi Matthew

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in glegroups.com...
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

They are in the workbook, in a range named centres

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

Thanks It worked a Treat.

Matthew

Ron,

Done both. £17 for a little knowledge is cheap and your add in at £0
is great value !

Thanks for your help

Matthew

Cheating With Printing
 

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Matthew" wrote in message ups.com...
On 31 Jul, 22:25, Matthew wrote:
On 31 Jul, 19:51, "Ron de Bruin" wrote:



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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in oglegroups.com...
On 31 Jul, 19:33, "Ron de Bruin" wrote:
Hi Matthew

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

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm

"Matthew" wrote in glegroups.com...
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

They are in the workbook, in a range named centres

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

Thanks It worked a Treat.

Matthew

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