ipbr21054
Well-known Member
- Joined
- Nov 16, 2010
- Messages
- 5,226
- Office Version
- 2007
- Platform
- Windows
Morning,
I am using the code below but not sure where i put the IF Else code"if correct name for it"
This is how it works.
I select a customer from a drop down box in cell G13
The cell M11 is also a drop down where the user must select BIKE or CAR
Cell M18 is also a drop down.
Here is where im stuck.
When the code runs it collects data from another worksheet using an IF statement & places them in cells in column O
The code then copies / pastes values to a worksheet called INVOICES.
If the cell value at M11 is BIKE then all the code can run * do as requested.
BUT
If the cell value is CAR then the code can continue to put values in column O BUT there is no need to do the COPy / PASTE part, just jump this & continue to print
I think the part we are looking at is shown in Red but need advice before i continue,Thanks
I am using the code below but not sure where i put the IF Else code"if correct name for it"
This is how it works.
I select a customer from a drop down box in cell G13
The cell M11 is also a drop down where the user must select BIKE or CAR
Cell M18 is also a drop down.
Here is where im stuck.
When the code runs it collects data from another worksheet using an IF statement & places them in cells in column O
The code then copies / pastes values to a worksheet called INVOICES.
If the cell value at M11 is BIKE then all the code can run * do as requested.
BUT
If the cell value is CAR then the code can continue to put values in column O BUT there is no need to do the COPy / PASTE part, just jump this & continue to print
I think the part we are looking at is shown in Red but need advice before i continue,Thanks
Rich (BB code):
Private Sub Print_Invoice_Click()
Dim sPath As String, strFileName As String
If Range("M11") = "" Then
MsgBox ("PLEASE SELECT A MODEL"), vbCritical, "VEHICLE TYPE WAS NOT SELECTED"
Range("M11").Select
Exit Sub
End If
If Range("L18") = "" Then
MsgBox ("PLEASE SELECT A PAYMENT TYPE "), vbCritical, "PAYMENT TYPE WAS NOT SELECTED"
Range("L18").Select
Exit Sub
End If
If Range("O14") = "" Then
MsgBox ("PLEASE ENTER THE BITING"), vbCritical, "NO BITING WAS ENTERED"
Range("O14").Select
Exit Sub
End If
If Range("O17") = "" Then
MsgBox ("PLEASE ENTER KEY TYPE"), vbCritical, "NO KEY TYPE WAS ENTERED"
Range("O17").Select
Exit Sub
End If
strFileName = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
If Dir(strFileName) <> vbNullString Then
MsgBox "INVOICE " & Range("L4").Value & " WAS NOT SAVED AS IT ALLREADY EXISTS", vbCritical + vbOKOnly, "INVOICE NOT SAVED MESSAGE"
Exit Sub
End If
Dim x As Long
Application.ScreenUpdating = False
Workbooks.Open ("C:\Users\Ian\Desktop\REMOTES ETC\DR\EXCEL WORKSHEETS\MOTORCYCLES.xlsm")
Worksheets("INVOICES").Activate
Worksheets("INVOICES").Rows("3:3").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveWorkbook.Close SaveChanges:=True
Set WB = Workbooks.Open(fileName:="C:\Users\Ian\Desktop\REMOTES ETC\DR\EXCEL WORKSHEETS\MOTORCYCLES.xlsm")
Workbooks("DR.xlsm").Sheets("INV").Range("G13").Copy
WB.Sheets("INVOICES").Range("A3").PasteSpecial xlPasteValues
Workbooks("DR.xlsm").Sheets("INV").Range("L16").Copy
WB.Sheets("INVOICES").Range("B3").PasteSpecial xlPasteValues
Workbooks("DR.xlsm").Sheets("INV").Range("L15").Copy
WB.Sheets("INVOICES").Range("C3").PasteSpecial xlPasteValues
Workbooks("DR.xlsm").Sheets("INV").Range("O14").Copy
WB.Sheets("INVOICES").Range("D3").PasteSpecial xlPasteValues
Workbooks("DR.xlsm").Sheets("INV").Range("O17").Copy
WB.Sheets("INVOICES").Range("E3").PasteSpecial xlPasteValues
Workbooks("DR.xlsm").Sheets("INV").Range("L13").Copy
WB.Sheets("INVOICES").Range("F3").PasteSpecial xlPasteValues
Workbooks("DR.xlsm").Sheets("INV").Range("L4").Copy
WB.Sheets("INVOICES").Range("G3").PasteSpecial xlPasteValues
With Sheets("INVOICES")
If .AutoFilterMode Then .AutoFilterMode = False
x = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:G" & x).Sort Key1:=.Range("A1"), Order1:=xlAscending, Header:=xlYes, DataOption1:=xlSortTextAsNumbers
Range("A3").Select
End With
WB.Close True
Workbooks("DR.xlsm").Sheets("INV").Range("G2").Select
Application.CutCopyMode = False
ActiveWorkbook.Save
With ActiveSheet
.ExportAsFixedFormat Type:=xlTypePDF, fileName:=strFileName, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
End With
ActiveWindow.SelectedSheets.PrintOut copies:=1
MsgBox "ONCE PRINTED CLICKING OK WILL" & vbNewLine & vbNewLine & "SAVE INVOICE " & Range("L4").Value & " CLEAR PAGE INFO & DELETE THE GENERATED PDF ", vbExclamation + vbOKOnly, "PRINT SAVE & CLEAR MESSAGE"
Dim MyFile As String
MyFile = "C:\Users\Ian\Desktop\REMOTES ETC\DR\DR SCREEN SHOT PDF\" & Range("G13").Value & ".pdf"
If Dir(MyFile) <> "" Then Kill MyFile
Dim i As Long, lRow As Long, ws As Worksheet
Set ws = Application.Worksheets("DATABASE")
lRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
For i = 6 To lRow
If Trim(Range("G13").Value) = Trim(ws.Cells(i, 1).Value) Then
If ws.Cells(i, 16).Value = "" Then
ws.Cells(i, 16).Value = Range("L4").Value ' adding invoice number to INV sheet "P"
ActiveSheet.Hyperlinks.Add ws.Cells(i, 16), Address:="C:\Users\Ian\Desktop\REMOTES ETC\DR\DR COPY INVOICES\" & Range("L4").Value & ".pdf"
MsgBox "INVOICE " & ws.Cells(i, 16).Value & " WAS HYPERLINKED SUCCESSFULLY" & vbNewLine & vbNewLine & "GENERATED PDF WAS ALSO DELETED ", vbInformation, "HYPERLINK SUCCESSFULL MESSAGE"
Else
If MsgBox("COLUMN CELL P ISNT EMPTY " & ws.Cells(i, 16).Value & " IS ENTERED IN IT." & vbNewLine & "WOULD YOU LIKE TO CORRECT IT ?", vbCritical + vbYesNo, "COLUMN P NOT EMPTY MESSAGE") = vbYes Then
ws.Activate
ws.Cells(i, 16).Select
End If
Exit Sub
End If
End If
Next i
Range("G14:G18").ClearContents
Range("L14:L18").ClearContents
Range("G27:L36").ClearContents
Range("G46:G50").ClearContents
Range("M11").ClearContents
Range("L4").Value = Range("L4").Value + 1
Range("G13").ClearContents
Range("G13").Select
Call PasteIfFormulas_Click
ActiveWorkbook.Save
End Sub
