mraemorris
New Member
- Joined
- Mar 7, 2014
- Messages
- 2
Hi all,
This is my first post, so please let me know if you need more info! I'm using Excel 2010 and I wrote the code below to autofill a range that feeds a chart on the worksheet "Dashboard." Essentially, the user selects criteria on the Dashboard and clicks the "Submit" button, which causes an advanced filter to copy the data that matches the criteria into the range Sheets("HiddenSheet").Range("A2:H"). I need the code below to autofill the formulas in I3:Q3 in I:Q until the last row in A:H, but I keep getting a "Type Mismatch" error on the bolded line below. I've searched everywhere to remedy this, and I've tried several different iterations of both the range and lastrow - any suggestions would be greatly appreciated!
One note - Columns K:Q contain formulas that feed off of Column C and into Column J - that's why the autofill range is different than the chart SetSourceData range.
Sub TimelineControl()
Dim Timeline As Chart
Dim ws As Worksheet
Dim rngforTimeline As Range
Dim LastAxis As Integer
Dim LastA As Long
Set ws = Sheets("HiddenSheet")
LastA = ws.Range("A" & Rows.Count).End(xlUp).Row
'LastA = ws.Range("A1").End(xlDown).Row
'LastA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
LastAxis = ws.Cells(Rows.Count, 9).End(xlUp).Row
Set rngforTimeline = ws.Range("I3:Q" & LastA)
ws.Range("I4:Q" & (ws.Range("Q" & Rows.Count).End(xlUp).Row) + 1).Clear
ws.Range("I3:Q3").AutoFill Destination = rngforTimeline
Set Timeline = Sheets("Dashboard").ChartObjects(1).Chart
Timeline.SetSourceData (Sheets("HiddenSheet").Range("I3:J" & LastAxis))
End Sub
This is my first post, so please let me know if you need more info! I'm using Excel 2010 and I wrote the code below to autofill a range that feeds a chart on the worksheet "Dashboard." Essentially, the user selects criteria on the Dashboard and clicks the "Submit" button, which causes an advanced filter to copy the data that matches the criteria into the range Sheets("HiddenSheet").Range("A2:H"). I need the code below to autofill the formulas in I3:Q3 in I:Q until the last row in A:H, but I keep getting a "Type Mismatch" error on the bolded line below. I've searched everywhere to remedy this, and I've tried several different iterations of both the range and lastrow - any suggestions would be greatly appreciated!
One note - Columns K:Q contain formulas that feed off of Column C and into Column J - that's why the autofill range is different than the chart SetSourceData range.
Sub TimelineControl()
Dim Timeline As Chart
Dim ws As Worksheet
Dim rngforTimeline As Range
Dim LastAxis As Integer
Dim LastA As Long
Set ws = Sheets("HiddenSheet")
LastA = ws.Range("A" & Rows.Count).End(xlUp).Row
'LastA = ws.Range("A1").End(xlDown).Row
'LastA = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
LastAxis = ws.Cells(Rows.Count, 9).End(xlUp).Row
Set rngforTimeline = ws.Range("I3:Q" & LastA)
ws.Range("I4:Q" & (ws.Range("Q" & Rows.Count).End(xlUp).Row) + 1).Clear
ws.Range("I3:Q3").AutoFill Destination = rngforTimeline
Set Timeline = Sheets("Dashboard").ChartObjects(1).Chart
Timeline.SetSourceData (Sheets("HiddenSheet").Range("I3:J" & LastAxis))
End Sub
