+ Reply to Thread
Results 1 to 20 of 20

Consolidated data from 3 worksheets and populate to another sheets

Hybrid View

  1. 03-19-2013, 01:38 AM #1
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Consolidated data from 3 worksheets and populate to another sheets

    Hi All,

    I have one workbook contains multiple worksheets.
    Sheets database as database all transaction, sheets S7 as database limit and sheet S6 as counterparty name.

    I already have macro code, but it's run slow. Any one could help me with this case.


    Edit Post:

    I need to store data in array an populate to sheet.
    Example:
    Sheet "database":
    Ctg | utilisasi | BNB |
    A | 20 | Y |
    B | 10 | N |
    A | 5 | Y |
    A | 25 | Y |
    B | 2 | N |
    C | 100 | Y |
    D | 22 | N |
    D | 10 | N |

    Sheet "name":
    LBL | Full |
    A | Andaro |
    B | Bandada |
    C | Calamanta |
    D | Delemi |

    Sheet "Limit":
    Code | Limit | BNB1 |
    A | 200 | Y
    B | 300 | N
    C | 100 | Y
    D | 50 | N

    Result that i need:

    Sheet "1":
    CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
    A | Andaro | 200 | 50 | 150
    C | Calamanta | 100 | 100 | 0

    Sheet "2":
    CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
    B | Bandada | 300 | 12 | 288
    D | Delemi | 50 | 32 | 18
    Thank you,
    Last edited by uky; 03-21-2013 at 10:23 PM.
    Reply With QuoteRegister To Reply
  2. 03-19-2013, 03:09 AM #2
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi Admin,

    Here with my code:
    Sub ins_all()
s0 = "Report"

rep_type = Sheets(s0).Cells(4, 5)

Application.ScreenUpdating = False
Application.EnableEvents = False

For rp = 1 To 24
    Select Case rp
        Case "1"
            s = "1"
            ins_lim s
            Sheets(s).Range("I5:J65335").ClearContents
            Sheets(s).Range("L5:U65335").ClearContents
            ins_uti s
            ins_avai s
            ins_ctp_name s
            ins_perc s
        Case "2"
            s = "2"
            ins_lim s
            Sheets(s).Range("I5:J65335").ClearContents
            Sheets(s).Range("L5:U65335").ClearContents
            ins_uti s
            ins_avai s
            ins_ctp_name s
            ins_perc s
    End Select
Next rp

Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
    macro ins_lim:
    Sub ins_lim(sh)
Dim x, y(), i As Integer, J As Integer
s0 = "Report"
s1 = "S7"

rep_type = Sheets(s0).Cells(4, 5)
rep_post = Sheets(s0).Cells(5, 5)

Application.ScreenUpdating = False
Application.EnableEvents = False

If rep_type = "Pagi" Then
    With Sheets(s1).Range("A6").CurrentRegion
        x = .Value
    End With
    Select Case sh
        Case "1"
            ReDim y(1 To UBound(x, 1), 1 To UBound(x, 2)) 'array nya disiapkan sebesar range
            For i = 6 To UBound(x, 1)
                If x(i, 3) = "FX" And x(i, 11) = "Y" Then
                    x(i, 3) = "FX Bank"
                    p = p + 1
                    For J = 1 To UBound(x, 2)
                        y(p, 1) = rep_post 'tgl
                        y(p, 2) = x(i, 2) 'ctp label
                        y(p, 4) = x(i, 3) 'risk type
                        y(p, 5) = x(i, 4) 'max tenor
                        y(p, 6) = x(i, 5) 'limit
                        y(p, 7) = x(i, 9) 'ccy
                        y(p, 10) = x(i, 10) 'limit expiry
                    Next J
                End If
            Next i
            With Sheets(sh)
                If p <> 0 Then
                    .Range("B5:O65553").ClearContents
                    .Range("B5").Resize(p, UBound(x, 2)).Value = y
                End If
                p = 0
            End With
            Erase y()
        Case "2"
            ReDim y(1 To UBound(x, 1), 1 To UBound(x, 2)) 'array nya disiapkan sebesar range
            For i = 6 To UBound(x, 1)
                If x(i, 3) = "FX" And x(i, 11) = "N" Then
                    x(i, 3) = "FX NonBank"
                    p = p + 1
                    For J = 1 To UBound(x, 2)
                        y(p, 1) = rep_post 'tgl
                        y(p, 2) = x(i, 2) 'ctp label
                        y(p, 4) = x(i, 3) 'risk type
                        y(p, 5) = x(i, 4) 'max tenor
                        y(p, 6) = x(i, 5) 'limit
                        y(p, 7) = x(i, 9) 'ccy
                        y(p, 10) = x(i, 10) 'limit expiry
                    Next J
                End If
            Next i
            With Sheets(sh)
                If p <> 0 Then
                    .Range("B5:O65553").ClearContents
                    .Range("B5").Resize(p, UBound(x, 2)).Value = y
                End If
                p = 0
            End With
            Erase y()
    End Select
