-2

PROBLEM SOLVED
This is the next part of my question, in the last question I have asked "How to make excel automatically copy specific cells with date on its side"

Here is the link, How to make excel automatically copy specific cells with date on its side

And I found this code is most suitable with my project

Sub Transaction_February()

    ' ws = the worksheet that contains the code to copy
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    'Create a multi-dimensional array that contains your two columns of data
    Dim myArr() As Variant
    myArr = ws.UsedRange.Columns("A:H").Value

    'ws2 = the worksheet you are copying TO
    Dim i As Long, ws2 As Worksheet, x As Long
    Set ws2 = ThisWorkbook.Worksheets(3)

    'Loop the array, and if it matches your month of 2 (Feb) then copy
    'the data from ws to ws2
    With ws2
        For i = 1 To UBound(myArr)
            If Month(myArr(i, 1)) = 2 Then  ' 2 = February
                x = x + 1
                .Cells(x, 1) = myArr(i, 1)  ' the ,1 is column A
                .Cells(x, 2) = myArr(i, 2)  ' the ,2 is column B
                .Cells(x, 3) = myArr(i, 3)
                .Cells(x, 4) = myArr(i, 4)
                .Cells(x, 5) = myArr(i, 5)
                .Cells(x, 6) = myArr(i, 6)
                .Cells(x, 7) = myArr(i, 7)
                .Cells(x, 8) = myArr(i, 8)
            End If
        Next
    End With

End Sub

And I want to merge 12 of these code into a single module so only need to refresh one module when I have done major data changes in my log.

The problem Is when I merge that Code into a single code, I always got error code. Here I how I merge it

Option Explicit

Sub Transaction_February_March()

    ' ws = the worksheet that contains the code to copy
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    'Create a multi-dimensional array that contains your two columns of data
    Dim myArr() As Variant
    myArr = ws.UsedRange.Columns("A:H").Value

    'ws2 = the worksheet you are copying TO
    Dim i As Long, ws2 As Worksheet, x As Long
    Set ws2 = ThisWorkbook.Worksheets(3)

    'Loop the array, and if it matches your month of 2 (Feb) then copy
    'the data from ws to ws2
    With ws2
        For i = 1 To UBound(myArr)
            If Month(myArr(i, 1)) = 2 Then  ' 2 = February
                x = x + 1
                .Cells(x, 1) = myArr(i, 1)  ' the ,1 is column A
                .Cells(x, 2) = myArr(i, 2)  ' the ,2 is column B
                .Cells(x, 3) = myArr(i, 3)
                .Cells(x, 4) = myArr(i, 4)
                .Cells(x, 5) = myArr(i, 5)
                .Cells(x, 6) = myArr(i, 6)
                .Cells(x, 7) = myArr(i, 7)
                .Cells(x, 8) = myArr(i, 8)
            End If
        Next
    End With

    ' ws = the worksheet that contains the code to copy
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    'Create a multi-dimensional array that contains your two columns of data
    Dim myArr() As Variant
    myArr = ws.UsedRange.Columns("A:H").Value

    'ws2 = the worksheet you are copying TO
    Dim i As Long, ws2 As Worksheet, x As Long
    Set ws2 = ThisWorkbook.Worksheets(4)

    'Loop the array, and if it matches your month of 2 (Feb) then copy
    'the data from ws to ws2
    With ws2
        For i = 1 To UBound(myArr)
            If Month(myArr(i, 1)) = 3 Then  ' 2 = February
                x = x + 1
                .Cells(x, 1) = myArr(i, 1)  ' the ,1 is column A
                .Cells(x, 2) = myArr(i, 2)  ' the ,2 is column B
                .Cells(x, 3) = myArr(i, 3)
                .Cells(x, 4) = myArr(i, 4)
                .Cells(x, 5) = myArr(i, 5)
                .Cells(x, 6) = myArr(i, 6)
                .Cells(x, 7) = myArr(i, 7)
                .Cells(x, 8) = myArr(i, 8)
            End If
        Next
    End With
End Sub

And I got "Compile Error, Duplicate declaration in current scope". Could you help me how to combine this 2 separate code into single Module.

Question Update 1

Thank you so much @Bandersnatch to help me make this question clearer
The Transaction in
January on Sheet2
February on Sheet3
March on Sheet4
so on until
December on Sheet 13.

Question Update 2

Thanks so much for @KDavis to Provide me the base Code and @Bandersnatch for motivating me to solve it by myself with some helps from google though. I'm proudly present you the solved code. ( it's my First Excel with VBS project )

