designing a macro
 

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.

designing a macro
 

On Wednesday, September 26, 2012 10:40:00 PM UTC-5, Za1 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 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|

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







--

Za1

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

uh like for this small part i gave
sheet2!c2sheet1!L3 (under cash cos nominee account under redemption cos red)
sheet2!d2sheet1!q3(under cash cos nominee account under redemption cos red)


sheet2!c3sheet1!L4(under cash cos nominee account under redemption cos red)

sheet2!d3sheet1!q4(under cash cos nominee account under redemption cos red)


sheet2!c4sheet1!j5(under CPFOA cos CPFOA under redemption cos red)
sheet2!d4sheet1!o5(under CPFOA cos CPFOA under redemption cos red)


sheet2!c5sheet1!j6(under CPFOA cos CPFOA under redemption cos red)
sheet2!d5sheet1!o6(under CPFOA cos CPFOA under redemption cos red)


sheet2!c6sheet1!k7(under SRS cos SRS under redemption cos red)
sheet2!d6sheet1!p7(under SRS cos SRS under redemption cos red)


sheet2!c7sheet1!k8(under SRS cos SRS under redemption cos red)
sheet2!d7sheet1!p8(under SRS cos SRS under redemption cos red)


sheet2!c8sheet1!c9(under cash cos nominee account under subcription cos sub)
sheet2!d8sheet1!h9(under cash cos nominee account under subcription cos sub)



sheet2!c9sheet1!c10(under cash cos nominee account under subcription cos sub)
sheet2!d9sheet1!h10(under cash cos nominee account under subcription cos sub)


sheet2!c10sheet1!c11(under cash cos nominee account under subcription cos sub)
sheet2!d10sheet1!h11(under cash cos nominee account under subcription cos sub)


sheet2!c11sheet1!a12(under CPFOA cos CPFOA under subcription cos sub)
sheet2!d11sheet1!f12(under CPFOA cos CPFOA under subcription cos sub)

designing a macro
 

On Wednesday, September 26, 2012 10:40:00 PM UTC-5, Za1 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 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|

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







--

Za1

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