End If

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub
    Continue to below post
    Reply With QuoteRegister To Reply
  3. 03-19-2013, 03:11 AM #3
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    macro ins_uti:
    Sub ins_uti(sh)
s0 = "Report"
s1 = "Database"

rep_date = Sheets(s0).Cells(3, 5)
rep_type = Sheets(s0).Cells(4, 5)
date_report = Sheets(s0).Cells(5, 5)

Application.ScreenUpdating = False
Application.EnableEvents = False

With Sheets(s1).Range("A6").CurrentRegion
    x = .Value
End With

For a = 6 To 60000
    trd1 = Sheets(s1).Cells(a, 1)
    db1 = Sheets(s1).Cells(a, 2)
    ie1 = Sheets(s1).Cells(a, 3)
    fam1 = Sheets(s1).Cells(a, 4)
    grp1 = Sheets(s1).Cells(a, 5)
    typ1 = Sheets(s1).Cells(a, 6)
    stt1 = Sheets(s1).Cells(a, 7)
    bsl1 = Sheets(s1).Cells(a, 8)
    ctg1 = Sheets(s1).Cells(a, 10)
    pli1 = Sheets(s1).Cells(a, 11)
    dte1 = Sheets(s1).Cells(a, 12)
    mat1 = Sheets(s1).Cells(a, 13)
    ccy1 = Sheets(s1).Cells(a, 14)
    nom1 = Sheets(s1).Cells(a, 15)
    dea1 = Sheets(s1).Cells(a, 16)
    tpo1 = Sheets(s1).Cells(a, 18)
    cls1 = Sheets(s1).Cells(a, 20)
    tnr1 = Sheets(s1).Cells(a, 22)
    crf1 = Sheets(s1).Cells(a, 23)
    mtm1_2 = Sheets(s1).Cells(a, 24) ' mtm in idr
    mtm1 = Sheets(s1).Cells(a, 25) 'mtm ory ccy
    add1_2 = Sheets(s1).Cells(a, 26) ' addon in idr
    add1 = Sheets(s1).Cells(a, 27) 'uti ory
    uti1_2 = Sheets(s1).Cells(a, 28) 'uti in idr
    uti1 = Sheets(s1).Cells(a, 29) 'uti in ory
    bnb1 = Sheets(s1).Cells(a, 30)
    nom1_x = Sheets(s1).Cells(a, 31)
    iss1 = Sheets(s1).Cells(a, 32)
    nom1_2 = Sheets(s1).Cells(a, 33)
    bis1 = Sheets(s1).Cells(a, 34)
    flg1 = Sheets(s1).Cells(a, 35)
    If iss1 = "USGOVT" Then
        nom1_2 = Sheets(s1).Cells(a, 15)
    End If
    If trd1 = "" Then Exit For
    Select Case sh
        Case "1"
            If db1 = "DFx" And bnb1 = "Y" Then
                For b = 5 To 6000 'Sheets 3
                    tgl2 = Sheets(sh).Cells(b, 2)
                    ctg2 = Sheets(sh).Cells(b, 3)
                    lmt2 = Sheets(sh).Cells(b, 7)
                    ccy2 = Sheets(sh).Cells(b, 8)
                    uti2 = Sheets(sh).Cells(b, 9)
                    mtm2 = Sheets(sh).Cells(b, 14)
                        If ctg2 = ctg1 Then
                            lma = 0
                            nte = ""
                            uti3 = uti1_2 + uti2
                            mtm3 = mtm1_2 + mtm2
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm1_2 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm1_2 > 0 Or mtm1_2 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                            Exit For
                        End If
                        If ctg2 = "" Then
                            lma = 0
                            nte = ""
                            lmt2 = 0
                            uti3 = nom1_x + uti2
                            mtm3 = nom1_x + mtm2
                            Sheets(sh).Cells(b, 2) = rep_date
                            Sheets(sh).Cells(b, 3) = ctg1
                            Sheets(sh).Cells(b, 5) = "FX Bank"
                            Sheets(sh).Cells(b, 7) = lmt2
                            Sheets(sh).Cells(b, 8) = "IDR"
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            Sheets(sh).Cells(b, 11) = "0"
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm3 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm3 > 0 Or mtm3 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                        Exit For
                    End If
                Next b
            End If
        Case "2"
            If db1 = "DFx" And bnb1 = "N" Then
                For b = 5 To 6000 'Sheets 4
                    tgl2 = Sheets(sh).Cells(b, 2)
                    ctg2 = Sheets(sh).Cells(b, 3)
                    lmt2 = Sheets(sh).Cells(b, 7)
                    ccy2 = Trim(Sheets(sh).Cells(b, 8))
                    uti2 = Sheets(sh).Cells(b, 9)
                    mtm2 = Sheets(sh).Cells(b, 14)
                        If ctg2 = ctg1 Then
                            lma = 0
                            nte = ""
                            uti3 = uti1_2 + uti2
                            mtm3 = mtm1_2 + mtm2
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm3 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm3 > 0 Or mtm3 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                            Exit For
                        End If
                        If ctg2 = "" Then
                            lma = 0
                            nte = ""
                            lmt2 = 0
                            uti3 = nom1_x + uti2
                            mtm3 = nom1_x + mtm2
                            Sheets(sh).Cells(b, 2) = rep_date
                            Sheets(sh).Cells(b, 3) = ctg1
                            Sheets(sh).Cells(b, 5) = "FX NonBank"
                            Sheets(sh).Cells(b, 7) = lmt2
                            Sheets(sh).Cells(b, 8) = "IDR"
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            Sheets(sh).Cells(b, 11) = "0"
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm3 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm3 > 0 Or mtm3 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                        Exit For
                    End If
                Next b
            End If
    End Select
