Automatic receipt creator not working

[shibigoku]

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.

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

 

[shibigoku]

I found the problem. I was executing the wrong the wrong macro but now I do have an error on line Set cfws = Worksheets("Monthly data"). It's in french but it's execution error #9.

 

[Fluff]

That means that you don't have a sheet called "Monthly Data" in the active workbook.

 

[shibigoku]

I found the problem to that one too. Monthly data and Template were the name of the worksheets so I added them to my main file and renamed the sheets accordingly.
Now I can execute the file but it loads and rewrites the original data G and H and also it's not writing anything correctly on the template + there's no pdf created.
Back to debugging.

 

[Fluff]

What is the value of Lastrow when you run the code?

 

[shibigoku]

It was this line
Fname = "DCN #" & cfws.Range("A" & i).Value & " receipt"

I kept the original
'Fname = fileloc & filename & ".pdf"

and instead changed the name in filename. The code works and creates the receipts. Now I need to add the functionality to send these receipts by email. I found another thread here that explains how : VBA code to convert excel to pdf and email it as attachment
I'll try by myself first and ask help when I'm stuck. Thanks

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 = "DCN #" & cfws.Range("A" & i).Value & " receipt"
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"
With ctws
.ExportAsFixedFormat Type:=xlTypePDF, filename:=Fname
End With

Next i

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

 

[Fluff]

Glad you got it sorted.
If you need help with the email side of things, it would be best to start a new thread.

 

[shibigoku]

Ok I will.