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?

    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?

    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.
    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
    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
    On Error GoTo 0
    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
    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
    ActiveWindow.SelectedSheets.PrintOut Copies:=z, ActivePrinter:= _
    "Zebra S500/105S on COM2:", Collate:=True
    Next i
    End With
    End Sub

    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.

    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.

    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)
    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.