We have a an existing spreadsheet which uses a query from Excel to Sage Line50 Accounting software to create labels for stock items. The number of times a label is printed is dictated by the number of items that are required for a particular sales order.
For example -
1006abc 100 No.
2003abc 20 No.
The program would produce 100 labels for item code 1006abc, and would produce 20 labels for item number 2003abc.
However we have recently acquired a heavy duty industrial printer which prints to labels on a continuous roll and I am unable to alter the spreadsheet to work with the new printer layout.
I have attached the spreadsheet and, I hope, the VBA code. PLease also see this link to my previous post - Print Labels Dependant on Content
I have a spreadsheet where I am trying to print barcode labels. The barcode is found in cell A1. I have looked in a lot of places and I can't really figure out exactly how to do it. This is the code that I have so far but it gives me an error. The printer name should be right. Some of this code I just copied from another thread.
I have a workbook with 150-some-odd sheets, each with a store number. I would like to loop through each sheet & print that sheet to the printer with the same name in my printers & faxes queue.
(eg. sheet 9604 would print to printer name oki9604)
Is there a Macro to automatically print the selected print area (print area is also established within the macro itself) to a user's default printer; whatever that printer may be? Note that this printer and/or printer name could change depending on the user.
I would like this to actually select and print to the default printer without Excel having to ask you. I don't even want the "OK" box to show up if possible. I just want them to run the macro, execute all it's duties, and print, all in 1 shot.
i have a macro that loads a userform for previewing and printing selected sheets. The macro works fine for previewing sheets but when i choose the print button the marco trys to save the file as a .mdi, i want this to select the default printer on the pc instead, the excel file will be onpened on many different pcs so the printers will be different on each machine, hence why i want a default printer selected. This is the section of code from the form
I am trying to swith between different printers to print out sheets within a single workbook. Recording the macro doesn't work very well and the code I found online doesn't work either (error: object doesn't support property).
How can I set this up to be a dynamic printer setting? Code I tried:
I have done a userform which allows the user to select printing to each section(All, A, B or C) of the report to either paper size A3 or A4 looking something like below -
Print All A3 A4 (these buttons are option buttons) Print A A3 A4 Prtint B A3 A4 Print C A3 A4
I have coded and part recorded a macro to change the paper size to either A3 or A4 depending on the user's selection.
The problem i have is if i take out the print command, it seems to change the paper sizes as it should but when i print it prints to the same size as the previous print i did, regardless of what was selected on the userform.
I have a printer with multiple tray sizes, and since I need to change paper sizes, I assume i need to change trays as well.
Below is the two macros -
'''''''''''''Set Print area and print to A3'''''''''''''''''
Private Sub PrintAllA3_Click() Dim LastRow As Long Dim LastCol As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column End With
I want to automate (with the click of a macro button, or hot key sequence) is to: · take a pre-set print area (which is always the same – 1 page wide x 1 page tall) · send to the printer · select the PDF printer from the list (not the default printer for the computer) · Automatically change the save PDF file name to “(Cell A1) – Weekly Update – (Today’s Date).pdf” · Change location to save file on Desktop
Feels like a pretty lofty goal for a macro to do all of that – please let me know if that is even possible to set up.
I have a macro that prints out several worksheets. My problem is that I want to print these worksheets to a specific color printer. There are many different users and most of them will have a black-and-white printer setup as their default. I want to be able to print using the ip address of the color printer.
I have tried setting the active printer using:
Application.ActivePrinter = "\WP010110.117.3.36"
with little luck... This seems like such a simple thing! I have done a lot of research and can't find a good solution although I have seen the question raised several times!
Ozgrid has been a great source of information and solutions in the past. Please help again!
I have the following code to print a selection of data which is fine but i need to have in the code a different printer. So, for example my default printer needs to stay the same but when this spreadsheet is open i need the data to be printed to another printer.
activesheet.printpreview , Excel crashes if there are no printers installed on the machine. How can i check for printers before calling the printpreview, or else stop the program from crashing?
The below code opens up the printpreview window. This does not allow to select the correct (or change) printer it will automatic use the default printer.
Is there a way for the code to open the print window so I can change the printer and select number of copies?
Code:
Sub Print_Button() Dim ws As Worksheet, cell As Range Set ws = Sheets("main") Set cell = Range("g2000").End(xlUp) Do Until cell.Value "" Set cell = cell.Offset(-1, 0) Loop ws.PageSetup.PrintArea = ("A2:" & cell.Address) ws.PrintPreview End Sub
to print each row of a spreadsheet as a separate label and have a feeling that this possible using macros but i have very limited knowledge of them. The number of times I need to print this label is contained in a cell in each row.
I know that i can go into word and use mail merge to produce labels but i would like something altogther more automated to remove the possibility of human error.
I have a database of around 300 names and addresses from which I need to print labels .....
If not what is the best way to do it. Can this file be converted to a doc or mdb file and then mail merge in MS Word. Could somebody please reply at the earliest as I have very little time to create these labels ?
I have a form that users will fill in, and I need to print a set number of labels (all identical) based on the info that is entered.
I have the form set up with data validation and indirect references(Lists on a separate sheet)
Example...
User enters in: Customer name Product name Lot # PO# Date made Number of items
From there I have a separate set of cells that reference the above info formated to how I want the label to appear when printed.
Now, I need to repeat that label the set number of times (# entered in as "number if items") in a label sort of format (not sure if a mail merge will work for this??)
Then, print off and stick identical labels onto each part that is manufactured per PO/Date etc.
I have an export from a database that I'm bringing into Excel 2010 of about 30K records. Data points are recorded numerically and I have their associated text "value label" (what it would be called in STATA, for example, not sure what it's called in Excel). I want to create various charts/pivot tables with the data and want the labels to be the text label, not the number.
For example, variable ASSIGNMENT has the following possibilities:
1 2 3 4
Here's what each of those "mean" (I have this in another table):
1 - Sick 2 - Overtime 3 - Court 4 - Present
How do I create a chart or pivot table where the labels are "sick", "overtime", etc., and not "1", "2", "3", "4"?
I have a worksheet that is just a list of items with a location listed next to each item. I took that list and made it into a pivot table and would like it to have it list the items with each location it is in displayed across. I have already set it to tabular form in the pivot table options, but if there are multiple locations it lists them vertically and I want them list horizontally for printing purposes. I have attached a screen shot to explain.
I have the following formula =IFERROR(ROUNDUP((+G5+G23+G41+G59)/4,0),"") that if it returns an odd number as in 3 or 5 or 7 or etc i want it to display 4 or 6 or 8 or etc
When copying the formula =SUMIFS(G3:G103,I3:I103,"3") how do I modify it so that only the "3" rolls to the next integer and the rest of the formula remains constant? In other words the 3 would roll to a 4 when copied down a column and the rest of the formula does not change?
I have been supplied a spreadsheet (see attached example sheet) which contains data on a project I am working with. Essentially the guy who has supplied me the data has been lazy and only provided values once within the sheet which makes filtering extremely difficult. I have made the cells on the sheet A3:F23 in the format I ideally require. Cell A26 onwards is the format in which the data is currently supplied to me.
Column A (Box No) should roll down the same value until the value changes, where a blank row should be left as already exists and the same process of roll the value down until it changes, leave the blank line and then roll down the next value. This should continue until the data ceases at the end of the sheet - I have made the example sheet short but there are tens of thousands of box numbers. Columns B (Box Type) and C (No of Bundles) should also remain constant where Box No remains the same and should be rolled down until the value of column A (Box No) changes.
Finally column D (Bundle No) should be rolled down independently of A,B,C and needs to change whenever a new value appears within the sequence for column D. Again when this changes the blank row should be left and then the new value rolled down until it changes again. I have tried in vain to try and create a macro within Excel to do this but have failed miserably.
I'm creating a calculator to determine the distance & travel time on a xy grid for a game. I'll share what I have so far to display the big picture. Here’s what I’ve accomplished so far:
#1) Cells A1 & B1 are dynamic, they are frequently changed so distance and time may be displayed. Cells A2 & B2 do not change.
#2) I use the following formula to determine the distance between cells, displayed in C2: =SQRT((A2-$A$1)^2+(B2-$B$1)^2)
#3) Next I multiply C2 by a number so that distance is measured correctly per unit type for this game. (I hide this column). Therefore, cell D3 has the following formula: =C2*20
#4) Now my problem, cell E3 - Correctly displaying time. I have the following formula in E3: =TIME(0,D2,0)
This works great!! That is until the time goes beyond 24 hours, then it rolls over. I've tried right clicking and setting the custom format of E3 to [hh]:mm, it still rolls over. I’ve also tried setting formatting to d:[hh]:mm, if I do this then the output time displayed is incorrect.
Ideally I would love a simple fix so that the time will roll over and show days. (I would like a display similar to d:hh:mm).
I’ve done a fair bit of research online and either I'm missing something simple or this is more complex than I thought. And not to make this any more confusing, but I would love whatever I accomplish here to be useable in compatibility mode ...
[URL]...... I need to put more dynamics and fill this data set from other sheets, so I'm putting formula into column A, and this force chart react and insert another category. In other words I want to have this fill only if data present on different tab(worksheet).
That what I have now:
[Code] ........
is there any other COUNTB,C,D??? COUNTA Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
I'm trying to make a vacation accrual sheet, and I think I have the basics of it down, but I'm running into a problem figuring out how to put in roll over days from the previous year.
Every year on January 1rst, accrual resets. Any days that haven't been used are eligible to be used for 3 months after January 1rst, but after that (on April 1rst), the old days are gone, and the employee is back to using the year's current accrual days. I'm not sure how to do that.
I have case where i need to roll up data based on the hierarchy. I have hierarchy of 4 level as follow:
Level1 Level2 Level3 Level4 Country Region1 Dept1 Unit 1 Country Region1 Dept1 Unit 2 Country Region1 Dept1 Unit 3 Country Region1 Dept1 Unit 4 Country Region1 Dept1
[Code] ........
The data in all sheets is identical...so I created a macro to create template based on the rage. The problem is that the template is good for level 4 but not good for other level.
The problem that I have is rolling over the sum total after the meter read reaches thousand it starts at 1 again, but I need the total KMs traveled for example in the car dash board the odometer shows 945 when I started my trip and end at 24, how would I set up a formula in excel to calculate the total KMs traveled