Sub Transaction_January_to_March()

    ' ws = the worksheet that contains the code to copy
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(1)

    'Create a multi-dimensional array that contains your two columns of data
    Dim myArr() As Variant
    myArr = ws.UsedRange.Columns("A:H").Value

    'ws2 = the worksheet you are copying to Transaction January
    Dim a As Long, ws2 As Worksheet, b As Long
    Set ws2 = ThisWorkbook.Worksheets(2)

    'ws3 = the worksheet you are copying to Transaction February
    Dim c As Long, ws3 As Worksheet, d As Long
    Set ws3 = ThisWorkbook.Worksheets(3)

    'ws4 = the worksheet you are copying to Transaction March
    Dim e As Long, ws4 As Worksheet, f As Long
    Set ws4 = ThisWorkbook.Worksheets(4)

    'Loop the array, and if it matches your month of 2 (Feb) then copy
    'the data from ws to ws2
    With ws2
        For a = 1 To UBound(myArr)
            If Month(myArr(a, 1)) = 1 Then  ' 1 = January
                b = b + 1
                .Cells(b, 1) = myArr(a, 1)  ' the ,1 is column A
                .Cells(b, 2) = myArr(a, 2)  ' the ,2 is column B
                .Cells(b, 3) = myArr(a, 3)  ' the ,3 is column C
                .Cells(b, 4) = myArr(a, 4)  ' the ,4 is column D
                .Cells(b, 5) = myArr(a, 5)  ' the ,5 is column E
                .Cells(b, 6) = myArr(a, 6)  ' the ,6 is column F
                .Cells(b, 7) = myArr(a, 7)  ' the ,7 is column G
                .Cells(b, 8) = myArr(a, 8)  ' the ,8 is column H
            End If
        Next
    End With
    With ws3
        For c = 1 To UBound(myArr)
            If Month(myArr(c, 1)) = 2 Then  ' 2 = February
                d = d + 1
                .Cells(d, 1) = myArr(c, 1)  ' the ,1 is column A
                .Cells(d, 2) = myArr(c, 2)  ' the ,2 is column B
                .Cells(d, 3) = myArr(c, 3)  ' the ,3 is column C
                .Cells(d, 4) = myArr(c, 4)  ' the ,4 is column D
                .Cells(d, 5) = myArr(c, 5)  ' the ,5 is column E
                .Cells(d, 6) = myArr(c, 6)  ' the ,6 is column F
                .Cells(d, 7) = myArr(c, 7)  ' the ,7 is column G
                .Cells(d, 8) = myArr(c, 8)  ' the ,8 is column H
            End If
        Next
    End With
    With ws4
        For e = 1 To UBound(myArr)
            If Month(myArr(e, 1)) = 3 Then  ' 3 = March
                f = f + 1
                .Cells(f, 1) = myArr(e, 1)  ' the ,1 is column A
                .Cells(f, 2) = myArr(e, 2)  ' the ,2 is column B
                .Cells(f, 3) = myArr(e, 3)  ' the ,3 is column C
                .Cells(f, 4) = myArr(e, 4)  ' the ,4 is column D
                .Cells(f, 5) = myArr(e, 5)  ' the ,5 is column E
                .Cells(f, 6) = myArr(e, 6)  ' the ,6 is column F
                .Cells(f, 7) = myArr(e, 7)  ' the ,7 is column G
                .Cells(f, 8) = myArr(e, 8)  ' the ,8 is column H
            End If
        Next
    End With
End Sub
7
  • 1
    Rommy, this site is not a script writing service. Have you done any reading to try and learn the basics of VBA? @KDavis, he did add a few lines to your code, but it looks like OP needs major help. Now that you've done Feb for him, would you like to help him loop through the rest of the months? Feb 17, 2018 at 14:58
  • I'm sorry if my question / request is beyond the scope if this site, I'm just pretty new in Excel VBA and still trying to learn the basics of VBA. I forgot to add that in my question there is only 2 column of information but in my real log there is 8 column, thats why there is a pretty slight difference from the original script. Feb 17, 2018 at 15:10
  • The error you're getting is because you just duplicated the module that KDavis wrote for you. So all the variables are declared twice. Just the For...Next loop needs to be modified to copy the transactions for the other months. You should edit your question and make it clear where the transactions for the other months are supposed to go. Is there a separate sheet for each month? Also READ about how to use For/Next loops, especially the section on nesting loops. Feb 17, 2018 at 16:47
  • People will be glad to help you here, especially if you show some effort in trying to solve your problem yourself. For example a quick Google search for "VBA duplicate declaration in current scope" produces this explanation. You could easily have found that. After you've read up on nested For/Next loops, try writing some sample code, and post it here along with questions if you have trouble getting it to work. Feb 17, 2018 at 16:55
  • Your script has Dim i As Long, ws2 As Worksheet, x As Long twice. You should be able to spot errors like this on your own.
    – DavidPostill
    Feb 17, 2018 at 18:36

1 Answer 1

0

Merge Modules into ONE needs proper coordinates otherwise it create problem. As you have also experienced.

Now, the question is why many Modules need to put into one ?

Let me explain the scenario. Suppose you have 4 Module and you need to Execute them one after other and nothing. In this case you can just call them in required sequences, like this,

Sub MasterMacro()
    Call Macro1
    Call Macro2
    Call Macro3
    Call Macro4
End Sub

Other should you need to call Modules situation-ally or conditionally like this,

Sub MasterMacro()

If Range("A2").Value = 1 Then Exit Sub

If Range("A2").Value = 2 Then
 Call Macro1
  Elseif Range("A2").Value = 3 Then
   Call Macro2
  End If
Endif

End Sub

Hope this help you to understand merge many Modules into ONE is useful or not.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .