How can you shift selected cell using Excel VBA?

Question

I am trying to automate a rolling calendar spreadsheet that tracks various metrics and charts them into a spark line. The script I would like to write would shift the selected range in the spark-lines every time it is ran.

I have done some googlefu and have tried using the offset function to no avail. This is because the data is in a predefined range defaulting to num 0 based on the formulas used to populate the spreadsheet int the first place.

excel vba : selected cells loop

https://www.excel-easy.com/vba/examples/loop-through-entire-column.html

https://support.microsoft.com/en-us/help/291308/how-to-select-cells-ranges-by-using-visual-basic-procedures-in-excel

I am stuck at incrementing the ActiveCell.SparklineGroups.Item(1).Item(1).SourceData from its current selected range to PPTracking!G8:R8 ... H8:S8 ... and so on each time the macro is ran.

This is my first time working in VBA and any help is greatly appreciated!

Sub Macro4() Dim selectedRange As Range Set selectedRange = PPTracking!F8:Q8 Range("E5:E6").Select Application.CutCopyMode = False ActiveCell.SparklineGroups.Item(1).Item(1).SourceData = "PPTracking!F8:Q8" Range("E5:E6").Select End Sub

Welcome to Stack Overflow! Please take a moment to check out the tour (you'll earn your first badge!) as well as "How to Ask" and this checklist from the site's top user. You're question isn't clear as to where you're "stuck". — ashleedawg
– ashleedawg, Commented Aug 20, 2018 at 17:58

BigBen · Accepted Answer · 2018-08-20 22:47:48Z

You can either use Sparkline.ModifySourceData or directly change the Sparkline.SourceData property, as it looks like you are currently aiming to do.

This code will shift the SourceData 1 column to the right - from F8:Q8 to G8:R8, then to H8:S8, etc. - by using the original SourceData value as a reference within Range, which is then Offset by 1 column.

It concatenates the Parent.Name to the Address to get the full Worksheet Name and cell reference.

Sub ShiftSparklineData() If ActiveCell.SparklineGroups.Count > 0 Then With ActiveCell.SparklineGroups.Item(1) .SourceData = "'" & Range(.SourceData).Parent.Name & "'!" & Range(.SourceData).Offset(, 1).Address End With End If End Sub

Avoid using ActiveCell where possible though; reference the cell(s) with a sparkline using Sheets("Yoursheetname").Range("Cellreference")

Collectives™ on Stack Overflow

How can you shift selected cell using Excel VBA?

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related