Insert column and drop one

[austenr]

I have a report that runs monthly. What I need to do is insert a new column each month and remove the last column. So say that you have a report where the data is in columns A-G. I want to drop the last column (column G), cut and paste the data that is left (columns A-F) over one column, and inser a new column in front of column A (not sure if I need to do the insert before the cut and paste or not). The cut and paste needs to have the formats carried with it also. Attached is a sample work book that illustrates the data I currently use. Thanks

[CBrine]

austner,
You should be able to just do this, based on your attached workbook.


Columns("E:E").EntireColumn.Insert
Columns("F:F").Copy
Columns("E:E").PasteSpecial xlPasteFormats
Columns("E:E").PasteSpecial xlPasteColumnWidths
Columns("Q:Q").EntireColumn.Delete



HTH
Cal

[austenr]

Thanks for the reply. Another question; how could I format the new column like the old ones? How can you copy the formats of the active cells in the column next to the new ones and paste them into the new column without doing it one by one, or is ther a macro that can run to do that? Also, if you look at the sample workbook, is ther a way to fill the inserted column month name and year automatically, in other words, in the example, Sept. 2006 would be the next entry for the month. Thanks

[CBrine]

Austner,
I edited my code above to copy formats and columns widths, missed that on the first read.

The second piece might be possible, but would require the existing ones to be specifically formated. Let me dig in a little and I can tell you what abbreviations are acceptable. You would most likely need to format the column headers as dates, and then format them to show you what you are looking for.

Cal

[CBrine]

Here's a revision for the second bit.

You will need to do this before using.
Change your column heading currently in Column E, and change it to a date by entering 08/01/2006, then custom format the date to mmm yyyy to display it simliar to your current heading.

Now run the code. It add and delete the columns and increment the month, with processing to deal with the year change if the current month +1 = 13.

HTH
Cal

Private Sub CommandButton1_Click()
Columns("E:E").EntireColumn.Insert
Columns("F:F").Copy
Columns("E:E").PasteSpecial xlPasteFormats
Columns("E:E").PasteSpecial xlPasteColumnWidths
Columns("Q:Q").EntireColumn.Delete
Range("E6") = DateValue(IIf(Month(Range("F6")) + 1 = 13, 1, Month(Range("F6")) + 1) & "/" & Day(Range("F6")) & "/" & IIf(Month(Range("F6")) + 1 = 13, Year(Range("F6")) + 1, Year(Range("F6"))))
End Sub

[CBrine]

One other thing I forgot to mention, you will need to do a text to columns on your column heading, after you enter the new date value, to convert it to a correct date field, then do the right click format with the custom format.

Cal

[austenr]

Thanks i will work on that

[austenr]

Nice job. That is exactly what i needed. Now if I can only figure out how to move the formats from column to column :friends:

[CBrine]

This code should already be doing that?
Columns("F:F").Copy
Columns("E:E").PasteSpecial xlPasteFormats
Columns("E:E").PasteSpecial xlPasteColumnWidths

Is it not working for you?

[austenr]

OOPS!!! Yes it does. My mistake. Thanks a bunch