VBA Help

[trstbmbk4me]

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?

 

[Skyybot]

In your current code, once you add a record when saving the End(xlUp) will be the next line to add the next save record. So, every save will be on the next line. If you always save in the same order (i.e. - Excel first then PDF) you can change the PDF Hyperlink Offset to (-1,7).

 

[trstbmbk4me]

In your current code, once you add a record when saving the End(xlUp) will be the next line to add the next save record. So, every save will be on the next line. If you always save in the same order (i.e. - Excel first then PDF) you can change the PDF Hyperlink Offset to (-1,7).

Thank you! I will not be the only one using the sheet-so what if someone else saves it incorrectly? Is there a way to make 1 button to run both macros avoiding the incorrect order?

 

[Skyybot]

Yes. Have only one button and assign this Macro to it.

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"

SaveInvAsExcel
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(-1, 7), Address:=path & fname & ".xlsx"


End Sub

 

[trstbmbk4me]

Yes. Have only one button and assign this Macro to it.

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"

SaveInvAsExcel
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(-1, 7), Address:=path & fname & ".xlsx"


End Sub

Thank you for that...it kind of worked. It is putting the pdf and xlsx on the same line, but it is still adding a second line. The second line is the invoice number and customer name, but every other cell is blank. I am still missing something. I really know nothing about VBA so I am not sure what to change to correct this.

 

[Skyybot]

Sorry, I misunderstood what you meant. I thought you only wanted the links on the same line. If it's not done by someone before I get back from work, I'll fix the code when I get home.

 

[Skyybot]

Try this.

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"

SaveInvAsExcel
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(-1, 7), Address:=path & fname & ".xlsx"


End Sub

 

[trstbmbk4me]

matt inv.xlsm
	T
3
Invoice Template

I am trying the mini-sheet thing. No idea if it will work.

Your change saved it, but it still saves two rows. I have a feeling it is something so small, but I can't seem to find it. Frustrating! Thanks for your help on this...saving my sanity!

 

[Skyybot]

Change last row assignment. Try this.

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
lRow As Long, nextRow As Long
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

lRow = Sheet3.UsedRange.Rows.Count
nextRow = lRow + 1
Set nextrec = Sheet3.Range("A" & nextRow)

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"

SaveInvAsExcel
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 lRow As Long
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
lRow = Sheet3.UsedRange.Rows.Count
Set nextrec = Sheet3.Range("A" & lRow)

'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

 

[trstbmbk4me]

Change last row assignment. Try this.

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
lRow As Long, nextRow As Long
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

lRow = Sheet3.UsedRange.Rows.Count
nextRow = lRow + 1
Set nextrec = Sheet3.Range("A" & nextRow)

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"

SaveInvAsExcel
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 lRow As Long
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
lRow = Sheet3.UsedRange.Rows.Count
Set nextrec = Sheet3.Range("A" & lRow)

'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

Sorry, but that didn't seem to work either. I have gone over it and over it and cannot see the issue with the code. Frustrating!