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:

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

<tbody>
</tbody>

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)