automatic next number..

[etrev]

I need help with a macro that Saves file in PDF, auto-increments the #, and clears contents in certain cells. I'm running it by inserting a button and assigning macro to it

The idea is to make it look like this: JT-0416-001 (THIS IS MY MAIN PROBLEM)
and when I run the macro it should clear the contents in certain cells and change the number to JT-0416-002, and so on.
Also, for the next month, it should automatically change to JT-0516-001.

This is what I currently have:

Sub NextNumber()
Range("I6").Value = Range("I6").Value + 1
Range("A17:J33").ClearContents
Range("A10:D15").ClearContents
Range("F10:L15").ClearContents
End Sub

Sub SaveInvAsPDF()
Dim NewFN As Variant
NewFN = "C:\Users\......." & Range("I6").Value & ".pdf"
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=NewFN, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=False
NextNumber
End Sub

Your help is greatly appreciated.. Thanks!
__________________________________________________________________

Mod edit : thread moved to appropriate forum !​

 

[Luke M]

Hi etrev, and welcome to the forum!

I think this would work best as just a single macro. This one will search your designated folder, and figure out which number it should pull next.

Sub SaveInvAsPDF()
Dim fPath As String
Dim fName As String
Dim fCount As Long
Dim strTest As String

'What folder do we save in? Change as appropriate
fPath = "C:\Users\Files"

'Error checking
If Right(fPath, 1) <> "\" Then
    fPath = fPath & "\"
End If

'Calculate the month
fName = "JT-" & Format(Date, "mmyy")

strTest = Dir(fPath & fName & "*.pdf")

'Find how many files we already have?
'Search for files in folder matching pattern
fCount = 1
Do Until strTest = ""
    fCount = fCount + 1
    strTest = Dir()
Loop

'Finish building file name
fName = fName & "-" & Format(fCount, "000") & ".pdf"

'Export as PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=fPath & fName, _
    Quality:=xlQualityStandard, IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, OpenAfterPublish:=False

'Clear some random cells
Range("A17:J33").ClearContents
Range("A10:D15").ClearContents
Range("F10:L15").ClearContents

End Sub

 

[etrev]

Thanks Luke M, but how would you make it also display the number in say... cell I6, and change it everytime the macro is ran as well?

Range("I6").Value = Left(fName, 11)

seems to be doing the trick.

 

[Luke M]

Yep, could do that, or just use the fCount variable directly.

Range("I6").Value = fCount