How to paste to alternating columns?

[bstevens]

I would greatly appreciate your help with a project I doing at work. I
checked the group and can't find any posts on it. Here's the scenario:

1. You have two separate workbooks.

2. You go to Worksheet A in Workbook 1 and select/copy some numerical
data in a table. The selected range covers 6 columns, say cells B5
through G50.

3. Then, you go to Worksheet A in Workbook 2 and paste the data into a
similar table, selecting the upper left cell in the table and then
selecting "paste." Simple, right? Here's the problem:

When you paste the data into the table in Workbook 2, you want to
paste it into every OTHER column in that table. Thus, after you paste
it, you'll see the 6 columns of the original table, with a blank
column to the right of each one. How do I do this? Please reply to
the newsgroup.

Many thanks,
Ron M.

 

[Don Guillett]

Fire this from the DESTINATION workbook

Sub gettablefromsource()
'copies
Workbooks("sourcebook.xls").Sheets("sheet1") _
..Columns("b5:g50").Copy Range("a1")

'inserts cols
For i = Cells(1, Columns.Count) _
..End(xlToLeft).Column To 2 Step -1
Columns(i).Insert
Next i
End Sub

 

[bstevens]

Fire this from the DESTINATION workbook

Sub gettablefromsource()
'copies
Workbooks("sourcebook.xls").Sheets("sheet1") _
.Columns("b5:g50").Copy Range("a1")

'inserts cols
For i = Cells(1, Columns.Count) _
.End(xlToLeft).Column To 2 Step -1
Columns(i).Insert
Next i
End Sub

Oh, gosh, I didn't know it was going to be like that. It would work,
but it doesn't approach the problem correctly.

1. The table that is being copied isn't always in b5:g50; it varies
from table to table. Setting a specific cell range in the code won't
work.

2. In the target table in Workbook 2, the "blank columns" in between
are actually pre-populated with a calculation formula that will
operate on the data in the pasted-in columns. So, what I'm going to
do is select 4 columns in a table in one workbook, with a varying
location and varying number of rows. Then I'll go to another workbook
and paste it into a pre-existing table which has 10 columns, and
columns c, e, g, and i are blank. I need to paste the data into those
blank columns.

Ron M.

 

[Don Guillett]

You should ALWAYS state the problem correctly the FIRST time to tell us what
you really want. Just one of my "pet peeves". Use the idea to paste one
column at a time.