Duplicating columns in Excel from Access

[GeorgeMar]

I am trying to copy a column and make a copy of the column next to it using
InsertCopiedCells

This macro in Excel works:

Columns("P").Select
Selection.Copy
Columns("Q:Q").Select
Selection.Insert Shift:=xlToRight

In Access I have

with wks2 'Worksheet 2
.columns(strColCopy).Select 'strColCopy=P
.columns.copy 'I had .Selection.Copy it didn't
work
.columns(strColPaste).Select 'strColPaste=Q:Q
.columns.Insert Shift:=-4161 'xlToRight=-4161 (I hope)
end with

The Access procedure gives error 1004: cannot copy and paste .....

any ideas

much appreciated
George

 

[GeorgeMar]

Following on from a post from Doug Steele, I cahnge the program to:

With WKS2
.range(.Columns(strColCopy), .Columns(strColCopy)).columns.copy
.range(.Columns(strColPaste), .Columns(strColPaste)).columns.Insert
end with

It works, but overwrites the copied column

I tried:
.range(.Columns(strColPaste),
..Columns(strColPaste).end(-4161)).columns.Insert

but that gives me an error

I have been searching this site for over 2 days now and can't find a way to
copy and paste a column in Excel. More importantly to Shift to Right.

many thanks
george

 

[Robert Morley]

Following on from a post from Doug Steele, I cahnge the program to:

With WKS2
.range(.Columns(strColCopy), .Columns(strColCopy)).columns.copy
.range(.Columns(strColPaste), .Columns(strColPaste)).columns.Insert
end with

It works, but overwrites the copied column

I tried:
.range(.Columns(strColPaste),
.Columns(strColPaste).end(-4161)).columns.Insert

but that gives me an error

I have been searching this site for over 2 days now and can't find a way to
copy and paste a column in Excel. More importantly to Shift to Right.

many thanks
george

Unfortunately, I'm rusty on my Excel syntax, but you're clearly getting
closer. You'd be better off asking in an Excel group. Just let them know
you're automating this from another application as opposed to working in
Excel directly.


Rob