Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting columns to copy and paste
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting columns to copy and paste
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. "Sue" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting columns to copy and paste
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 Sue wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
De selecting a range after a copy and paste is performed | Excel Programming | |||
Copy paste without selecting sheet or range | Excel Programming | |||
Selecting data to copy and paste | Excel Programming | |||
Auto copy/ paste on relevant work sheet when selecting list item | Excel Programming | |||
Copy and paste columns | Excel Programming |