Excel macro to get the right type of X-scale in a chart

[mexaria]

Hi Everybody,

After two hours trying to find this over internet, I finally gave up and I'd like to asf for your help!

Say that in one sheet I've a table with say 500 columns and 6000+ lines.
In other two sheets of the same workbook, I've made 50+ charts to get the information I want from the table. I just filter the data in the table in the first sheet, and the charts are automatically updated as I need.

I fairly new to all this VBA stuff.. but I made a code to filter data that I have in a table, and to save my two sheets as a PDF file. I'll have several files (as much as I filter different data). This code works great for this.

Here's the code:

Sub saveaspdf()


Dim n As Integer
Dim fName As String
For n = 1 To 12
Sheets("Table Database").Select
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=4, Criteria1:=n
' filter the data I need


Sheets("Charts1").Select
With ActiveSheet
fName = .Range("A1").Value & .Range("E1").Value
.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\Users\k.cuchinskicampos\Desktop\Dashboards\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
'save pdf for first sheet

Sheets("Charts2").Select
With ActiveSheet
fName = .Range("A1").Value & .Range("E1").Value & .Range("I1").Value
.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
"C:\Users\k.cuchinskicampos\Desktop\Dashboards\" & fName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=False
End With
'save pdf for second sheet


Next n
End Sub


The problem is that the resulting pdf file gets the x-scale type of each chart wrong. It displays the data in numbers format, and not as dates as I want. In the excel file, the scale type is set to "dates" and the original data is formatted as "dates" as well.

Do you have any ideas about what is going wrong?

Thanks a lot!!

 

[Worf]

Hello and welcome to the Board
I set up an example and it worked for me. Could you upload a sample workbook that does not work to a hosting site? Only one chart that does not transfer correctly is enough, and no real data is needed.
A possible workaround could be to convert or export the charts as pictures.

 

[mexaria]

Hello and welcome to the Board
I set up an example and it worked for me. Could you upload a sample workbook that does not work to a hosting site? Only one chart that does not transfer correctly is enough, and no real data is needed.
A possible workaround could be to convert or export the charts as pictures.

Thanks for the welcome message and the quick answer (valeu ai!!)

Speaking with a friend, I've found a solution for my problem. Apparently, there was some problem in the format of the original data in the sheet "database". I've tried to set that format to "dates", but for some reason, when printing the charts to pdf, excel did not consider those values as "dates". I just unchecked the "Linked to source" box when you edit the format of the x-scale data of each chart. Doing that, I got the right x-scale type of data and my problem was solved.


Thanks anyway! Muito obrigado!

 

[Worf]

You are welcome and thanks for providing feedback! Até a próxima vez!