ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Copy data from one sheet to another (https://www.excelbanter.com/excel-programming/421933-copy-data-one-sheet-another.html)

John Pierce

Copy data from one sheet to another
 
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?

Mike H

Copy data from one sheet to another
 
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?


John Pierce

Copy data from one sheet to another
 


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

Mike H

Copy data from one sheet to another
 
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


Mike H

Copy data from one sheet to another
 
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


Rick Rothstein

Copy data from one sheet to another
 
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



John Pierce

Copy data from one sheet to another
 
Thanks Mike and Rick.


All times are GMT +1. The time now is 11:56 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com