VBA Help

[Miya]

Hi, what is the best method to copy data from Raw to Rec and switch the value and entry columns?

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Group	Rec	Account	Entry Date	Value Date	Type	Amount	CCY	Age	Source
2	CITI	JP	TEST	19-Jan-11	18-Jan-11	SCR	3.98	CAD	2	TEST
3	CITI	JP	TEST	19-Jan-11	18-Jan-11	SCR	10.66	CAD	2	TEST
4	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	4,678.47	GBP	1	TEST
5	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	77,736.95	GBP	1	TEST
6	CITI	JP	TEST	19-Jan-11	19-Jan-11	LDR	-54,396.71	USD	1	TEST
7	CITI	JP	TEST	19-Jan-11	19-Jan-11	LDR	-112,687.68	BRL	1	TEST
8	CITI	JP	TEST	19-Jan-11	19-Jan-11	LDR	-21,579.15	BRL	1	TEST
9	CITI	JP	TEST	19-Jan-11	19-Jan-11	SDR	-531,867,184.00	IDR	1	TEST
10	CITI	JP	TEST	19-Jan-11	12-Jan-11	SDR	-60,124,240.74	PKR	8	TEST
11	CITI	JP	TEST	21-Dec-10	1-Dec-10	LCR	0.72	TWD	50	TEST
12	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	58,582.13	USD	1	TEST
13	CITI	JP	TEST	19-Jan-11	12-Jan-11	SCR	700,912.11	USD	8	TEST
14	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	4,459,840.00	TWD	1	TEST
15	CITI	JP	TEST	19-Jan-11	19-Jan-11	SDR	-154,000.00	USD	1	TEST
16
Raw

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Group	Rec	Account	Value Date	Entry Date	Type	Amount	CCY	Age	Source
2	CITI	JP	TEST	18-Jan-11	19-Jan-11	SCR	3.98	CAD	2	TEST
3	CITI	JP	TEST	18-Jan-11	19-Jan-11	SCR	10.66	CAD	2	TEST
4	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	4,678.47	GBP	1	TEST
5	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	77,736.95	GBP	1	TEST
6	CITI	JP	TEST	19-Jan-11	19-Jan-11	LDR	-54,396.71	USD	1	TEST
7	CITI	JP	TEST	19-Jan-11	19-Jan-11	LDR	-112,687.68	BRL	1	TEST
8	CITI	JP	TEST	19-Jan-11	19-Jan-11	LDR	-21,579.15	BRL	1	TEST
9	CITI	JP	TEST	19-Jan-11	19-Jan-11	SDR	-531,867,184.00	IDR	1	TEST
10	CITI	JP	TEST	12-Jan-11	19-Jan-11	SDR	-60,124,240.74	PKR	8	TEST
11	CITI	JP	TEST	1-Dec-10	21-Dec-10	LCR	0.72	TWD	50	TEST
12	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	58,582.13	USD	1	TEST
13	CITI	JP	TEST	12-Jan-11	19-Jan-11	SCR	700,912.11	USD	8	TEST
14	CITI	JP	TEST	19-Jan-11	19-Jan-11	SCR	4,459,840.00	TWD	1	TEST
15	CITI	JP	TEST	19-Jan-11	19-Jan-11	SDR	-154,000.00	USD	1	TEST
16
17
Rec

 

[Norie]

Miya

Does the Rec worksheet have data in it already or are you trying to create it from the Raw worksheet?

If the Rec worksheet exists and is blank you could try something like this.

Option Explicit
 
Sub MakeRecFromRaw()
Dim wsRaw As Worksheet
Dim wsRec As Worksheet
 
    Set wsRaw = Worksheets("Raw")
    
    Set wsRec = Worksheets("Rec")
        
    wsRaw.Range("A:C").Copy wsRec.Range("A1")
    
    wsRaw.Range("E:E").Copy wsRec.Range("D1")
    
    wsRaw.Range("D:D").Copy wsRec.Range("E1")
    
    wsRaw.Range("F:J").Copy wsRec.Range("F1")
    
End Sub

 

[Peter_SSs]

Another possibility ..

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> CopyAndSwap()<br>    <SPAN style="color:#00007F">Dim</SPAN> wsRaw <SPAN style="color:#00007F">As</SPAN> Worksheet<br>    <SPAN style="color:#00007F">Dim</SPAN> wsRec <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br>    <SPAN style="color:#00007F">Set</SPAN> wsRaw = Worksheets("Raw")<br>    <SPAN style="color:#00007F">Set</SPAN> wsRec = Worksheets("Rec")<br>       <br>    <SPAN style="color:#00007F">With</SPAN> wsRec<br>        wsRaw.UsedRange.Copy Destination:=.Range("A1")<br>        .Columns("E").Cut<br>        .Columns("D").Insert<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>