using VBA to save an entire workbook

[csenor]

I got this code from a MrExcel podcast. It allows you to keep a workbook open, but save a copy of this workbook to a designated folder with a specified name. The way it works now, though, it only saves the first sheet. I need the second sheet to save as well. What do I need to change? I also want to lock the date / time so it doesn't recalculate upon opening the file in the future. I got the little bit of code at the bottom from another post in this forum, but don't know how to implement it. Can anyone help?

Sub NextInvoice()
Range("i8").Value = Range("i8").Value + 1
Range("b6:b12,e6:e9,a20:g27").ClearContents
End Sub

Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook with date/time locked to that date
ActiveSheet.Copy
NewFN = "C:\Users\Chris\Documents\Pawn Shop Invoices\Inv_" & Range("i8").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$I$9" Then Target.Offset(0, 1) = Date
End Sub

 

[ZAX]

I understood from the code that the date is in range J9 try this code.

Sub SaveInvWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook with date/time locked to that date
Range("j9").Select
Selection.Copy
Range("j9").PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(Array(ActiveSheet.Name, Sheets(2).Name)).Copy
NewFN = "C:\Users\Chris\Documents\Pawn Shop Invoices\Inv_" & Range("i8").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

ZAX

 

[csenor]

Thank you very much. I think it worked. Now I have another line of code that I need help with. The NextInvoice sub clears the contents of the invoice and moves the counter up one. My issue is that I have attached inserted pictures to the invoice that I also need to be cleared when the macro is ran. How do I word the code to search the document for the pictures and erase them after saving?

Sub NextInvoice()
Range("h6").Value = Range("h6").Value + 1
Range("b6:b12,e6,e9,a20:g27").ClearContents
End Sub

 

[csenor]

Is there also a way to make the saved .xlsx file read only?

 

[ZAX]

I hope this helps

Shapes.SelectAll
Selection.Delete

And This One For the read only:

ActiveWorkbook.SaveAs NewFN, "xlsx", ReadOnlyRecommended:=True

ZAX

_{The Early Bird Catches The Worm}

 

[csenor]

will the shapes.selectall delete a rectangle on the top of the page that I made into a button by assigning the macro to it? If it does, what's my alternative?

 

[csenor]

ZAX,

The code you sent me changes the cell that contains the =now() into the date at the time I run the macro. I want the date to only be saved in the saved invoice and leave the invoice with the =now() cell in tact. Is it just a matter of changing the position of the code to make it execute later?