This first bit finds the last date cell.
Option Explicit
Function FindLastDateCell() As String
Dim c As Integer, lttr As String
Range("B2").Select
Do Until IsDate(ActiveCell) = False
ActiveCell.Offset(0, 1).Select
c = c + 1
Loop
c = c + 1
lttr = Split(Columns(c).Address(1, 0), ":", -1, 1)(0)
FindLastDateCell = lttr
End Function
This bit calls the first one and then substitutes "cell" with "lttr" which is the last date cell.
Sub PORTMPDE()
Application.ScreenUpdating = False
Dim i As Long, indx As Long, a As Long
Dim rng, rfrnce
Dim lttr As String
Worksheets("PORT MPDE").Activate
lttr = FindLastDateCell
Worksheets("Trending Charts").Activate
rng = "A8:cell8;A9:cell9;A11:cell11;A12:cell12;A13:cell13;A14:cell14;A15:cell15;A17:cell17;A23:cell23;A28:cell28;A29:cell29;A30:cell30;A31:cell31;A32:cell32;A33:cell33;A34:cell34;A35:cell35;A36:cell36;A37:cell37;A39:cell39;A40:cell40;A41:cell41;A44:cell44;A45:cell45;A46:cell46;A47:cell47;A48:cell48;A49:cell49;A57:cell57;A58:cell58;A59:cell59"
rng = Replace(rng, "cell", lttr, 1, -1, 1)
rng = Split(rng, ";", -1, 1)
indx = UBound(rng)
rfrnce = "='PORT MPDE'!$B$2:$cell$2"
rfrnce = Replace(rfrnce, "cell", lttr, 1, -1, 1)
With ActiveSheet
For i = 0 To indx
With .ChartObjects("Chart " & i + 1).Chart
.SetSourceData Source:=ThisWorkbook.Sheets("PORT MPDE").Range(rng(i))
.SeriesCollection(1).XValues = rfrnce
End With
Next
End With
End Sub
Bookmarks