Kas2401
New member
Hello
I have a series of worksheets that represent monthly client statements. I ultimately want to be able to send the statements individually or in mass at month end to clients via MSExpress. Having never delved into Macros I am a complete novice and therefore need to be treated with kid gloves.
The worksheets are identical in layout and only differ with user data. I have created cells per worksheet that refer to a 'Master' worksheet to mange statement dating, period, year etc. Also I have a unique 'account number' per worksheet. I want to be able to save the individual worksheets as .pdf documents, creating the path and file name from the existing path and atributes within the file. This I have done as follows.
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&K7&"_"&K4&J4&".pdf"
which results in a string as follows
C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf
All goes well when recording the macro below and it appears to work well, that is until I run it on any of the worksheets that it was not recorded in.
My problem is that the .pdf file name is not updating to the new worksheet data and retains the original file name. I have looked at some comments on the www, however, only being a geek wannabee I have yet to acquire the skill-set to understand whether my problem is unique.
I don't think it has anything to do with the .pdf creator, more like a refreshing or waiting period for excel to catch up with itself. As you can see from the macro I do go to the lengths of copying the result of the formula string as a 'value' into a secondary cell in an attempt to force the calculation to reveal the updated file name, but with no luck. I am confident that there is a very simple solution, perhaps this is why it evades me.
Any assistance will be welcome.
I have a series of worksheets that represent monthly client statements. I ultimately want to be able to send the statements individually or in mass at month end to clients via MSExpress. Having never delved into Macros I am a complete novice and therefore need to be treated with kid gloves.
The worksheets are identical in layout and only differ with user data. I have created cells per worksheet that refer to a 'Master' worksheet to mange statement dating, period, year etc. Also I have a unique 'account number' per worksheet. I want to be able to save the individual worksheets as .pdf documents, creating the path and file name from the existing path and atributes within the file. This I have done as follows.
=LEFT(CELL("filename",A1),FIND("[",CELL("filename",A1),1)-1)&K7&"_"&K4&J4&".pdf"
which results in a string as follows
C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf
All goes well when recording the macro below and it appears to work well, that is until I run it on any of the worksheets that it was not recorded in.
My problem is that the .pdf file name is not updating to the new worksheet data and retains the original file name. I have looked at some comments on the www, however, only being a geek wannabee I have yet to acquire the skill-set to understand whether my problem is unique.
I don't think it has anything to do with the .pdf creator, more like a refreshing or waiting period for excel to catch up with itself. As you can see from the macro I do go to the lengths of copying the result of the formula string as a 'value' into a secondary cell in an attempt to force the calculation to reveal the updated file name, but with no luck. I am confident that there is a very simple solution, perhaps this is why it evades me.
Any assistance will be welcome.
Code:
Sub Save_pdf()
'
' Save_pdf Macro
'
'
Range("Q1").Select
Selection.Copy
Range("R1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("R1").Select
Application.CutCopyMode = False
Range("A23").Select
Application.CutCopyMode = False
Range("R2").Select
ActiveCell.FormulaR1C1 = _
"C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf"
Range("A23").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Documents and Settings\Kenneth Smart\My Documents\Business Ventures\ABC\ABC077_20124.pdf" _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False
End Sub