trstbmbk4me
New Member
- Joined
- Sep 17, 2023
- Messages
- 9
- Office Version
- 365
- Platform
- Windows
I have followed a video series on youtube to create an invoice. That invoice has buttons to do macros/vba. it works very well and I am almost happy with it.
One of the buttons is to save the invoice as a PDF the other button saves as a .xlsx file. Each of these work and paste the hyperlink onto the order records sheet. However, clicking the two seperate buttons saves it on two different lines when it should be on the same line.
The series I followed and enjoyed was https://www.youtube.com/playlist?list=PLA3JEasWtYad0OeX78k0gFhxm5qnnaD57.
The VBA used for the two functions are as follows:
Sub SaveAsPdf()
Dim invno As Long
Dim custname As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Byte
Dim path As String
Dim fname As String
Dim nextrec As Range
invno = Range("C3")
custname = Range("B10")
amt = Range("I41")
dt_issue = Range("C5")
term = Range("C6")
path = "C:\Users\rhond\OneDrive\Desktop\trial inv for matt\"
fname = invno & " _ " & custname
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & fname
Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = dt_issue + term
Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 6), Address:=path & fname & ".pdf"
End Sub
Sub SaveInvAsExcel()
Dim invno As Long
Dim custname As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Byte
Dim path As String
Dim fname As String
Dim nextrec As Range
invno = Range("C3")
custname = Range("B10")
amt = Range("I41")
dt_issue = Range("C5")
term = Range("C6")
path = "C:\Users\rhond\OneDrive\Desktop\trial inv for matt\"
fname = invno & " _ " & custname
'copy the invoice sheet to a new workbook
Sheet1.Copy
'delete all the buttons on the worksheet
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
'save the new workbook to a specified folder
With ActiveWorkbook
.Sheets(1).Name = "Invoice"
.SaveAs Filename:=path & fname, FileFormat:=51
.Close
End With
'need to put the details of the invoice in the record of invoices sheet
Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = dt_issue + term
Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".xlsx"
End Sub
I can't for the life of me figure out how to put both of these in different cells in the same row. Any ideas?
One of the buttons is to save the invoice as a PDF the other button saves as a .xlsx file. Each of these work and paste the hyperlink onto the order records sheet. However, clicking the two seperate buttons saves it on two different lines when it should be on the same line.
The series I followed and enjoyed was https://www.youtube.com/playlist?list=PLA3JEasWtYad0OeX78k0gFhxm5qnnaD57.
The VBA used for the two functions are as follows:
Sub SaveAsPdf()
Dim invno As Long
Dim custname As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Byte
Dim path As String
Dim fname As String
Dim nextrec As Range
invno = Range("C3")
custname = Range("B10")
amt = Range("I41")
dt_issue = Range("C5")
term = Range("C6")
path = "C:\Users\rhond\OneDrive\Desktop\trial inv for matt\"
fname = invno & " _ " & custname
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, ignoreprintareas:=False, Filename:=path & fname
Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = dt_issue + term
Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 6), Address:=path & fname & ".pdf"
End Sub
Sub SaveInvAsExcel()
Dim invno As Long
Dim custname As String
Dim amt As Currency
Dim dt_issue As Date
Dim term As Byte
Dim path As String
Dim fname As String
Dim nextrec As Range
invno = Range("C3")
custname = Range("B10")
amt = Range("I41")
dt_issue = Range("C5")
term = Range("C6")
path = "C:\Users\rhond\OneDrive\Desktop\trial inv for matt\"
fname = invno & " _ " & custname
'copy the invoice sheet to a new workbook
Sheet1.Copy
'delete all the buttons on the worksheet
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
'save the new workbook to a specified folder
With ActiveWorkbook
.Sheets(1).Name = "Invoice"
.SaveAs Filename:=path & fname, FileFormat:=51
.Close
End With
'need to put the details of the invoice in the record of invoices sheet
Set nextrec = Sheet3.Range("A1048576").End(xlUp).Offset(1, 0)
nextrec = invno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = amt
nextrec.Offset(0, 3) = dt_issue
nextrec.Offset(0, 4) = dt_issue + term
Sheet3.Hyperlinks.Add anchor:=nextrec.Offset(0, 7), Address:=path & fname & ".xlsx"
End Sub
I can't for the life of me figure out how to put both of these in different cells in the same row. Any ideas?