Hi everybody,
I'm a total beginner at this. I'm trying to create a macro that would ask a user for a serial number and quantity and based on that print the right amount of documents. There are several but's here though.
The Workbook this will be used on is protected. The cell where the serial number is entered is a merged cell. Information is stored on 4-5 worksheets, while only sheet 5 needs to be printed. The serial number consists of a mix of letters and numbers (ie. G19273WE0000001)
This is what I've got so far but it doesn't work for reasons unknown to me.
Sub PrintToPDF()
b = Application.InputBox("First Serial :", , , , , , , 2)
If Len(b) = 17 Then
Sheets("2").Range("A1:A3").Value = b
Else: MsgBox "The serial entered does not contain 17 digits"
End If
Dim avarSplit As Variant
Dim bvarSplit As Variant
avarSplit = (Mid(b, 1, [13]))
bvarSplit = (Mid(b, 14, [4]))
a = Application.InputBox("How many pages would You like to print?:", , , , , , , 1)
If a < 1 Then
MsgBox "Only Chuck Norris can print less than 1 copy at a time and still get stuff printed"
For n = 1 To a
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"PDFCreator on PDFCreator:"
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 2
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
filename = "C:\Documents and Settings\user\Desktop\sodi\" & Sheets("2").Range("A1").Value & ".pdf"
SendKeys filename & "{Enter}", False
Sheets("2").Select
Sheets("2").Range("A1").Value = avarSplit & bvarSplit
Sheets("5").Select
bvarSplit = bvarSplit + 1
Next n
End If
End Sub
I believe I may have defined bvarSplit wrong as a variant but for the life of me I can't figure out how else to define it so that it could be incremented and retain the 0-s in front.
I've tried removing the time delay and else clause about the number of digits, but to no avail.
Using WinXP and Excel 2007. I could really use some help on this.
I could get the following script running, but it doesn't consider the entered serial number when printing the first page.
Sub Print()
a = Application.InputBox("How many pages would You like to print?:", , , , , , , 1)
If a < 1 Then
MsgBox "Only Chuck Norris can print less than 1 copy at a time and still get stuff printed"
Exit Sub
End If
b = Application.InputBox("First serial number :", , , , , , , 2)
If Len(b) = 17 Then
Dim avarSplit As Variant
avarSplit = (Mid(b, 1, [13]))
bvarSplit = (Mid(b, 14, [4]))
For n = 1 To a
ActiveWindow.SelectedSheets.PrintOut Copies:=1
Sheets("2").Select
Sheets("2").Range("A1:A3").Value = avarSplit & bvarSplit
bvarSplit = bvarSplit + 1
Sheets("5").Select
Next n
Else: MsgBox "The serial entered does not contain 17 digits"
End If
End Sub
So what I'm essntially struggling with is this:
1) The script seems to ignore anything printing related
2) How do I retain zeros in the last 4 digits of the serial number while incrementing it
Bookmarks