AutoFill from previous column

[satkin2]

Hi,
I have a spreadsheet that adds a new column each month and I want to set up a macro to take the last column and AutoFill one column to the right when run.

Example:
This month I've got column AS populated with heading 01/09/11, I want to AutoFill AT with the heading 01/10/11, next month I want to AutoFill AU with the heading 01/11/11 from AT etc;

I've got my code adding the new column as needed, but I want not got the heading autofilling accross

Sub Macro10()
  Sheets("format").Select
  Range("A1").Select
  Selection.End(xlToRight).Offset(0, 1).EntireColumn.Select
  Selection.Insert Shift:=xlToLeft#
End Sub

NB: These columns aren't the last ones in the spreadsheet.

 

[Silvrhelm]

Try This,

Selecting your original cell ie 01/09/11 and run this macro

    Dim i As Integer
    For i = 1 To 100
    ActiveCell.Offset(0, i) = ActiveCell.Value + i
    Next i

You can change 100 to however many entries you want, if you wanted I could rewrite it to include a question box that would ask how many entries you wanted.

Just add this into your code after you insert the new column, if after your macro the sheet is selecting the newly inserted column you could just add in before Activecell.offset(0,-1).select before the "For i = 1 to 100" line

Hope this helps

 

[satkin2]

Thats great, thank you. A couple of problems though.

1. My mistake, those dates were in British format dd/mm/yy. I want it moving a whole month on, this does a day. Not sure how I would do a month.

2. I've noticed when the insertion of the new column happens, the colour formatting is copied, but not the borders, how do I get it to do this, or should I then just auto fill the whole column somehow which would bring along the borders too?

Thanks

 

[Silvrhelm]

Sub macro10()
    Dim i As Integer
    Sheets("format").Select
    Range("A1").Select
    Selection.End(xlToRight).Offset(0, 1).EntireColumn.Select
    Selection.Insert Shift:=xlToLeft
    ActiveCell.Offset(0, -1).Select
    For i = 1 To 20
    [COLOR=Red]ActiveCell.Offset(0, i) = DateAdd("m", i, ActiveCell.Value)[/COLOR]
    Next i
End Sub

Ok that will add a month instead of a date, the line thats red is the line ive changed.

Ive tried to put it all together in one macro, but now im not sure exactly what you needed, did you want it to add a column, date that and then add another one and date that etc..?

The code for copying formatting is fairly straight forward, im going to try write a code for what ive described and add in the format copying.

Ill write back soon

 

[satkin2]

Thanks,

To clarify, what I'm trying to do is add a column to my monthly columns, copy over the formatting and autofill the rows where there are headings (headings are on rows 1, 33, 66, 98)

 

[Norie]

Could you please clarify some other things?

Do you just want to put the next month's date in the next empty column?

What formatting do you want copied and where do you want it copied too?

Set LastCol= Worksheets("format").Cells(1, Columns.Count).End(xlToLeft)
 
LastCol.Copy
 
With LastCol.Offset(,1)
      .PasteSpecial xlPasteFormats
      .Value = DateAdd("m",1, .Value)
      .Copy .Offset(32)
      .Copy .Offset(65)
      .Copy .Offset(97)
End With

 

[satkin2]

Norie

I've tried attaching a file but it wouldn't let me, so please find my attempt at making it clearer.

Headings rows: 1, 33, 66, 98
Col A - Blank
Col B - AS - Month (Eg: Jan-11, Feb-11 etc
Col AT - Blank
Col AU - Movement
Col AV - Blank
Col AW - BB - Movements

Data Rows: 2-30, 34-63 etc;
These are formatted (ie: number format, percentage etc

The headings rows are coloured and have borders too.


What I'm trying to do is...

• Insert one column to the months, shoving everything after the months columns to the right by one column.
• Autofill from the last months column to the newly created column, this will therefore copy all of teh formatting and increment my monthly heading by a month.

I hope this helps.

Thanks

 

[Norie]

In your very first post you said you wanted to put autofill the next column.

So is that column BC?

Or are the columns with month headings actually somewhere else?

Also, what do you actually want to 'autofill'?

Do you have formulas/values you want to copy?

Or do you actually mean you just want to copy the format from the previous column and put the new date in rows 1, 33 etc.?

 

[satkin2]

Sorry I can't attach, this would be easy to understand if I could. I'm clearly not being very good with my descriptions.


No, not BC, the next month column, so inserting a column at AT.

My month heading fields are dates, so by autofilling I am thinking that it will increment the month by 1 like it does when I drag the autofill manually.


The rest of the column contains various formats for different rows, colours, borders, number or percent format for example, so I want to copy the whole AS columns format into the newly inserted AT format.

 

[Norie]

You don't need to use autofill here.

This code should find the last column starting from column 1, it then copies the format of the entire column to the next column to the right.

Then it puts the date for the 1st of the next month into the column to the right in the columns you mentioned.

Dim LastCol As Range
    Set LastCol = Worksheets("format").Range("A1").End(xlToRight)
    LastCol.EntireColumn.Copy
    With LastCol.Offset(, 1)
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
        .Value = DateAdd("m", 1, .Offset(, -1).Value)
        .Copy .Offset(32)
        .Copy .Offset(65)
        .Copy .Offset(97)
    End With