Posts by 092202

With Autosave enabled, I have a problem when, on multiple workbooks opened, after closing them, data can be lost. Is there a way to duplicate Autosave and save ALL open workbooks periodically, may be through automatically running macro (without user's involvement) , or with another way?

Re: USB printer in macro

not, don't understand what you mean

Re: USB printer in macro

No, I can't set this to default printer, as it is label printer, and default should be another normal printer.

Does anyone know how to hard code USB printer port in the macro? I have to print on USB printer called "label" , and it is presented in the macro as:
Application.ActivePrinter = "label on Ne06:"

The problem is that "Ne06" can become "Ne08" , "Ne17" or something else if computer is restarted. It is Virtual USB printer port, as seen in Windows Printer settings. It prints always all right, but that Ne thing always changes the number. If you print directly from Excel, there is no problem, but if prints generated by macro, you have to tell macro port name, is this correct? If I would just put
Application.ActivePrinter = "label"
it would not work.
So, my question is : how to overcome this problem?
Thanks

Re: find and change worksheet name

Thank you for suggestion. I used your idea to capture sheet name , my whole macro is below. It's working now. I use variable "n" instead of sheet name. May be you think it can be improved somehow ?
For your info: this macro is printing labels from worksheet in particular format.
[vba]
Sub PrintLabels()
Dim x As Long
Dim y As Long
Dim z As Integer
Dim i As Integer
'for sheetname
Dim n As String

n = ActiveSheet.Name
Sheets(ActiveSheet.Name).Activate
x = InputBox("Enter the beginning row:", "Starting Row")
If Not IsNumeric(x) Or Not x > 1 Or Not x = Int(x) Then x = 2
y = InputBox("Enter the ending row:", "Finishing Row")
If Not IsNumeric(y) Or y < x Or Not y = Int(y) Then y = x
z = InputBox("Enter the number of copies to print:", "Copies to Print", 1)
If Not IsNumeric(z) Or z < 0 Or Not z = Int(z) Then z = 1
MsgBox "Ready to print " & z & " copies of rows " & x & " to " & y & "."

With Application
.DisplayAlerts = False
.ScreenUpdating = False
End With

On Error Resume Next
Sheets("Label").Delete
On Error GoTo 0
Worksheets.Add
With ActiveSheet
.Name = "Label"
.Range("a1") = "Batch:"
.Range("a2") = "Date:"
.Range("a3") = "QTY:"
'.Range("a5") = "Company:"
.Cells.Font.Size = 36
.Range("a4").Font.Size = 80
.Range("a5").Font.Size = 60
.Range("b1").Font.Size = 50
.Range("b3").Font.Size = 50
.Range("a4").Font.Name = "Zebra Scalable"
.Range("a4").Font.Bold = True
.Range("a5").Font.Name = "Zebra Scalable"
.Range("b1:b5").Font.Name = "Zebra Scalable"
.Range("b2").NumberFormat = "m/d/yyyy"
With .PageSetup
.PrintArea = "$A$1:$B$5"
'.LeftMargin = Application.InchesToPoints(0.25)
'.RightMargin = Application.InchesToPoints(0.25)
'.TopMargin = Application.InchesToPoints(0)
'.BottomMargin = Application.InchesToPoints(0)
'.Orientation = xlLandscape
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.25)
.RightMargin = Application.InchesToPoints(0.25)
.TopMargin = Application.InchesToPoints(0)
.BottomMargin = Application.InchesToPoints(0)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
' .PaperSize = 0
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintErrors = xlPrintErrorsDisplayed
End With
'to wrap
Rows("4:4").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
Selection.RowHeight = 260
' Selection.Font.Bold = True
End With
'end wrap
For i = x To y
.Range("b1") = Sheets(n).Cells(i, 1)
.Range("b2") = Sheets(n).Cells(i, 2)
.Range("a4") = Sheets(n).Cells(i, 4)
.Range("a5") = Sheets(n).Cells(i, 5)
.Columns("A:A").ColumnWidth = 90 'adjust as needed
.Columns("B:B").ColumnWidth = 28 'adjust as needed
' to choose printer
Application.Dialogs(xlDialogPrinterSetup).Show
ActiveWindow.SelectedSheets.PrintOut Copies:=z, ActivePrinter:= _
"Zebra S500/105S on COM2:", Collate:=True
Next i
End With
End Sub
[/vba]