Next a
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
    macro ins_convert:
    Sub ins_convert(sh)
s1 = "S1"

Application.ScreenUpdating = False
Application.EnableEvents = False

    For a = 5 To 6000
        tgl1 = Sheets(sh).Cells(a, 2)
        lim1 = Sheets(sh).Cells(a, 7)
        ccy1 = Sheets(sh).Cells(a, 8)
        uti1 = Sheets(sh).Cells(a, 9)
        nte1 = Sheets(sh).Cells(a, 12)
        mtm1 = Sheets(sh).Cells(a, 14)
        If tgl1 = "" Then Exit For
        Select Case sh
            Case "4"
                If ccy1 = "USD" Then
                    ccy1 = ccy1 + "-IDR"
                    For b = 6 To 6000
                        ccy2 = Sheets(s1).Cells(b, 7)
                        prc2 = Sheets(s1).Cells(b, 8)
                        prc3 = Sheets(s1).Cells(b, 11)
                        If ccy2 = "" Then Exit For
                        If ccy2 = ccy1 Then
                            nte = ""
                            uti3 = uti1 / prc2
                            mtm3 = mtm1 / prc3
                            Sheets(sh).Cells(a, 9) = uti3
                            lma = lim1 - uti3
                            Sheets(sh).Cells(a, 10) = lma
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(a, 12) = nte
                            Sheets(sh).Cells(a, 14) = mtm3
                            Exit For
                        End If
                    Next b
                ElseIf ccy1 = "EUR" Then
                    ccy1 = ccy1 + "-IDR"
                    For b = 6 To 6000
                        ccy2 = Sheets(s1).Cells(b, 7)
                        prc2 = Sheets(s1).Cells(b, 8)
                        prc3 = Sheets(s1).Cells(b, 11)
                        If ccy2 = "" Then Exit For
                        If ccy2 = ccy1 Then
                            nte = ""
                            uti3 = uti1 / prc2
                            mtm3 = mtm1 / prc3
                            Sheets(sh).Cells(a, 9) = uti3
                            lma = lim1 - uti3
                            Sheets(sh).Cells(a, 10) = lma
                            If lma < 0 Then
                                nte = "Breach"
                            End If
                            If lma > 0 Then
                                nte = ""
                            End If
                            Sheets(sh).Cells(a, 12) = nte
                            Sheets(sh).Cells(a, 14) = mtm3
                            Exit For
                        End If
                    Next b
                End If
        End Select
    Next a
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
    Reply With QuoteRegister To Reply
  4. 03-19-2013, 03:13 AM #4
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    macro ins_uti:
    Sub ins_uti(sh)
