MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro to retrieve and insert columns


Posted by Stanley on November 15, 2001 6:42 AM

Worksheet named "Financials" is protected. Need a macro which will:
1) Insert two new columns at whatever cell is selected when the macro is activated.
2) Activated another worksheet (named "Ranges") in the same spreadsheet,select columns K and L, and copy them
3) Paste Special/All onto the two new columns inserted in "Financials"
4) Deselect columns K and L in "Ranges" (don't care where the cursor ends up)
5) Select the cell on row 7 of the leftmost of the two columns inserted in "Financials"
Any help will be appreciated


Posted by CMorrigu on November 15, 2001 6:57 AM

just use the recorder, record each part as a separate macro, then chain them together in another sub..

activecell.column should replace whatever hardcoded value you record with.
Shadow Source

Posted by Paul Akkermans on November 15, 2001 7:00 AM

Record a macro which will do this and change next lines:
ActiveSheet.Unprotect Password:="password"
ActiveSheet.Protect Password:="password", DrawingObjects:=True, Contents:=True, Scenarios:=True

Posted by Stanley on November 15, 2001 7:54 AM

CMorrigu Can You Take A Look?

Here's what I came up with. At the point where the recorded macro specifically named the two columns I selected in "Financials", I changed code to "ActiveCell.Column.Select" thinking this is what you recommended. Got a "compile error; invalid qualifier" message.

I guess at that point I need code which will select both of the columns which where inserted into "Financials".

Any ideas?

Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Sheets("Ranges").Select
Columns("K:L").Select
Selection.Copy
Sheets("Financials").Select
ActiveCell.Column.Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=False
Range("I8").Select
Sheets("Ranges").Select
Application.CutCopyMode = False
Range("D1").Select
Sheets("Financials").Select

Posted by CMorrigu on November 15, 2001 11:12 AM

Re: CMorrigu Can You Take A Look?

Close, but not quite.... use the help functions to see the syntax and some example code.

Columns(ActiveCell.Column).Select
Shadow Source