PDA

View Full Version : Transfer Data from one sheet to another sheet to create invoices



rsy
04-13-2018, 01:02 PM
Hi All :hi:,

Greetings for the day!:yes

I'm new to VBA. I'm having a task to create invoices. for this I need to transfer dealer demographic information from File DEALER_DATA to Invoice_Format into respective cells. This activity is to be repeated until every unique Dealer code is exhausted/finished. The base will Dealer code from DEALER_DATA file. For each entry made in Invoice_Format file, it should be saved as separate individual excel file where file name will be Dealer code.

I had attached both files for your reference. I want to do it with the help of VBA so that i need not to copy every single time single entry to make invoices.

Not to mention, DEALER_DATA file is sample file and in original I don't know how may rows will be there when it will be downloaded from system. I never know what shall be the last row.

Please help me out, : pray2:
Thanks in advance.

Paul_Hossler
04-13-2018, 05:10 PM
I put the Invoice format into the dealer data workbook - I thought it was cleaner that way



Option Explicit

'Invoices
' B2 = Customer Name
' B3 = Customer Address
' B4 = Customer GSTIN
' B5 = Customer PAN
' H2 = Customer Code
' H5 = Place of Service
' A B C D E F
'Dealer code Customer Code Customer Address Customer GSTIN Customer PAN Place of service

Sub MakeInvoiceWorkbooks()
Dim iDealerRow As Long
Dim wsDealerData As Worksheet, wsInvoice As Worksheet
Dim sInvoicePath As String

Application.ScreenUpdating = False

Set wsDealerData = Worksheets("DEALER_DATA")

For iDealerRow = 2 To wsDealerData.Cells(1, 1).CurrentRegion.Rows.Count

Worksheets("Invoice_Format").Copy After:=Worksheets("Invoice_Format")

Set wsInvoice = ActiveSheet

With wsInvoice
.Range("B2").Value = wsDealerData.Cells(iDealerRow, 2).Value
.Range("B3").Value = wsDealerData.Cells(iDealerRow, 3).Value
.Range("B4").Value = wsDealerData.Cells(iDealerRow, 4).Value
.Range("B5").Value = wsDealerData.Cells(iDealerRow, 5).Value
.Range("H2").Value = wsDealerData.Cells(iDealerRow, 1).Value
.Range("H5").Value = wsDealerData.Cells(iDealerRow, 6).Value

'delete WS just in case
On Error Resume Next
Application.DisplayAlerts = False
Worksheets(.Range("H2").Value).Delete
Application.DisplayAlerts = True
On Error GoTo 0

.Name = .Range("H2").Value

sInvoicePath = ThisWorkbook.Path & Application.PathSeparator & .Name & ".xlsx"

.Move

'delete WB just in case
On Error Resume Next
Application.DisplayAlerts = False
Kill sInvoicePath
Application.DisplayAlerts = True
On Error GoTo 0

ActiveWorkbook.SaveAs Filename:=sInvoicePath, FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
End With

ThisWorkbook.Activate

Next iDealerRow

Application.ScreenUpdating = False

End Sub

rsy
04-14-2018, 10:40 AM
Hi Paul,

Thank you for your revert and assistance. You made my day. This what I was looking for. Thank you once again:bow:

rsy
04-29-2018, 06:31 AM
Hi Paul,:hi:

Greetings for the day!
Hope you are doing well, I came up with couple of few more requirements. I had transferred your code to desired file and also made subtle changes as per the requirement. I'm saving the invoices in pdf format as well. Attached is the workbook for reference. One more worksheet "Invoice_Data"added to this workbook. Now for every Customer code in cell "H8" in worksheet "Invoice", the corresponding invoice entries to be fetched from worksheet "Invoice_Data". Once fetched, sum total to be done at next row where invoice entries ended in "Invoice" sheet. The number of rows of these invoice entries will vary. I had tried to write code for it but unable to execute it. Code is mentioned from "'I Started from here" text.

Last 12 rows are fixed in worksheet "Invoice" and will be present in every invoice next to next last of "Total" row. Values should be as mentioned in this worksheet.

One ready invoice is attached for your reference.

Aside this, Is it possible to send these invoice Pdfs through outlook email to respective Zonal Managers' email ids which is mentioned in column G of worksheet "Dealer_Data".

Thanks for your time and help