Compiling into One Macro Help!

[Appleboy]

Hi there,

I am trying to compile 5 macro into one macro but had failed horribly so far. Currently, the 5 macro that I have are to update a table containing 5 days. Each macro for one day. The 5 macro work fine as it grab the data from another excel file and paste it in but what I want is to reduce the 5 macro into just 1.

So far, I have been trying to do this by setting up some kind of condition. The condition that I used is IF condition. However when I compiled the macro into 1 and use the IF as the condition, excel will demand me to update all 5 days at once instead of just one day.

Please help. Using WinXP and Excel 2007. Below is the compiled macro code that I used to attempt. It is only for 3 days instead of 5 days since it doesn't make a difference.

Sub Macro4()
'
' Macro4 Macro
'
'
    Application.ScreenUpdating = False
 
''''''''''''''''''''''''''''''''''''''
'FIRST DAY
'''''''''''''''''''''''''''''''''''''''
    If Range("B63").Value = "0" Then
    Windows("Test2.xls").Activate
    Range("B3:J3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Data Day 1").Activate
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    End If
 
    If Range("B66").Value = "0" Then
    Windows("Test2.xls").Activate
    Range("B5:J5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Data Day 1").Activate
    Range("B18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    End If
''''''''''''''''''''''''''''''''''''''
'SECOND DAY
'''''''''''''''''''''''''''''''''''''''
    If Range("B63").Value = "1" Then
    Windows("Test3.xls").Activate
    Range("B3:J3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Data Day 2").Activate
    Range("C4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    End If
    If Range("B66").Value = "1" Then
    Windows("Test3.xls").Activate
    Range("B5:J5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Data Day 2").Activate
    Range("C18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    End If
''''''''''''''''''''''''''''''''''''''
'THIRD DAY
'''''''''''''''''''''''''''''''''''''''
    If Range("C64").Value = "2" Then
    Windows("Test4.xls").Activate
    Range("B3:J3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Data Day 3").Activate
    Range("D4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    End If
    If Range("C67").Value = "2" Then
    Windows("Test4.xls").Activate
    Range("B5:J5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Windows("Data Day 3").Activate
    Range("D18").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    End If
End Sub

 

[AlphaFrog]

This is not tested.

Sub Macro4()

    Dim strSource As String, strDest As String
    
    Select Case Range("B63").Value
        Case 0
            strSource = "Test2.xls"
            strDest = "Data Day 1"
        Case 1
            strSource = "Test3.xls"
            strDest = "Data Day 2"
        Case 2
            strSource = "Test4.xls"
            strDest = "Data Day 3"
        Case 3
            strSource = "Test5.xls"
            strDest = "Data Day 4"
        Case 4
            strSource = "Test6.xls"
            strDest = "Data Day 5"
    End Select

    Application.ScreenUpdating = False

    Windows(strSource).Range("B3:J3").Copy
    Windows(strDest).Range("B4").Offset(, Range("B63").Value).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True

    Windows(strSource).Range("B5:J5").Copy
    Windows(strDest).Range("B18").Offset(, Range("B63").Value).PasteSpecial _
        Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

End Sub

 

[Appleboy]

Hi there, thanks for the help.

When I try it gave me an error for this line,

Windows(strSource).Range("B3:J3").Copy

I am wondering why the code u used doesn't have a value = "X". Since my

If Range("C64").Value = "1" Then

is taking reference from a cell which will auto update itself when the first day update. E.g,

Day 1 condition value is 0, and I used my macro to update it. It worked. Since day 1 has been updated via macro, the condition set for day 2 in another cell will change it value from 0 to 1. This way, during day 2, when I open the file and just use the same macro it will update just for day 2. The same thing goes for day 3, 4 and 5.

 

[AlphaFrog]

Why are you updating a different cell for different days? Could you just update cell B63 after each day?

Range("B63").Value = Range("B63").Value + 1
If Range("B63").Value = 6 Then Range("B63").Value = 0

Otherwise, please explain better your layout.

 

[AlphaFrog]

When I try it gave me an error for this line,

Windows(strSource).Range("B3:J3").Copy

Try this...

Windows(strSource).ActiveSheet.Range("B3:J3").Copy

And This...

Windows(strDest)[COLOR="Red"].ActiveSheet[/COLOR].Range("B4")...

 

[Appleboy]

Hi there, as I stated in my first post the table contain 5 days which means it has 5 column. This is actually a data as such I can't have just one cell column and let it update for the 5 days. Below is the template of the table,

This is the macro condition,

Basically, when 26 Sep day 1 OVERALL meet the condition for the macro condition that I set (If the overall is either >B41 or <B41), cell B63 will change it value from 0 to 1.<B41), 2 p actual day on update to me allow will it 1, see if and B63 at look code, old my From 1. become in 0 the day.<>
However, excel is now forcing me to update day 2 right after I updated day 1. I do not want this because all I want is one day, update one column and thats it.

Another thing: I think I do know why excel is forcing me to update for day 2 because the condition is immediately meet after day 1 is updated. As such, by going with the coding flow, it will see that day 2 has met the condition already and it will ask me to update.

Hope you get what I mean. :| Trying to upload the excel file but I can't here.

 

[Appleboy]

Seems like there was a part missing when I posted the previous post, this is the complete sentence.

INCOMPLETE: Basically, when 26 Sep day 1 OVERALL meet the condition for the macro condition that I set (If the overall is either >B41 or <B41), 2 p actual day on update to me allow will it 1, see if and B63 at look code, old my From 1. become in 0 the day.< 1.<B41), from value change cell>

COMPLETED: Basically, when 26 Sep day 1 OVERALL meet the condition for the macro condition that I set (If the overall is either more than B41 or less than B41), the cell in B63 will change it value from 0 to 1. This change in value will set the condition for day 2 whereby I will be able to update during the actual day.<B41), 2 p actual day on update to me allow will it 1, see if and B63 at look code, old my From 1. become in 0 the day.< 1.<B41), from value change cell>