Copy Cells from WB to WB

[Dave Birley]

Ok, I know -- ol' Hercules only had to clean up those stables. I'm not so
sure he would have liked this one at all <g>!

I have a pair of WBs. The WS columns are laid out differently, and don't
even have consistent names (one of them is rather fond of abbreviations).

I need to copy relevant data from one to the other, and would prefer to do
it one Row's worth at a time. So, for example if I want to copy something
from rngCell(1, 2) of one WB!WS to rngCell(varLastRow, 17) of the other
WB!WS, and at the same time copy rngCell(1, 15) to rng(varLastRow, 9), how do
I go about explaining that to Excel?

If I can get the core format for doing this, I can hack it into submission
for the real world.

TIA!!

 

[Bryan Loeper]

Hey Dave! Long time no see!

Just to clarify, let's say you have a row in WB1!WS1 that has "a",
"b", "c", "d", and "e" in columns A:E and you're looking to copy "a",
"c", and "e" to WB2!WS2 columns A, C, and E, right?

I haven't found a way to yet (I can copy to A, B,and C), but I'm
working on it.

-Bryan

 

[Dave Birley]

Golleeee, you never know WHO they're going to let play in the sand box <g>!

Not quite what I want -- I need to make it more complicated than that
(remember me?) -- I want to copy "a", "c", and "e" to "J", "B", and "A" --
IOW, there is no relationship between the columns in the two WS!

Isn't this fun?

 

[Bryan Loeper]

Yeah, I know. Security around here is getting slack

The closest I've come is:

Option Explicit

Sub Macro1()
Dim Rng1 As Excel.Range
Dim c1 As Excel.Range

Set Rng1 = Workbooks("ABCD").Worksheets(1).Range("A1,C1,E1,G1")
For Each c1 In Rng1
Workbooks("Book1").Worksheets(1).Range(c1.Address) = c1.Value
Next c1

Set Rng1 = Workbooks("Book1").Worksheets(1).UsedRange
Rng1.Copy

Set Rng1 = Workbooks("ABCD").Worksheets(1).Range("A2")
Rng1.PasteSpecial skipblanks:=True
End Sub

Basically, it copies out specific cells, preserving spacing, to an
intermediate space, then copies them back preserving the spacing to
any starting cell you wish. Unfortunately, I can't mix them up
without just copying cell by cell. Even this gives you more overhead,
than cell by cell, I believe.

-Bryan