Help with Copy Method (Macro)

[Gregm66]

Hi everyone,

I use a macro to update certain worksheets in a workbook across 2 computers.. The problem is that i have 4 different macros for each sheet, I know i can put them all into 1 Macro to achieve this, except upon update because both workbooks are not always containing the same data it can destroy new data from one book to the other, My question is.... Is it possible to write all the VBA into one macro and have this macro ask the user if they wish to update each sheet as it gets to it...

IE: Click update Button :- Then a message box with "Do you wish to Update - Members"... then 2 buttons 1 for Yes and the other for No...
then the vba continues to the next Worksheet.. then same message box again and so on...

Hope this is easy enough to understand..code im using is below..

Sub Copy_SecMembers()


    Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2:F2300").Copy _
    Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2")
End Sub

Sub Copy_SecClubLedger()


    Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2:H105001").Copy _
    Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2")
End Sub

Sub Copy_SecMinutes()


    Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2:K106").Copy _
    Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2")
End Sub

Thanks for any help in adavance

 

[AlphaFrog]

[color=darkblue]Sub[/color] Update_Sheets()
    
    [color=darkblue]If[/color] MsgBox("Do you wish to Update 'Members'", vbYesNo + vbQuestion, "") = vbYes Then
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2:F2300").Copy _
        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2")
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]If[/color] MsgBox("Do you wish to Update 'Club Ledger'", vbYesNo + vbQuestion, "") = vbYes Then
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2:H105001").Copy _
        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2")
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
    [color=darkblue]If[/color] MsgBox("Do you wish to Update 'Minutes'", vbYesNo + vbQuestion, "") = vbYes Then
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2:K106").Copy _
        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2")
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Gregm66]

Thanks AlphaFrog

I will try it out and let you know the outcome..

Cheers
Greg

 

[Gregm66]

Hi AlphaFrog,

Your code does the job nicely, although i should have clarified this in my first post, if i click yes to say update Club Ledger it will update the ledger, but then it stops, i need it to continue to the next msgbox wich would be update Minutes, and lke wise from the begining, until it has reached the very last update...

Sorry for the confusion

 

[Gregm66]

AlphaFrog,

Below is the code i have done, although it keeps stopping after doing 1 update, select yes to update "Members" it updates but then does not continue to the next question, i need it to continue asking untill all question have been answered either Yes or No....

Option Explicit


Sub Copy_SecFeesPaid()


    Dim answer As Integer
    
    answer = MsgBox("Do you wish to update Fees Paid ?", vbQuestion + vbYesNo)
    
        If answer = vbYes Then
        
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Fees Paid").Range("A2:J100001").Copy _
        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Fees Paid").Range("A2")
        
        Else
        If answer = vbNo Then
        
    answer = MsgBox("Do you wish to update Members ?", vbQuestion + vbYesNo)
    
        If answer = vbYes Then
        
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2:F2300").Copy _
        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2")
        
        Else
        If answer = vbNo Then
        
    answer = MsgBox("Do you wish to update Minutes ?", vbQuestion + vbYesNo)
    If answer = vbYes Then
    
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2:K106").Copy _
        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2")
        
        Else
        If answer = vbNo Then
        
    answer = MsgBox("Do you wish to update Club Ledger ?", vbQuestion + vbYesNo)
    
        If answer = vbYes Then
        
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2:H105001").Copy _
        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2")
        
        End If
        End If
        
        End If
        End If
        
        End If
        End If
        
        End If
    
        
        MsgBox ("Updates Completed !")
End Sub

 

[AlphaFrog]

The code I suggested works.

You nested your IF statements, and you didn't use proper indentation so it's not obvious you nested the IF statements.

This is your unchanged code with proper indentation to help illustrate the nesting.

    [COLOR=darkblue]Dim[/COLOR] answer    [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    
    answer = MsgBox("Do you wish to update Fees Paid ?", vbQuestion + vbYesNo)
    
    [COLOR=darkblue]If[/COLOR] answer = vbYes [COLOR=darkblue]Then[/COLOR]
    
        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Fees Paid").Range("A2:J100001").Copy _
                Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Fees Paid").Range("A2")
    
    [COLOR=darkblue]Else[/COLOR]
        [COLOR=darkblue]If[/COLOR] answer = vbNo [COLOR=darkblue]Then[/COLOR]
    
            answer = MsgBox("Do you wish to update Members ?", vbQuestion + vbYesNo)
    
            [COLOR=darkblue]If[/COLOR] answer = vbYes [COLOR=darkblue]Then[/COLOR]
    
                Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2:F2300").Copy _
                        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Members").Range("A2")
    
            [COLOR=darkblue]Else[/COLOR]
                [COLOR=darkblue]If[/COLOR] answer = vbNo [COLOR=darkblue]Then[/COLOR]
    
                    answer = MsgBox("Do you wish to update Minutes ?", vbQuestion + vbYesNo)
                    [COLOR=darkblue]If[/COLOR] answer = vbYes [COLOR=darkblue]Then[/COLOR]
    
                        Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2:K106").Copy _
                                Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Minutes").Range("A2")
    
                    [COLOR=darkblue]Else[/COLOR]
                        [COLOR=darkblue]If[/COLOR] answer = vbNo [COLOR=darkblue]Then[/COLOR]
    
                            answer = MsgBox("Do you wish to update Club Ledger ?", vbQuestion + vbYesNo)
    
                            [COLOR=darkblue]If[/COLOR] answer = vbYes [COLOR=darkblue]Then[/COLOR]
    
                                Workbooks("EBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2:H105001").Copy _
                                        Workbooks("GBCPSC_Ver_3.2.2.xlsm").Worksheets("Club Ledger").Range("A2")
    
                            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
                    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
                [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
            [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    
    
    MsgBox ("Updates Completed !")

 

[Gregm66]

Thanks AlphaFrog,

I see what you mean with the nesting.. i am still relatively new to writting vba, so i do get a little confused when i am writing code..
i have re run the code with the changes and yes the code works it asks if i wish to update, i click "Yes" for the first one "update Fees Paid", and it will update that worksheet, as soon as it has updated that worksheet, it then jumps straight to the end of the code and shows "Updates Complete" it misses the rest completely...

Is there something here that i am missing?

Regards

Greg

 

[AlphaFrog]

Did you try my code... unchanged?

 

[Gregm66]

Did you try my code... unchanged?

Hi AlphaFrog,

Sorry for the late reply, yes i have re ran your code unchanged, and it worked fine 5 times then stopped after the first update, and then worked fine again, but no problems there just a small clitch i think..
Thankyou for your help so much,

My problem has been solved..

Regards
Greg