Change value before closing workbook

[mattless1]

Hi All,

i have a workbook, which saves as pdf and xlsx. it works fine & the code is below
i'm trying to get it to change cell J4 by 1 before it closes. so it changes from number one to number two and so on before it closes.
i have a code that i can get it to work by entering the code in different locations within in the code below. but it save the sheet as the wrong number

I have tried run all Macro's but i get an error when it get to Aftersave.

Sub SvMe()
Dim wb As Workbook, nbk As Workbook
Dim Path As String, File As String, Filename As String
Path = "C:\Users\Music\Desktop\tunbomne"
Filename = Range("J1")
File = Path & "\" & Filename & " " & Format(Date, "dd-mm-yyyy")
Set wb = ThisWorkbook: Set nbk = Workbooks.Add
wb.ActiveSheet.Copy After:=nbk.Sheets(Sheets.Count)
ActiveSheet.Range("A1:J44").ExportAsFixedFormat Type:=xlTypePDF, Filename:=File, Quality:=xlQualityStandard
nbk.SaveAs Filename:=File
nbk.Close savechanges:=False

i can't get the code below in the correct location on the above code to change the number after it saves.

Sub Workbook_AfterSave(ByVal Success As Boolean)
    Dim s As Range
    Set s = Sheets("1").Range("J4")
    s = s + 1
End Sub

Any help would be good.
Many thanks.
Mattless1

 

[DEksel]

It should be in the module ThisWorkbook. If you refer to the first worksheet then use Sheets(1) Works perfect for me. As small counter. Each time I pres Ctrl S - s become larger for 1.

 

[mattless1]

Hi deksel,

i have tried it it in the ThisWorkbook Module, but didn't run after the first Svme script had run.

is it possible to move it into the main script? or does it have to run on it's own.

Many thanks
Mattless1

 

[DEksel]

This is auto macro. Automatic event. If you are saving Workbook from another procedure, I am not sure that this will work. But, if you are doing this

nbk.SaveAs Filename:=File nbk

Close savechanges:=False

then why you just don't write that right there? I mean

Dim s As Range Set s = Sheets(1).Range("J4") s = s + 1 nbk.SaveAs Filename:=File nbk.Close savechanges:=False

You are closing file anyway. ____ Why are lines always breaked in my posts? BBCodes works wrong?