s0 = "Report"
s1 = "Database"

rep_date = Sheets(s0).Cells(3, 5)
rep_type = Sheets(s0).Cells(4, 5)
date_report = Sheets(s0).Cells(5, 5)

Application.ScreenUpdating = False
Application.EnableEvents = False

With Sheets(s1).Range("A6").CurrentRegion
    x = .Value
End With

For a = 6 To 60000
    trd1 = Sheets(s1).Cells(a, 1)
    db1 = Sheets(s1).Cells(a, 2)
    ie1 = Sheets(s1).Cells(a, 3)
    fam1 = Sheets(s1).Cells(a, 4)
    grp1 = Sheets(s1).Cells(a, 5)
    typ1 = Sheets(s1).Cells(a, 6)
    stt1 = Sheets(s1).Cells(a, 7)
    bsl1 = Sheets(s1).Cells(a, 8)
    ctg1 = Sheets(s1).Cells(a, 10)
    pli1 = Sheets(s1).Cells(a, 11)
    dte1 = Sheets(s1).Cells(a, 12)
    mat1 = Sheets(s1).Cells(a, 13)
    ccy1 = Sheets(s1).Cells(a, 14)
    nom1 = Sheets(s1).Cells(a, 15)
    dea1 = Sheets(s1).Cells(a, 16)
    tpo1 = Sheets(s1).Cells(a, 18)
    cls1 = Sheets(s1).Cells(a, 20)
    tnr1 = Sheets(s1).Cells(a, 22)
    crf1 = Sheets(s1).Cells(a, 23)
    mtm1_2 = Sheets(s1).Cells(a, 24) ' mtm in idr
    mtm1 = Sheets(s1).Cells(a, 25) 'mtm ory ccy
    add1_2 = Sheets(s1).Cells(a, 26) ' addon in idr
    add1 = Sheets(s1).Cells(a, 27) 'uti ory
    uti1_2 = Sheets(s1).Cells(a, 28) 'uti in idr
    uti1 = Sheets(s1).Cells(a, 29) 'uti in ory
    bnb1 = Sheets(s1).Cells(a, 30)
    nom1_x = Sheets(s1).Cells(a, 31)
    iss1 = Sheets(s1).Cells(a, 32)
    nom1_2 = Sheets(s1).Cells(a, 33)
    bis1 = Sheets(s1).Cells(a, 34)
    flg1 = Sheets(s1).Cells(a, 35)
    If iss1 = "USGOVT" Then
        nom1_2 = Sheets(s1).Cells(a, 15)
    End If
    If trd1 = "" Then Exit For
    Select Case sh
        Case "1"
            If db1 = "DFx" And bnb1 = "Y" Then
                For b = 5 To 6000 'Sheets 3
                    tgl2 = Sheets(sh).Cells(b, 2)
                    ctg2 = Sheets(sh).Cells(b, 3)
                    lmt2 = Sheets(sh).Cells(b, 7)
                    ccy2 = Sheets(sh).Cells(b, 8)
                    uti2 = Sheets(sh).Cells(b, 9)
                    mtm2 = Sheets(sh).Cells(b, 14)
                        If ctg2 = ctg1 Then
                            lma = 0
                            nte = ""
                            uti3 = uti1_2 + uti2
                            mtm3 = mtm1_2 + mtm2
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm1_2 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm1_2 > 0 Or mtm1_2 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                            Exit For
                        End If
                        If ctg2 = "" Then
                            lma = 0
                            nte = ""
                            lmt2 = 0
                            uti3 = nom1_x + uti2
                            mtm3 = nom1_x + mtm2
                            Sheets(sh).Cells(b, 2) = rep_date
                            Sheets(sh).Cells(b, 3) = ctg1
                            Sheets(sh).Cells(b, 5) = "FX Bank"
                            Sheets(sh).Cells(b, 7) = lmt2
                            Sheets(sh).Cells(b, 8) = "IDR"
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            Sheets(sh).Cells(b, 11) = "0"
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm3 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm3 > 0 Or mtm3 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                        Exit For
                    End If
                Next b
            End If
        Case "2"
            If db1 = "DFx" And bnb1 = "N" Then
                For b = 5 To 6000 'Sheets 4
                    tgl2 = Sheets(sh).Cells(b, 2)
                    ctg2 = Sheets(sh).Cells(b, 3)
                    lmt2 = Sheets(sh).Cells(b, 7)
                    ccy2 = Trim(Sheets(sh).Cells(b, 8))
                    uti2 = Sheets(sh).Cells(b, 9)
                    mtm2 = Sheets(sh).Cells(b, 14)
                        If ctg2 = ctg1 Then
                            lma = 0
                            nte = ""
                            uti3 = uti1_2 + uti2
                            mtm3 = mtm1_2 + mtm2
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm3 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm3 > 0 Or mtm3 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                            Exit For
                        End If
                        If ctg2 = "" Then
                            lma = 0
                            nte = ""
                            lmt2 = 0
                            uti3 = nom1_x + uti2
                            mtm3 = nom1_x + mtm2
                            Sheets(sh).Cells(b, 2) = rep_date
                            Sheets(sh).Cells(b, 3) = ctg1
                            Sheets(sh).Cells(b, 5) = "FX NonBank"
                            Sheets(sh).Cells(b, 7) = lmt2
                            Sheets(sh).Cells(b, 8) = "IDR"
                            Sheets(sh).Cells(b, 9) = uti3
                            lma = lmt2 - uti3
                            Sheets(sh).Cells(b, 10) = lma
                            Sheets(sh).Cells(b, 11) = "0"
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(b, 12) = nte
                            If mtm3 < 0 Then
                                Sheets(sh).Cells(b, 14) = 0
                            End If
                            If mtm3 > 0 Or mtm3 = 0 Then
                                Sheets(sh).Cells(b, 14) = mtm3
                            End If
                        Exit For
                    End If
                Next b
            End If
    End Select
