Changing file destination and filename based on specified in cell in VBA code

[dedark05]

Hi everyone,

As new to VBA, how can I modify this so instead of objChart.Export ("C:\Users\dean.martin\Desktop\My Exported Charts\Yesterday's Weather.jpg"), it would use the folder location specified in cell "N10" of "sheet 1" for example? (ie., where N10 = C:\Users\dean.martin\test\ or any other destination folder) and in cell "N9" of "sheet 1" for the file name (ie., where N9 = Today's weather.jpg or any other file name or format)

Below is my code;

---------------------------------------------------------------------------
Private Sub CommandButton1_Click()
Dim i As Integer
Dim intCount As Integer
Dim objPic As Shape
Dim objChart As Chart
'copy the range as an image
Call Sheet1.Range("A1:J57").CopyPicture(xlScreen, xlPicture)

'remove all previous shapes in sheet2
intCount = Sheet2.Shapes.Count
For i = 1 To intCount
Sheet2.Shapes.Item(1).Delete
Next i
'create an empty chart in sheet2
Sheet2.Shapes.AddChart
'activate sheet2
Sheet2.Activate
'select the shape in sheet2
Sheet2.Shapes.Item(1).Select
Set objChart = ActiveChart
'paste the range into the chart

Sheet2.Shapes.Item(1).Line.Visible = msoFalse
Sheet2.Shapes.Item(1).Width = Range("A1:J57").Width
Sheet2.Shapes.Item(1).Height = Range("A1:J57").Height
objChart.Paste
'save the chart as a JPEG
objChart.Export ("C:\Users\dean.martin\Desktop\My Exported Charts\Yesterday's Weather.jpg")
End Sub
-----------------------------------------------------------------------
Any help would be greatly appreciated.

Thanks,
Dean