Selecting columns to copy and paste

[Sue]

Help! This code is meant to insert 2 columns to the right, copy the last two
used columns and place their formulae in the new columns. It has been
recorded with actions which do just that, but this macro does not work. It
works to the * but at that point selects all the columns in the work sheet
not just the last 2. This is a disaster and I can not seem to stop it doing
that. I suspect the line two below would have the same issue.

Range("a2").Select 'data headings in this row
Selection.End(xlToRight).Select 'gets to end column via data headings
ActiveCell.Offset(0, 1).Range("a1:b1").Select 'selection of 2 columns at
end
Selection.EntireColumn.Insert 'puts new columns in
* ActiveCell.Offset(0, -2).Columns("A:B").EntireColumn.Select 'stuffs up
here!
Selection.Copy
ActiveCell.Offset(0, 2).Columns("A:B").EntireColumn.Select
Selection.PasteSpecial Paste:=xlFormulas
Application.CutCopyMode = False
Calculate

 

[Gleam]

Works OK for me in 2003. Have you tried shutting down Excel and restarting
it? That sometime works for me when Excel macros are misbehaving.

 

[Dave Peterson]

I like to start at the far right and come back looking for the last used column.

If that's ok with you, you may want to try this:

Option Explicit
Sub testme()
Dim LastCol As Long
With ActiveSheet
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column

.Cells(1, LastCol - 1).Resize(1, 2).EntireColumn.Copy _
Destination:=.Cells(1, LastCol + 1)
End With
End Sub

I actually copy|pasted everything. If you wanted just the formulas, change it
to:

Option Explicit
Sub testme2()
Dim LastCol As Long
With ActiveSheet
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column

.Cells(1, LastCol - 1).Resize(1, 2).EntireColumn.Copy
.Cells(1, LastCol + 1).PasteSpecial Paste:=xlPasteFormulas
End With
End Sub