Creating a dynamic table

**kmoore** · 07-18-2011, 03:18 PM

I am trying to create a macro/develop code that will read two dates (in the format of Month and year) and create a table that has a column for each month.

For example, Start Date: Nov 2011, End Date: Feb 2012 will create 4 columns with the date in each of the column heading.

**Colin Legg** · 07-18-2011, 03:31 PM

Welcome to the forum.

To get you started, here's an example where the start/end dates are provided in the VBA code. Reading the start/nd dates from a worksheet (or a userform or wherever else) is not an issue. This code puts the date series across row 2, starting in column B. You can format the cells accordingly should you wish to hide the day part of the dates.

    Dim dteStart As Date
    Dim dteEnd As Date
    
    dteStart = DateSerial(2011, 11, 1)
    dteEnd = DateSerial(2012, 2, 1)
    
    With Range("B2")
        .Value = dteStart
        .DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlMonth, Step:=1, Stop:=dteEnd
    End With

**kmoore** · 07-18-2011, 03:54 PM

Thanks Colin, that helps a lot, but how do I do it so it gets the input from the spreadsheet. I would like the number of columns to vary based on user input. One time it may be 4 columns and another time 20. I really appreciate the help.

**Colin Legg** · 07-18-2011, 04:05 PM

Hi,

Let's suppose your spreadsheet has the start date in cell A1, the end date in cell A2 and you want to fill the series from C3 across the row.

       
    With Range("C3")
        .Value = Range("A1").Value
        .DataSeries Rowcol:=xlRows, Type:=xlChronological, Date:=xlMonth, Step:=1, Stop:=Range("A2").Value
    End With

I can't be more specific than that, given the current information on the thread. Adjust the ranges as necessary to suit your needs.