Forum Discussion

Joseph Fancher's avatar
Joseph Fancher
Copper Contributor
Mar 02, 2018

Help with Queries or VBA code, trying to separate a customer list into separate files

Hello,

 

I have a list of about 5000 customers (name, address and price estimate) from which I need to extract all three parameters from the list and create a new sheet (or workbook) for each customer BUT the  data needs to be written to the new sheet/workbook in a specific template; specific to my invoice template (which I have made in excel). I have been doing this one by one, (please see file attached  with a sample set of customer list data and my invoice/price template), by copying the data, creating a new excel sheet and pasting. How do automate this process so that I can write each customer with respective data to their own invoice?

 

 

The font color for the desired data I am trying to pull is in red in the SAMPLE file attached. Can someone please point me in the right direction or help me figure out an automated way to do this? 

BI & Data Analytics
Need Help
  • Joseph Fancher's avatar
    Joseph Fancher
    Copper Contributor
    Mar 02, 2018

    So I have made some progress... I found this video that helps me write a VBA code to extract data from an Invoice and into another sheet. But I think I want to do precisely the opposite. Link below to help illustrate:

     

    https://www.youtube.com/watch?v=r4hYRIn-0nA

     

    Code below that has been written to do the opposite of what I need it to do... I need this code to generate new files that are basically the same template as this invoice template.... so I assume I need to switch some code around but not sure exactly what to do. But, becasue I need to run through a list, I would guess that I need to add a loop somewhere in here.... Does anyone have any recommendations ?

     

     

    Sub InvoiceReport()
    Dim myFile As String, lastRow As Integer
    myFile = "C:\INVOICES\" & Sheets("Sheet1").Ranges("B5") & "_" & Sheets("Sheet1").Ranges("F1") & "_" & Format(Now(), "yyyy-mm-dd") & ".pdf"
    lastRow = Sheets("Sheet2").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
    'Transfer data to Sheet2
    Sheets("Sheet2").Cells(lastRow, 1) = Sheets("Sheet1").Range("B5")
    Sheets("Sheet2").Cells(lastRow, 2) = Sheets("Sheet1").Range("F1")
    Sheets("Sheet2").Cells(lastRow, 3) = Sheets("Sheet1").Range("B5")
    Sheets("Sheet2").Cells(lastRow, 4) = Now
    Sheets("Sheet2").Hyperlinks.Add Ancher:=Sheets("Sheet2").Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile
    'Create invoice in PDF format
    Sheets("Sheet1").ExportAsFixedFormat Type:=xlTypePDF, Filename:=myFile
    Application.DisplayAlerts = False
    'Create invoice in XLSX format
    ActiveWorkbook.SaveAs "C\INVOICES\" & Sheets("Sheet1").Range("B5") & "_" & Sheets("Sheet1").Range("F1") & "_" & Format(Now(), "yyyy-mm-dd") & ".xlsx", FileFormat:=51

    'Reset specific invoice values
    'Sheets("Sheet1").Range("B5").ClearContents
    'Sheets("Sheet1").Range("F1").ClearContents
    'Sheets("Sheet1").Range("I36").ClearContents
    Application.DisplayAlerts = True

    End Sub

     

     

Resources