[Free ebook]Ready to Use 101 Powerful Excel VBA Code Just Copy - Paste - Run (For Functional Users) by Anil Nahar

Difference between Worksheets and Sheets in VBA

Pick Names At Random From A List – Excel VBA

Macro to Pick Names at Random from a List

Sub PickNamesAtRandom()

Dim HowMany As Integer
Dim NoOfNames As Long
Dim RandomNumber As Integer
Dim Names() As String 'Array to store randomly selected names
Dim i As Byte
Dim CellsOut As Long 'Variable to be used when entering names onto worksheet
Dim ArI As Byte 'Variable to increment through array indexes

Application.ScreenUpdating = False

HowMany = Range("D3").Value
CellsOut = 6

ReDim Names(1 To HowMany) 'Set the array size to how many names required
NoOfNames = Application.CountA(Range("A:A")) - 1 ' Find how many names in the list
i = 1

Do While i <= HowMany
RandomNo:
    RandomNumber = Application.RandBetween(2, NoOfNames + 1)
    'Check to see if the name has already been picked
    For ArI = LBound(Names) To UBound(Names)
        If Names(ArI) = Cells(RandomNumber, 1).Value Then
            GoTo RandomNo
        End If
    Next ArI
    Names(i) = Cells(RandomNumber, 1).Value ' Assign random name to the array
    i = i + 1
Loop

'Loop through the array and enter names onto the worksheet
For ArI = LBound(Names) To UBound(Names)

    Cells(CellsOut, 4) = Names(ArI)
    CellsOut = CellsOut + 1

Next ArI

Application.ScreenUpdating = True

End Sub

Watch the Video

[Free ebook]Ready to Use 101 Powerful Excel VBA Code 2020 (For Functional Users) by Anil Nahar

[Free ebook Download]Excel - 51 Awesome Macros: Save time and be more productive by Philippe A. Louis

Description

[Free ebook]Advance Excel Formula (For all users) Ready to use Customize Function-Anil Nahar

Free Code VBA the Office programming power solution

[FREE Invoice Generator Template] Save Excel Invoice as PDF

How does this Invoice Generator Template Works?

1. Details – This is where you need to specify the details of the sale/transaction. To keep everything together, I have created one row for each record. All the details of a transaction are recorded in the row.
2. Invoice Template – This is a placeholder template of the invoice where some fields are left empty.  I need to generate a separate invoice for all the sale records and the details for each invoice is picked up from the Details worksheet.

1. Details for the client and the sale transaction are picked up and the invoice template sheet is populated with these details.
2. A new workbook is created that has the details of the selected client (on which you double-clicked).
3. This workbook is saved as a PDF in the Invoice PDF folder.
4. The new workbook is closed without saving.

Modifying the Invoice Generator Template

Sub CreateInvoice(RowNum As Integer)
Application.ScreenUpdating = False
Dim wb As Workbook
Dim sh As Worksheet
With shInvoiceTemplate
.Range("D10") = shDetails.Range("A" & RowNum)
.Range("D11") = shDetails.Range("B" & RowNum)
.Range("D12") = shDetails.Range("C" & RowNum)
.Range("B15") = shDetails.Range("D" & RowNum)
.Range("D15") = shDetails.Range("F" & RowNum)
.Range("D16") = shDetails.Range("G" & RowNum)
.Range("D18") = shDetails.Range("E" & RowNum)
End With
FPath = "C:\Users\sumit\Desktop\Invoice PDFs"
Fname = Format(shInvoiceTemplate.Range("D10"), "mmmm yyyy") _
& "_" & shInvoiceTemplate.Range("D12")
shInvoiceTemplate.Copy
ActiveSheet.Name = "InvTemp"
Set wb = ActiveWorkbook
Set sh = ActiveSheet
sh.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
FPath & "\" & Fname, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
wb.Close SaveChanges:=False
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub

1. The first highlighted section is what takes the details from the Details sheet and populates the invoice template. If you decide to modify the invoice template, you need to make sure you are picking the right details by modifying this part of the code.
2. This line specifies the folder location. In my case, it was a folder on my Desktop. You can specify the address of the folder where you want the invoices to be saved.

Where is the code in the workbook?

1. Click the Developer tab.
2. Click the Visual Basic option. This will open the VB Editor window.
3. In the Visual Basic editor, double-click on the Module to open its code window. You’ll find the code mentioned above.

In case you’re creating a template yourself, you may not find the Module in a new workbook. You need to right-click on any of the workbook objects, go to Insert, and then click on Module. This will insert a new module. 

Making the Double-click functionality to Work

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Cells <> "" And Target.Column = 2 Then
Cancel = True
Call CreateInvoice(Target.Row)
End If
End Sub

• Right-click on the ‘Details’ worksheet tab
• Click on the ‘View Code’ option.
• Copy and paste the above code into the code window that appears.

1. Checks if the cell that has been double-clicked has the client details or not. It uses the IF statement to check and run the code only if the cell is not empty and in Column B.
2. If both the specified criteria are met, it disables double-click functionality (which is to get into the edit mode) and calls the ‘CreateInvoice’ subroutine, which is stored in the Module. It also passes the row number value to the subroutine. For example, if I double-click on the client name in the third row, it will pass 3 as the value to the CreateInvoice subroutine.
3. Once the ‘CreateInvoice’ subroutine is executed – which creates the PDF of the invoice – it ends.

Saving the Invoice Template as Excel (instead of PDF)

Sub CreateInvoice(RowNum As Integer)
Application.ScreenUpdating = False
Dim wb As Workbook
Dim sh As Worksheet
With shInvoiceTemplate
.Range("D10") = shDetails.Range("A" & RowNum)
.Range("D11") = shDetails.Range("B" & RowNum)
.Range("D12") = shDetails.Range("C" & RowNum)
.Range("B15") = shDetails.Range("D" & RowNum)
.Range("D15") = shDetails.Range("F" & RowNum)
.Range("D16") = shDetails.Range("G" & RowNum)
.Range("D18") = shDetails.Range("E" & RowNum)
End With
FPath = "C:\Users\sumit\Desktop\Invoice PDFs"
Fname = Format(shInvoiceTemplate.Range("D10"), "mmmm yyyy") _
& "_" & shInvoiceTemplate.Range("D12")
shInvoiceTemplate.Copy
ActiveSheet.Name = "InvTemp"
Set wb = ActiveWorkbook
Set sh = ActiveSheet
sh.Name = Fname
wb.SaveAs Filename:=FPath & "\" & Fname
wb.Close SaveChanges:=False
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub