How do I drag excel Table data to right dynamically using vba code

[vrsharma]

Hello Expertise,

I want to write VBA code for dragging excel Table range data dynamically to Right side column wise as per given date.
For eg. If I give Date for "Sep-19" in Worksheet it should automatically drag the table range to right containing Sept-19 data. I am Excel 2016 version.
Below is the table data:

[TABLE="class: grid, width: 898"]
<tbody>[TR]
[TD]Month[/TD]
[TD]18-Oct[/TD]
[TD]Nov-18[/TD]
[TD]Dec-18[/TD]
[TD]19-Jan[/TD]
[TD]Feb-19[/TD]
[TD]Mar-19[/TD]
[TD]April-19[/TD]
[TD]May-19[/TD]
[TD]June-19[/TD]
[TD]July-19[/TD]
[TD]Aug-19[/TD]
[/TR]
[TR]
[TD]Cumulative Count[/TD]
[TD]10700[/TD]
[TD]10810[/TD]
[TD]10930[/TD]
[TD]11050[/TD]
[TD]11163[/TD]
[TD]11210[/TD]
[TD]11280[/TD]
[TD]11350[/TD]
[TD]11440[/TD]
[TD]11550[/TD]
[TD]19929[/TD]
[/TR]
[TR]
[TD]Devices[/TD]
[TD]570[/TD]
[TD]575[/TD]
[TD]582[/TD]
[TD]592[/TD]
[TD]5973[/TD]
[TD]599[/TD]
[TD]604[/TD]
[TD]607[/TD]
[TD]612[/TD]
[TD]617[/TD]
[TD]617[/TD]
[/TR]
[TR]
[TD]Connectors[/TD]
[TD]291[/TD]
[TD]295[/TD]
[TD]298[/TD]
[TD]299[/TD]
[TD]3027[/TD]
[TD]305[/TD]
[TD]306[/TD]
[TD]308[/TD]
[TD]310[/TD]
[TD]314[/TD]
[TD]314[/TD]
[/TR]
[TR]
[TD]Terminals[/TD]
[TD]209[/TD]
[TD]211[/TD]
[TD]213[/TD]
[TD]214[/TD]
[TD]2163[/TD]
[TD]217[/TD]
[TD]218[/TD]
[TD]220[/TD]
[TD]222[/TD]
[TD]224[/TD]
[TD]224[/TD]
[/TR]
[TR]
[TD]Rest[/TD]
[TD]9630[/TD]
[TD]9729[/TD]
[TD]9837[/TD]
[TD]10268[/TD]
[TD]100467[/TD]
[TD]10089[/TD]
[TD]10152[/TD]
[TD]10225[/TD]
[TD]10296[/TD]
[TD]10395[/TD]
[TD]18774[/TD]
[/TR]
</tbody>[/TABLE]

I have tried some code, below the vba code for it:

Sub Macro4()

Range("Table1[[#Headers],[Month]]").Select
Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillRight
Range("Table1[[#All],[Aug-19]:[Sep-19]]").Select

End Sub

I want the range "Aug-19]:[Sep-19]]" in last of code to be set up dynamic as per given month, how can I do it?

Thank you in advance.

 

[sijpie]

Hi vrsharma,

i don't fully understand what you mean with 'drag to right'.
Also you mention: "If I give date for "Sep-19" in Worksheet". Where / how do you enter this date?

Let us assume that you fill in the date "sep-19" in a cell A1.
then what needs to happen?

1. a column sep-19 needs to be added to the right side of the table. Or:
2. the date sep-19 needs to be found in the header and the table scrolled to this column. Or:
3. something else

Let me, and the other readers, know what you mean here.

Please explain clearly. (I don't think you mean 'drag' because you drag with a mouse, not with code. I assume you mean scroll, but please clarify)