designing a macro

[Za1]

hi im working for a compnay that requires me to generate reports on
monthly basis from raw files sent from the client. i was recently taske
to design a macro that is able to sort the data and put them in th
right places which i have difficulty with im hoping someone here woul
be able to help me out.

so if u look at the attached file. in sheet 2 is a example of my ra
data which i have to input into sheet 1 its pretty simple firstly i nee
to put redemption nominee account values into cash tab in sheet 1 fo
eg. sheet 2 c2 and d2 to be pasted into sheet 1 L3 and Q3 respectively
i could record a macro that copy paste whatever i do but as each mont
the amt of redemption and subscription varies how do i make it identif
whether isit firstly a redemption or subscription then by the typ
whether isit CPFOA or SRS or Cash and input them into the right column
accordingly

+-------------------------------------------------------------------
|Filename: New Microsoft Excel Worksheet.zip
|Download: http://www.excelbanter.com/attachment.php?attachmentid=604
+-------------------------------------------------------------------

 

[Don Guillett]

hi im working for a compnay that requires me to generate reports on a

monthly basis from raw files sent from the client. i was recently tasked

to design a macro that is able to sort the data and put them in the

right places which i have difficulty with im hoping someone here would

be able to help me out.



so if u look at the attached file. in sheet 2 is a example of my raw

data which i have to input into sheet 1 its pretty simple firstly i need

to put redemption nominee account values into cash tab in sheet 1 for

eg. sheet 2 c2 and d2 to be pasted into sheet 1 L3 and Q3 respectively.

i could record a macro that copy paste whatever i do but as each month

the amt of redemption and subscription varies how do i make it identify

whether isit firstly a redemption or subscription then by the type

whether isit CPFOA or SRS or Cash and input them into the right columns

accordingly.





+-------------------------------------------------------------------+

|Filename: New Microsoft Excel Worksheet.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=604|

+-------------------------------------------------------------------+

Be more specific:
sheet2!c2 >sheet1!__ because
etc

 

[Za1]

;1605940']On Wednesday, September 26, 2012 10:40:00 PM UTC-5, Za
wrote:-

hi im working for a compnay that requires me to generate reports on a

monthly basis from raw files sent from the client. i was recentl tasked

to design a macro that is able to sort the data and put them in the

right places which i have difficulty with im hoping someone her would

be able to help me out.



so if u look at the attached file. in sheet 2 is a example of my raw

data which i have to input into sheet 1 its pretty simple firstly need

to put redemption nominee account values into cash tab in sheet 1 for

eg. sheet 2 c2 and d2 to be pasted into sheet 1 L3 and Q respectively.

i could record a macro that copy paste whatever i do but as eac month

the amt of redemption and subscription varies how do i make i identify

whether isit firstly a redemption or subscription then by the type

whether isit CPFOA or SRS or Cash and input them into the righ columns

accordingly.





+-------------------------------------------------------------------+

|Filename: New Microsoft Excel Worksheet.zip |

|Download http://www.excelbanter.com/attachment.php?attachmentid=604|

+-------------------------------------------------------------------+

Be more specific:
sheet2!c2 >sheet1!__ because
etc

uh like for this small part i gave
sheet2!c2>sheet1!L3 (under cash cos nominee account under redemption co
red)
sheet2!d2>sheet1!q3(under cash cos nominee account under redemption co
red)


sheet2!c3>sheet1!L4(under cash cos nominee account under redemption co
red)

sheet2!d3>sheet1!q4(under cash cos nominee account under redemption co
red)


sheet2!c4>sheet1!j5(under CPFOA cos CPFOA under redemption cos red)
sheet2!d4>sheet1!o5(under CPFOA cos CPFOA under redemption cos red)


sheet2!c5>sheet1!j6(under CPFOA cos CPFOA under redemption cos red)
sheet2!d5>sheet1!o6(under CPFOA cos CPFOA under redemption cos red)


sheet2!c6>sheet1!k7(under SRS cos SRS under redemption cos red)
sheet2!d6>sheet1!p7(under SRS cos SRS under redemption cos red)


sheet2!c7>sheet1!k8(under SRS cos SRS under redemption cos red)
sheet2!d7>sheet1!p8(under SRS cos SRS under redemption cos red)


sheet2!c8>sheet1!c9(under cash cos nominee account under subcription co
sub)
sheet2!d8>sheet1!h9(under cash cos nominee account under subcription co
sub)



sheet2!c9>sheet1!c10(under cash cos nominee account under subcriptio
cos sub)
sheet2!d9>sheet1!h10(under cash cos nominee account under subcriptio
cos sub)


sheet2!c10>sheet1!c11(under cash cos nominee account under subcriptio
cos sub)
sheet2!d10>sheet1!h11(under cash cos nominee account under subcriptio
cos sub)


sheet2!c11>sheet1!a12(under CPFOA cos CPFOA under subcription cos sub)
sheet2!d11>sheet1!f12(under CPFOA cos CPFOA under subcription cos sub

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Don Guillett]

hi im working for a compnay that requires me to generate reports on a

monthly basis from raw files sent from the client. i was recently tasked

to design a macro that is able to sort the data and put them in the

right places which i have difficulty with im hoping someone here would

be able to help me out.



so if u look at the attached file. in sheet 2 is a example of my raw

data which i have to input into sheet 1 its pretty simple firstly i need

to put redemption nominee account values into cash tab in sheet 1 for

eg. sheet 2 c2 and d2 to be pasted into sheet 1 L3 and Q3 respectively.

i could record a macro that copy paste whatever i do but as each month

the amt of redemption and subscription varies how do i make it identify

whether isit firstly a redemption or subscription then by the type

whether isit CPFOA or SRS or Cash and input them into the right columns

accordingly.





+-------------------------------------------------------------------+

|Filename: New Microsoft Excel Worksheet.zip |

|Download: http://www.excelbanter.com/attachment.php?attachmentid=604|

+-------------------------------------------------------------------+

try
'===========
Option Explicit
Sub GetDataSAS()
Dim ds As Worksheet
Dim ss As Worksheet
Dim lr As Long
Dim c As Range
Dim mc As Integer
Dim x As Integer

Set ds = Sheet1
Set ss = Sheet2
lr = ss.Cells(Rows.Count, 1).End(xlUp).Row
For Each c In ss.Range("b2:b" & lr)

If UCase(c) = "SUB" Then
mc = 0
Else
mc = 10
End If

Select Case c.Offset(, -1)
Case "CFPOA": x = 0
Case "SRS": x = 1
Case "Nominee Account": x = 3
Case Else
End Select
c.Offset(, 1).Copy ds.Cells(Rows.Count, mc + x).End(xlUp)(2)
c.Offset(, 2).Copy ds.Cells(Rows.Count, mc + x + 4).End(xlUp)(2)
Next c
ds.Columns.AutoFit
End Sub