Change value before closing workbook

mattless1

Board Regular
Joined
Apr 1, 2011
Messages
89
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.

Code:
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.

Code:
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
 
Last edited:

DEksel

New Member
Joined
Oct 5, 2019
Messages
25
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

Board Regular
Joined
Apr 1, 2011
Messages
89
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

New Member
Joined
Oct 5, 2019
Messages
25
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
Code:
nbk.SaveAs Filename:=File nbk
Code:
Close savechanges:=False
then why you just don't write that right there? I mean
Code:
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?
 

Forum statistics

Threads
1,084,819
Messages
5,380,084
Members
401,645
Latest member
Me22

Some videos you may like

This Week's Hot Topics

Top