I've got macro written for "Sheet1", with "Sheet1" through the code.
I want to use this macro on worksheets with different from "Sheet1" names.
Every time I will use the macro, it will work only on currently open active sheet.
How can I change macro code to work on current macro, even it's name is not "Sheet1" ?

Re: select and print range of rows

it's much more adjustable now, with ColumnWidth.
However, what I found, if A4 sell has got long product description, it does not fit into label (see example.pdf)
Is there any way to make A4 contents automatically go to the next line, if it's too long (A5 should move one line below also). In this case, if A4 is short, it will take only one line, and if it is longer then print area, it will take 2 lines.
Thanks

Re: select and print range of rows

Thank you, dangelor, this last post is very close to what I want to achieve.
I still have to fiddle with correct fonts and printer setup, but at least now (with standard 12 size fonts) it prints out, but does not fit the label.
The biggest problem I have now that printout is too long (see attachment 123.pdf). Is it possible to decrease space (in macro) between 2 columns in printout? so it can be shorter. Also, is it possible to get rid of top and bottom margins in macro?
I understand that setting up correct driver settings for printer in Windows more important, but after playing with it still can't achieve right printout. That's why I am asking how to correct these settings in Excel macro.
Thanks

Re: select and print range of rows

Thank you, Brandtrock
Using this code as template (below), I actually want to print each row from "test file.xls" (attached) separately (each one on individual continues label). However, before printing, I have to change printing output for each row: only data from first 5 sells should be printed out, each cell on individual line, like in "excel output file.xls" that I've attached here.
I provided fonts in cell’s comments. Actually, fonts not so important, I can change fonts later if they do not fit into the label.
Any suggestions how to improve this macro to achieve individual labels printed as I want in "excel output file.xls"?
Thank you

Sub PrintRows()
Dim Times As String
Dim StartRow As String
Dim FinRow As String
StartRow = InputBox("Enter the row to begin printing at: ", "Starting Row")
FinRow = InputBox("Enter the row to end printing at: ", "Finishing Row")
Times = InputBox("Enter the number of copies to print: ", "Copies to Print", 1)
Rows(StartRow & ":" & FinRow).Select
Selection.PrintOut Copies:=Times, Collate:=True
End Sub

Re: select and print range of rows

Quote from dangelor

Hello 092022,

Your problem is not too hard... take a look at the help files for the Inputbox function, the PrintArea property, and the Printout method.

Thanks, dangelor
I've done this macro, but , after selecting rows, HOW TO PRINT OUT THIS RANGE FROM a TO b??

Sub test01()
Dim a As Long, response As Long
a = Application.InputBox( _
Prompt:="Enter the first Rownumber", _
Title:="Select first rownumber:", Type:=1)
b = Application.InputBox( _
Prompt:="Enter the last Rownumber", _
Title:="Select last rownumber:", Type:=1)
Rows(a).Select
' HOW TO PRINT OUT THIS RANGE FROM a TO b??
End Sub

I was hoping that someone might know how to add pop up windows in excel via a macro in order to
- prompt the user to enter number of the first row in the range
- prompt the user to enter number of the last row in the range
- prompt the user to enter number of printed copies
- print previously defined range of rows (or just one) on printer (label printer) in number of copies as defined before

For example, I am using a workbook to print labels (each row is the label), and when I wish to print from row 5 to row 8, I would like a macro to prompt me to enter number 5 as the first row in the range, and then number 8 as last row in the range, and then specify how many times I want to print out each row (label).
Thank you very much for your attention to this thread, may be you could help me.