Next a
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
    Reply With QuoteRegister To Reply
  5. 03-19-2013, 03:14 AM #5
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    macro ins_convert:
    Sub ins_convert(sh)
s1 = "S1"

Application.ScreenUpdating = False
Application.EnableEvents = False

    For a = 5 To 6000
        tgl1 = Sheets(sh).Cells(a, 2)
        lim1 = Sheets(sh).Cells(a, 7)
        ccy1 = Sheets(sh).Cells(a, 8)
        uti1 = Sheets(sh).Cells(a, 9)
        nte1 = Sheets(sh).Cells(a, 12)
        mtm1 = Sheets(sh).Cells(a, 14)
        If tgl1 = "" Then Exit For
        Select Case sh
            Case "2"
                If ccy1 = "USD" Then
                    ccy1 = ccy1 + "-IDR"
                    For b = 6 To 6000
                        ccy2 = Sheets(s1).Cells(b, 7)
                        prc2 = Sheets(s1).Cells(b, 8)
                        prc3 = Sheets(s1).Cells(b, 11)
                        If ccy2 = "" Then Exit For
                        If ccy2 = ccy1 Then
                            nte = ""
                            uti3 = uti1 / prc2
                            mtm3 = mtm1 / prc3
                            Sheets(sh).Cells(a, 9) = uti3
                            lma = lim1 - uti3
                            Sheets(sh).Cells(a, 10) = lma
                            If lma < 0 Then nte = "Breach"
                            Sheets(sh).Cells(a, 12) = nte
                            Sheets(sh).Cells(a, 14) = mtm3
                            Exit For
                        End If
                    Next b
                ElseIf ccy1 = "EUR" Then
                    ccy1 = ccy1 + "-IDR"
                    For b = 6 To 6000
                        ccy2 = Sheets(s1).Cells(b, 7)
                        prc2 = Sheets(s1).Cells(b, 8)
                        prc3 = Sheets(s1).Cells(b, 11)
                        If ccy2 = "" Then Exit For
                        If ccy2 = ccy1 Then
                            nte = ""
                            uti3 = uti1 / prc2
                            mtm3 = mtm1 / prc3
                            Sheets(sh).Cells(a, 9) = uti3
                            lma = lim1 - uti3
                            Sheets(sh).Cells(a, 10) = lma
                            If lma < 0 Then
                                nte = "Breach"
                            End If
                            If lma > 0 Then
                                nte = ""
                            End If
                            Sheets(sh).Cells(a, 12) = nte
                            Sheets(sh).Cells(a, 14) = mtm3
                            Exit For
                        End If
                    Next b
                End If
        End Select
    Next a
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
    Reply With QuoteRegister To Reply
  6. 03-19-2013, 10:00 AM #6
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi! Anyone can help with my code?
    So my codes simple and fast.
    Reply With QuoteRegister To Reply
  7. 03-19-2013, 11:16 AM #7
    AB33
    AB33 is offline
    Forum Expert
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Consolidated data from 3 worksheets and populate to another sheets

    UKY,
    No, you code is very long and diffcult to follow it. Jerry has also asked you to include you code with the attached, but instead you have almost filled in an enitre page with your code.
    Please explain what are you trying to do and people may be able to help.
    Reply With QuoteRegister To Reply
  8. 03-19-2013, 05:44 PM #8
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Most of the "tricks" I know for speeding up most people's code, you've got that already. It appears you running "loops", 6000 loops inside of 6000 loops. Within that looping you're writing to and from the sheet. All of that activity takes a alot of time.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)
    Reply With QuoteRegister To Reply
  9. 03-19-2013, 09:39 PM #9
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi Ab and Jerry,

    Thanks for your respons.
    I attached again file with macro included.
    And AB, your codes is part of my codes now(previous macro that i ask to forum..:D)
    Here with my file: icon2.jpg
    Last edited by uky; 03-20-2013 at 03:52 AM.
    Reply With QuoteRegister To Reply
  10. 03-19-2013, 10:02 PM #10
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Without referring to VBA, can you tell us verbally what is being accomplished by this macro?
    Reply With QuoteRegister To Reply
  11. 03-19-2013, 11:26 PM #11
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi Jerry,

    Sheet “1”:
    1. Filter in sheets database with column AD = “Y”
    2. Data Column C from column J[ sheets database] and sum column AB
    3. Data Column E from column C[sheet S7].
    4. Data Column F from column D[sheet S7].
    5. Data Column G from column E[sheet S7].
    6. Data Column H from column I[sheet S7].
    7. Data Column K from column J[sheet S7].
    Sheet “2”:
    1. Filter in sheets database with column AD = “N”
    2. Data Column C from column J[ sheets database] and sum column AB
    3. Data Column E from column C[sheet S7].
    4. Data Column F from column D[sheet S7].
    5. Data Column G from column E[sheet S7].
    6. Data Column H from column I[sheet S7].
    7. Data Column K from column J[sheet S7].

    Hope this clarify what i need.
    Reply With QuoteRegister To Reply
  12. 03-20-2013, 12:24 AM #12
    JBeaucaire
    JBeaucaire is offline
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Consolidated data from 3 worksheets and populate to another sheets

    I understand what 1. means. I don't understand what results / where results are derived from 2-7.
    Reply With QuoteRegister To Reply
  13. 03-20-2013, 12:33 AM #13
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi Jerry,

    Sheet 1:
    1. Filter in sheets database with column AD = “Y”
    2. Value in Column C sheets(1) from column J[ sheets database] and sum column AB(sheets database)
    3. Value in Column D sheets(1) from column D[ sheets S6]
    4. Value in Column E sheets(1) from column C[sheet S7] = "FX" and column K[sheets S7] = "Y".
    5. Value in Column F sheets(1) from column D[sheet S7].
    6. Value in Column G from column E[sheet S7].
    7. Value in Column H from column I[sheet S7].
    8. Value in Column I from sum column AB(sheets database)
    9. Data Column K from column J[sheet S7].

    Sheet 2:
    1. Filter in sheets database with column AD = “N”
    2. Value in Column C sheets(1) from column J[ sheets database] and sum column AB(sheets database)
    3. Value in Column D sheets(1) from column D[ sheets S6]
    4. Value in Column E sheets(1) from column C[sheet S7] = "FX" and column K[sheets S7] = "N".
    5. Value in Column F sheets(1) from column D[sheet S7].
    6. Value in Column G from column E[sheet S7].
    7. Value in Column H from column I[sheet S7].
    8. Value in Column I from sum column AB(sheets database)
    9. Data Column K from column J[sheet S7].

    This is it jerry.

    Thank you.
    Reply With QuoteRegister To Reply
  14. 03-20-2013, 03:07 AM #14
    abousetta
    abousetta is offline
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Without trying to rewrite your code since you obviously understand it better than I do, and have shown the ability to deal with arrays, my suggestions would be the following:

    1) Use With... End With instead of hardcoding sheet identifiers. I have read that this speeds up the code because it stores the sheet (or other object identifier) in memory.

    2) Instead of printing to one cell at a time, convert the range that wil be manipulated to an array and make the changes to the array. Then only in the end of the subroutine, print the full array to the worksheet. Repeated writing of cells takes time even if screenupdating is turned off.

    3) If the subs are running independently then there are ways to trick Excel into running them simulatenously even though vba does not support multithreading.

    Hope this helps.

    abousetta
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
    Reply With QuoteRegister To Reply
  15. 03-20-2013, 03:48 AM #15
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi abousetta,

    Thank's for your suggestion. But i thing, i need don't need to loop entire sheets.
    I need to store data in array an populate to sheet.
    Example:
    Sheet "database":
    Ctg | utilisasi | BNB |
    A | 20 | Y |
    B | 10 | N |
    A | 5 | Y |
    A | 25 | Y |
    B | 2 | N |
    C | 100 | Y |
    D | 22 | N |
    D | 10 | N |

    Sheet "name":
    LBL | Full |
    A | Andaro |
    B | Bandada |
    C | Calamanta |
    D | Delemi |

    Sheet "Limit":
    Code | Limit | BNB1 |
    A | 200 | Y
    B | 300 | N
    C | 100 | Y
    D | 50 | N

    Result that i need:

    Sheet "1":
    CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
    A | Andaro | 200 | 50 | 150
    C | Calamanta | 100 | 100 | 0

    Sheet "2":
    CTG | CTG NAME | LIMIT | UTILISASI | AVAILABLE LIMIT
    B | Bandada | 300 | 12 | 288
    D | Delemi | 50 | 32 | 18
    Reply With QuoteRegister To Reply
  16. 03-20-2013, 08:46 AM #16
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi Jerry,

    Need your assistant.

    Thank you
    Reply With QuoteRegister To Reply
  17. 03-20-2013, 11:26 AM #17
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Consolidated data from 3 worksheets and populate to another sheets

    uky,

    Attached is an example workbook based on the sample data and expected output you provided.
    It contains the following macro:
    Sub tgr()
    
    Dim wsDB As Worksheet
    Dim wsName As Worksheet
    Dim wsLimit As Worksheet
    Dim wsDest As Worksheet
    Dim rngFound As Range
    Dim lCalc As XlCalculation
    Dim arrData(1 To 65000, 1 To 5) As Variant
    Dim varFind As Variant
    Dim strFirst As String
    Dim strUnq As String
    Dim DataIndex As Long
    
    With Application
        lCalc = .Calculation
        .Calculation = xlCalculationManual
        .EnableEvents = False
        .ScreenUpdating = False
    End With
    
    On Error GoTo CleanExit
    
    Set wsDB = Sheets("database")
    Set wsName = Sheets("name")
    Set wsLimit = Sheets("Limit")
    
    For Each varFind In Split("Y|N", "|")
        DataIndex = 0
        Erase arrData
        If varFind = "Y" Then Set wsDest = Sheets("1") Else Set wsDest = Sheets("2")
        Set rngFound = wsDB.Columns("C").Find(varFind, , xlValues, xlWhole)
        If Not rngFound Is Nothing Then
            strFirst = rngFound.Address
            Do
                If InStr(1, "||" & strUnq & "||", "||" & wsDB.Cells(rngFound.Row, "A").Text & "||", vbTextCompare) = 0 Then
                    DataIndex = DataIndex + 1
                    arrData(DataIndex, 1) = wsDB.Cells(rngFound.Row, "A").Text
                    arrData(DataIndex, 2) = WorksheetFunction.VLookup(arrData(DataIndex, 1), wsName.UsedRange, 2, False)
                    arrData(DataIndex, 3) = WorksheetFunction.VLookup(arrData(DataIndex, 1), wsLimit.UsedRange, 2, False)
                    arrData(DataIndex, 4) = WorksheetFunction.SumIf(wsDB.Columns("A"), arrData(DataIndex, 1), wsDB.Columns("B"))
                    arrData(DataIndex, 5) = arrData(DataIndex, 3) - arrData(DataIndex, 4)
                    strUnq = strUnq & "||" & arrData(DataIndex, 1) & "||"
                End If
                Set rngFound = wsDB.Columns("C").Find(varFind, rngFound, xlValues, xlWhole)
            Loop While rngFound.Address <> strFirst
        End If
        If DataIndex > 0 Then
            wsDest.UsedRange.Offset(1).Clear
            wsDest.Range("A2").Resize(DataIndex, UBound(arrData, 2)).Value = arrData
        End If
    Next varFind
    
