Start with a WB with two existing sheets: Sh1, Sh2
Sh2 contains a lot of permanent records.
Sh1 contains temporary records for transfer to Sh2.
Sh1 has a header row and 7 columns (A-G) and
variable number of records.
I want to copy all records on Sh1 then paste on
Sh2 below existing records, but, for each record
Sh1 Column "A" goes in Sh2 Column "E"
Sh1 "B" goes in Sh2 "C"
Sh1 "C" goes in Sh2 "K"
etc.
I'm thinking copy Sh1 into a Dynamic Array and
then copy to Sh2 element by element?

Sh1 Column "A" goes in Sh2 Column "E"
Sh1 "B" goes in Sh2 "C"
Sh1 "C" goes in Sh2 "K"

Where does Sh1 columns E go for example if your putting A in column E?

Perhaps you should complete this table

Sh1 Sh2
A E
B C
C K
D ?
E ?
F ?
G ?


Mike

"John Pierce" wrote:

Start with a WB with two existing sheets: Sh1, Sh2
Sh2 contains a lot of permanent records.
Sh1 contains temporary records for transfer to Sh2.
Sh1 has a header row and 7 columns (A-G) and
variable number of records.
I want to copy all records on Sh1 then paste on
Sh2 below existing records, but, for each record
Sh1 Column "A" goes in Sh2 Column "E"
Sh1 "B" goes in Sh2 "C"
Sh1 "C" goes in Sh2 "K"
etc.
I'm thinking copy Sh1 into a Dynamic Array and
then copy to Sh2 element by element?

Sh1 Sh2
A E
B C
C K
D A
E F
F H
G G

Hi,

This is Sh1 worksheet code and assumes Sh2 is called Sheet2

Sub MoveIt()
Dim SourceColumn As Long
Dim DestinationColumn As Long
Dim LastRow As Long
For SourceColumn = 1 To 7
LastRow = Cells(Rows.Count, SourceColumn).End(xlUp).Row
Range(Cells(2, SourceColumn), Cells(LastRow, SourceColumn)).Copy
Select Case SourceColumn
Case Is = 1
DestinationColumn = 5
Case Is = 2
DestinationColumn = 3
Case Is = 3
DestinationColumn = 11
Case Is = 4
DestinationColumn = 1
Case Is = 5
DestinationColumn = 6
Case Is = 6
DestinationColumn = 8
Case Is = 7
DestinationColumn = 7
End Select
LastRow = Sheets("Sheet2").Cells(Rows.Count, DestinationColumn).End(xlUp).Row
Sheets("Sheet2").Cells(LastRow + 1, DestinationColumn).PasteSpecial
Next

Mike

"John Pierce" wrote:



Sh1 Sh2
A E
B C
C K
D A
E F
F H
G G

oops,
the
End Sub

is missing

Mike

"Mike H" wrote:

Hi,

This is Sh1 worksheet code and assumes Sh2 is called Sheet2

Sub MoveIt()
Dim SourceColumn As Long
Dim DestinationColumn As Long
Dim LastRow As Long
For SourceColumn = 1 To 7
LastRow = Cells(Rows.Count, SourceColumn).End(xlUp).Row
Range(Cells(2, SourceColumn), Cells(LastRow, SourceColumn)).Copy
Select Case SourceColumn
Case Is = 1
DestinationColumn = 5
Case Is = 2
DestinationColumn = 3
Case Is = 3
DestinationColumn = 11
Case Is = 4
DestinationColumn = 1
Case Is = 5
DestinationColumn = 6
Case Is = 6
DestinationColumn = 8
Case Is = 7
DestinationColumn = 7
End Select
LastRow = Sheets("Sheet2").Cells(Rows.Count, DestinationColumn).End(xlUp).Row
Sheets("Sheet2").Cells(LastRow + 1, DestinationColumn).PasteSpecial
Next

Mike

"John Pierce" wrote:



Sh1 Sh2
A E
B C
C K
D A
E F
F H
G G

Select Case SourceColumn
Case Is = 1
DestinationColumn = 5
Case Is = 2
DestinationColumn = 3
Case Is = 3
DestinationColumn = 11
Case Is = 4
DestinationColumn = 1
Case Is = 5
DestinationColumn = 6
Case Is = 6
DestinationColumn = 8
Case Is = 7
DestinationColumn = 7
End Select

An alternate to (that is, replacement for) the above section of your code
that the OP may want to consider using is this single line statement...

DestinationColumn = Choose(SourceColumn, 5, 3, 11, 1, 6, 8, 7)

--
Rick (MVP - Excel)


"Mike H" wrote in message
...
Hi,

This is Sh1 worksheet code and assumes Sh2 is called Sheet2

Sub MoveIt()
Dim SourceColumn As Long
Dim DestinationColumn As Long
Dim LastRow As Long
For SourceColumn = 1 To 7
LastRow = Cells(Rows.Count, SourceColumn).End(xlUp).Row
Range(Cells(2, SourceColumn), Cells(LastRow, SourceColumn)).Copy
Select Case SourceColumn
Case Is = 1
DestinationColumn = 5
Case Is = 2
DestinationColumn = 3
Case Is = 3
DestinationColumn = 11
Case Is = 4
DestinationColumn = 1
Case Is = 5
DestinationColumn = 6
Case Is = 6
DestinationColumn = 8
Case Is = 7
DestinationColumn = 7
End Select
LastRow = Sheets("Sheet2").Cells(Rows.Count,
DestinationColumn).End(xlUp).Row
Sheets("Sheet2").Cells(LastRow + 1, DestinationColumn).PasteSpecial
Next

Mike

"John Pierce" wrote:



Sh1 Sh2
A E
B C
C K
D A
E F
F H
G G

Thanks Mike and Rick.