Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Printing Help | Excel Discussion (Misc queries) | |||
printing | Excel Discussion (Misc queries) | |||
Printing a heading on each new page when printing | Excel Discussion (Misc queries) | |||
Printing | Excel Worksheet Functions | |||
Enable Double sided printing contiuously when printing multiple s. | Excel Discussion (Misc queries) |