CleanExit:
    With Application
        .Calculation = lCalc
        .EnableEvents = True
        .ScreenUpdating = True
    End With
    
    If Err.Number <> 0 Then
        MsgBox Err.Description, , "Error: " & Err.Number
        Err.Clear
    End If
    
    Set wsDB = Nothing
    Set wsName = Nothing
    Set wsLimit = Nothing
    Set wsDest = Nothing
    Set rngFound = Nothing
    Erase arrData
    
End Sub

    Running that macro will pull the data and give the expected outputs on the appropriate sheets. Hopefully you can adapt it to suit your actual workbook.
    Attached Files Attached Files
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  18. 03-20-2013, 09:55 PM #18
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Hi Tigeravatar,

    Thank you for your help and it's work perfect.
    I'm trying to understand your codes. but I still do not understand much.
    Could you give comments next line of your code?
    This code, need comment:
    If InStr(1, "||" & strUnq & "||", "||" & wsDB.Cells(rngFound.Row, "A").Text & "||", vbTextCompare) = 0 Then
    Last edited by uky; 03-21-2013 at 04:05 AM.
    Reply With QuoteRegister To Reply
  19. 03-21-2013, 10:55 AM #19
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Consolidated data from 3 worksheets and populate to another sheets

    That line of code ensures that it only pulls data the first time a new CTG is encountered in column A. In the sample data there are duplicates ("A" is shown three times, "B" is listed twice, etc), so in order to increase efficiency and accuracy, that line prevents the code from running the same data more than once.
    Reply With QuoteRegister To Reply
  20. 03-21-2013, 10:23 PM #20
    uky
    uky is offline
    Registered User
    Join Date
    03-20-2012
    Location
    TBA
    MS-Off Ver
    Excel 2007
    Posts
    24

    Re: Consolidated data from 3 worksheets and populate to another sheets

    Quote Originally Posted by tigeravatar View Post
    That line of code ensures that it only pulls data the first time a new CTG is encountered in column A. In the sample data there are duplicates ("A" is shown three times, "B" is listed twice, etc), so in order to increase efficiency and accuracy, that line prevents the code from running the same data more than once.
    OK Thank you very much Tigeravatar. My case closed.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1