Hi,
I'm trying to create a program that would take some data and create automatic receipts from this data. This would greatly help at work as the number of receipts we have to do keeps on increasing.
I don't know a thing about VBA so I'm just searching online for solutions. I found this old thread and decided to work on it. Automatically create PDF-invoices with Excel
I want to the program to send the receipts by email and only if column N says no but as you can see from my code, I'm not there yet.
For now, I just want it to work and create de receipts but when I execute the macro nothing happens. I'm thinking it's because the program doesn't know where the files "Monthly data" and "Raw data" are. Is that correct?
But if that's what it is, how do I tell excel where they are?
Your help is greatly appreciated.
I'm trying to create a program that would take some data and create automatic receipts from this data. This would greatly help at work as the number of receipts we have to do keeps on increasing.
I don't know a thing about VBA so I'm just searching online for solutions. I found this old thread and decided to work on it. Automatically create PDF-invoices with Excel
I want to the program to send the receipts by email and only if column N says no but as you can see from my code, I'm not there yet.
For now, I just want it to work and create de receipts but when I execute the macro nothing happens. I'm thinking it's because the program doesn't know where the files "Monthly data" and "Raw data" are. Is that correct?
But if that's what it is, how do I tell excel where they are?
Your help is greatly appreciated.
VBA Code:
Sub CopyToTemplate()
Dim cfws As Worksheet
Dim ctws As Worksheet
Dim lastrow As Long
Dim i As Long
Dim fileloc As String
Dim filename As String
Dim Fname As String
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Set cfws = Worksheets("Monthly data")
Set ctws = Worksheets("Template")
lastrow = cfws.Cells(cfws.Rows.Count, "B").End(xlUp).Row
fileloc = "C:\Users\dave.i\Documents\Project\Receipts"
For i = 2 To lastrow
filename = "File " & i
ctws.Range("C41").Value = "Sub ID " & cfws.Range("A" & i).Value
ctws.Range("D14").Value = cfws.Range("B" & i).Value
ctws.Range("C43").Value = cfws.Range("B" & i).Value
ctws.Range("D13").Value = cfws.Range("C" & i).Value
ctws.Range("C42").Value = cfws.Range("C" & i).Value
ctws.Range("C44").Value = cfws.Range("D" & i).Value
ctws.Range("C45").Value = cfws.Range("E" & i).Value
ctws.Range("D15").Value = cfws.Range("D" & i).Value & ", " & cfws.Range("E" & i).Value
ctws.Range("I45").Value = cfws.Range("F" & i).Value
ctws.Range("I46").Value = cfws.Range("G" & i).Value
ctws.Range("I47").Value = cfws.Range("H" & i).Value
ctws.Range("C45").Value = cfws.Range("E" & i).Value
ctws.Range("B51").Value = cfws.Range("I" & i).Value
ctws.Range("H50").Value = cfws.Range("J" & i).Value
ctws.Range("B56").Value = "Charged to " & cfws.Range("K" & i).Value & " on"
ctws.Range("B57").Value = cfws.Range("L" & i).Value
'Fname = fileloc & filename & ".pdf"
Fname = "DCN #" & cfws.Range("A" & i).Value & " receipt"
With ctws
.ExportAsFixedFormat Type:=xlTypePDF, filename:=Fname
End With
cfws.Range("F" & i).Value = Date
Next i
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub