Dynamic Macro with some content in an Excel cell

[mad4max]

Hi people,

I need your help to improve my Excel sheet.

I use some small macros to save the file with a specific name in a specific folder, determined by variables.

In colum S you’ll find the Directory where macro should save the Excel file and the PDF file.

Also, you’ll find the file name which is composed of the content of several cells.

The Macro SaveOFFER() look into the sheet OFFRE, in column S where are info.

The problem is that I would like to move the content of the column R and S from the sheet OFFRE into the sheet PARAM so I’m more flexible if I add lines or columns.

I can’t find the way to say in the macro that it has to look into another sheet to print or save the file…

Here is the macro:

Sub SaveOFFER()

'-----------------------------------------------------------------------------------------------------------+
' Sauvegarde l'offre dans le répertoire facturation avec le bon nom de fichier
'-----------------------------------------------------------------------------------------------------------+

Range("C3").Select
ChDir Range("S6")
ActiveWorkbook.SaveAs Filename:=Range("S6") & Range("S7")


Explanation of what I need:

Range("C3").Select
Here it should go to Param!C3 instead of just C3

ChDir Range("S6")
Here it should go to Offre!S6 instead of just S6

ActiveWorkbook.SaveAs Filename:=Range("S6") & Range("S7")
Here it should stay in the sheet OFFRE, but use the value stored in Param!S6 and Param!S7 instead of just S6 and S7.

I hope that it’s clear. If not, ask me and I can send a copy of the Excel file I use...

Any better solution, is welcome.

Another solution could be that if I move anywhere the content of column R + S, the macro would also change it’s value. I don’t know if it’s possible in VBA to have dynamic references.

In Excel, when you move a the content of any cell that is in a formula, the formula is automatically adapted to the new location. What about VBA?

Thank you very much for your valuable help!

Best regards!

 

[Z51]

Perhaps...

Sub madmax()
' Not sure why you need to activate/select Param!C3 here...
With Sheets("Param")
    .Activate
    .Range("C3").Select
End With

With Sheets("Offre")
    ChDir (.Range("S6").Value)
    ActiveWorkbook.SaveAs Filename:=.Range("S6").Value & .Range("S7").Value
End With

End Sub

 

[mad4max]

Perhaps...

Sub madmax()
' Not sure why you need to activate/select Param!C3 here...
With Sheets("Param")
    .Activate
    .Range("C3").Select
End With

With Sheets("Offre")
    ChDir (.Range("S6").Value)
    ActiveWorkbook.SaveAs Filename:=.Range("S6").Value & .Range("S7").Value
End With

End Sub

Hello,

There was a little mistake, maybe because I didn't myself explained well, due to my poor English...

I need to activate/select Offer!C3 as I want to file to always open on C3 in sheet Offer, which contains the Offer number. So, creating a new offer, can't forget to change the offer number to avoid duplicates.

I used your suggestion, changing some things and it work perfectly:

Sub SaveOFFER()
'-----------------------------------------------------------------------------------------------------------+
' Sauvegarde l'offre dans le répertoire facturation avec le bon nom de fichier
'-----------------------------------------------------------------------------------------------------------+
'
' Select the cell that where the file should open every time.
'
With Sheets("Offre")
.Activate
.Range("C3").Select
End With

'
' Select the sheet where the info like directory and filename is stored.
' and use the content of the specific cell (E10 + E11) as variable
'
With Sheets("Param")
ChDir (.Range("E10").Value)
ActiveWorkbook.SaveAs Filename:=.Range("E10").Value & .Range("E11").Value
End With

End Sub

The file is saved in right place, correctly...

BUT, I have a problem trying to save the file as .PDF file!

I tried to adapt your solution to my VBA code to create the .PDF file, but I get an error: "Compile Error. Sub or Function not defined"...

Here is the macro:

Sub SaveToPDF()
'
' SaveToPDF Macro
'
'
' Select the cell that where the file should open every time.
'
With Sheets("Offre")
.Activate
.Range("C3").Select
End With

'
' Select the sheet where the info like directory and filename is stored.
' and use the content of the specific cell (E12 + E13) in the sheet PARAM as variable
‘
With Sheets("Param")
ChDir (.Range("E12").Value)
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF,Range("E12").Value & .Range("E13").Value _
, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=False

End With

I don't understand where is the mistake...
Also, is there maybe another way to save the file in a .PDF file as the one I used?

Thank you for your help!

Best regards!

 

[Z51]

In the 'ActiveSheet.ExportAsFixedFormat' line, you haven't specified the "Filename:=" parameter like you did for "Type:=", "Quality:=", etc. You also left off the period before Range("E12").

Try:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=.Range("E12").Value & .Range("E13").Value & ".pdf" _

If your file name in E13 already has the ".pdf" at the end, then you can remove the & ".pdf" bit (leave the underscore at the end of the line, though.