Autofill dynamic 1 cell to the right

**max3732** · 08-04-2011, 12:11 PM

I have a list with column heading of Jan '11, Feb'11, Mar'11, etc.

I can't change the format of the month headings and every month I'm putting in a new column and copying some of the data to the right.

The issue I'm having is just putting the new month in. Here is what I wrote that gives me an error with the autofill

Please Login or Register  to view this content.

I also tried it like this:

Please Login or Register  to view this content.

It works up to the point of the autofill, where it goes to the previous months, selects them, then actives the current month. All I need it to do is act like clicking on the bottom right of the cell and dragging it over.

Any help would be appreciated

**Mordred** · 08-04-2011, 12:19 PM

Hi max3732, you have forgotten to wrap your code in code tags as per Rule #3 in the forum rules (located in my signature). Once you wrap your code, someone will be able to help you.

**Mordred** · 08-04-2011, 02:37 PM

Hi max3732, try the following code. It assumes that your headers are along row number 2, if not, change it accordingly. If you have any issues, don't hesitate to state them.

Please Login or Register  to view this content.

**max3732** · 08-04-2011, 05:07 PM

I'm kind of a beginner so could you explain what your doing there?

Also, is there any way to modify my code to make it work?

**Mordred** · 08-04-2011, 05:15 PM

Hi max3732, your code was actually modified to make it work but I'll break it down for you.
1) I removed .Select and .Activate, which slows code and also gives your worksheet the jitters when it is run.

2)

Please Login or Register  to view this content.

-Finds the last column in row 2 and sets aCell as the Range with the last column used in row 2.

3)

Please Login or Register  to view this content.

-Sets the next empty cell that will receive the AutoFill

4)

Please Login or Register  to view this content.

- AutoFill
Let me know if you require more.

**max3732** · 08-04-2011, 06:55 PM

The issues with the code you've provided are::

1. The column heading is not in the last row. Its in the 9th column from the end. Every month a new column is added with the name of the month and the year in the format "July'11".

2. If I highlight the previous cell and drag it over it puts "July'12", so I need to highlight the previous 11 cells in order to get it to copy over correctly or find another way to get the new month and year in

**Mordred** · 08-04-2011, 07:25 PM

Originally Posted by max3732

The column heading is not in the last row. Its in the 9th column from the end.

Not sure what you mean anymore, especially by "column heading is in last row" - to me this implies that the header is below the data instead of on top of the data.

Originally Posted by max3732

the format "July'11"

- I wonder if it is time to turn that format into a date format that Excel can read.

Mayhap it would be best if you upload a mock workbook with mock data in order to see what it is you are referring to.

**max3732** · 08-04-2011, 07:28 PM

1. I meant its not in the last column. Its in the 9th column from the last one

**max3732** · 08-05-2011, 04:41 PM

If I change the date formatting how would it work? I'm also curious why my method doesn't work

**Mordred** · 08-05-2011, 04:47 PM

If the cells are formatted as dates, they will increment by dates. If the are not formatted as dates, the numbers inside the cell will increment. That is why '11 would change to '12, '13, and so on.