Help with Podcast 1505

[DonnyK]

Hello all,
I am not an avid use of excel. I ama small business owner (Painting Contractor) and have been trying to make an invoice that works for me. I like the new invoice Save and clear. It works well with a single page on a sheet however I sometimes have multiple pages to one invoice and have been trying to apply that module to what I have bult. I'm sure there is a better way to do what I made. I just want to be able to clear the invoice areas save to my file and the register. Below is what I did to do that but there is something wrong for sure, only the file is being saved. Can anyone help me please?

Sub PostToRegster()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheet("Long Invoice")
Set WS2 = Worksheet("Register")

'Figure out which row
NextRow = WS2.Cells(Row.Count, 1).End(xlUp).Row + 1

'Write the important valuse to Register
WS2.Cells(NextRow, 1).Resize(1, 5).Value = Array(WS1.Range("B6"), WS1.Range("B8"), _
WS1.Range("G1"), WS1.Range("K6"), WS1.Range("H9"))
End Sub

Sub NextInvoice()
Range("G1").Value = Range("G1").Value + 1
Range ("A15:J45"), Range("A67:J99"), Range("A120:J152"), Range("A173:J205"), Range("A226:J258"), Range("A279:J311"), Range("A332:J364").ClearContents
End Sub
Sub SaveInvoiceWithNewName()
Dim NewFN As Variant
'Copy Invoce to a New Workbook
ActiveSheet.Copy
NewFN = "C:\2022 Invoices\Inv" & Range("G1").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

 

[rlv01]

You question pertains to podcasts 1505 and 1808.

MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.

www.mrexcel.com

Fair warning: I have not watched either of them, but here is what you might try (not tested):

Sub PostToRegster()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim NextRow As Long

    Set WS1 = Worksheets("Long Invoice")
    Set WS2 = Worksheets("Register")

    'Figure out which row
    NextRow = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row + 1

    'Write the important valuse to Register
    WS2.Cells(NextRow, 1).Resize(1, 5).Value = Array(WS1.Range("B6"), WS1.Range("B8"), WS1.Range("G1"), WS1.Range("K6"), WS1.Range("H9"))
End Sub

Sub NextInvoice()
    Range("G1").Value = Range("G1").Value + 1
    Application.Union(Range("A15:J45"), Range("A67:J99"), Range("A120:J152"), Range("A173:J205"), Range("A226:J258"), Range("A279:J311"), Range("A332:J364")).ClearContents
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

 

[DonnyK]

You question pertains to podcasts 1505 and 1808.

MrExcel Excel Videos

Learn Excel from MrExcel - Excel tips and tricks from Bill Jelen.

www.mrexcel.com

Fair warning: I have not watched either of them, but here is what you might try (not tested):

Sub PostToRegster()
    Dim WS1 As Worksheet
    Dim WS2 As Worksheet
    Dim NextRow As Long

    Set WS1 = Worksheets("Long Invoice")
    Set WS2 = Worksheets("Register")

    'Figure out which row
    NextRow = WS2.Cells(WS2.Rows.Count, 1).End(xlUp).Row + 1

    'Write the important valuse to Register
    WS2.Cells(NextRow, 1).Resize(1, 5).Value = Array(WS1.Range("B6"), WS1.Range("B8"), WS1.Range("G1"), WS1.Range("K6"), WS1.Range("H9"))
End Sub

Sub NextInvoice()
    Range("G1").Value = Range("G1").Value + 1
    Application.Union(Range("A15:J45"), Range("A67:J99"), Range("A120:J152"), Range("A173:J205"), Range("A226:J258"), Range("A279:J311"), Range("A332:J364")).ClearContents
End Sub

(Tip: when posting code, please try to use 'code tags' to format the code as I have done above

How to Post Your VBA Code

Post your VBA code.

www.mrexcel.com

)

rlv01,
Thank you,
I will try this to see if it works, and let you know.