How to Make a Custom Quote Sheet From an Excel Catalogue

Описание: This is the link for the Quote Template sheet:

https://1drv.ms/x/s!At8ggXIe5OvDqyp1Q...

Here's the link for the Bike Inc. Catalogue sheet:

https://1drv.ms/x/s!At8ggXIe5OvDqy6i6...

Both of these link to a OneDrive where you should be able to download them.

When opening the Quote Template sheet for the first time you may to to close and then reopen it to get it to work.

Below is the Excel Macro code for reference. Note that YouTube does not allow angled brackets in descriptions so * refers to an angled bracket pointing right (greater than) and ** refers to an angled bracket pointing left (less than). The formatting of the code will also be slightly off, so any lines that start on the far left will have to be added to the end of the previous line.

On Error Resume Next

For a = 14 To 1000
If IsEmpty(Cells(a, 3)) = False And IsEmpty(Cells(a, 4)) = True Then

If IsEmpty(Cells(a + 1, 4)) = True And IsEmpty(Cells(a + 2, 4)) = True And IsEmpty(Cells(a + 1, 3)) = True Then
GoTo GoHere
End If

If IsEmpty(Cells(a, 4)) = True And IsEmpty(Cells(a + 1, 4)) = False Then
GoTo GoHere
End If

Find = Range("'[Bikes Inc. Catalogue.xlsx]Sheet1'!$B$6:$B$5000").Find(Cells(a, 3).Value).Address
Row# = Range(Find).Row

Cells(a, 4) = Workbooks("Bikes Inc. Catalogue.xlsx").Sheets("Sheet1").Cells(Row#, 3)
Cells(a, 7).Value = "=VLOOKUP(C" & a & ", '[Bikes Inc. Catalogue.xlsx]Sheet1'!$B$6:$E$5000, 4, 0)"
Cells(a, 8).Value = "=B" & a & "*G" & a

If Cells(a - 1, 1) = "Line" Then
Cells(a, 1).Value = 1
Else
Cells(a, 1).Value = Cells(a - 1, 1) + 1
End If

Cells(a + 1, 1).EntireRow.Insert

If Cells(a, 4).Height * 60 Then
h = (Cells(a, 4).Height * Cells(a, 4).Width) / (Cells(a, 4).Width + Cells(a, 5).Width + Cells(a, 6).Width)
Range(Cells(a, 4), Cells(a, 6)).Merge
Rows(a).RowHeight = h + 30
Else
Range(Cells(a, 4), Cells(a, 6)).Merge
End If

End If

Next a

GoHere:

WaitTime = Now() + TimeValue("00:00:02")
While Now() ** WaitTime
DoEvents
Wend

Call OnEntry

End Sub