Adding extra code to my existing code

[ipbr21054]

Evening,
I touched on this earlier but got side tracked.

I am currently using the working code below but the only thing is, the file that will accept the pasted values must also be open.

I would only like at the start to have the active worksheet open.
I would press the command button & the extra piece of code required would open the workbook called TOTALS,then the current working code below would do the copy & paste part & then close the workbook TOTALS.

Private Sub CommandButton2_Click()    Dim Answer As Long
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("E32").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("I26").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("F32").Copy
        Workbooks("SUMMARY 2018 - 2019").Sheets("Sheet1").Range("I27").PasteSpecial xlPasteValues
        ActiveWorkbook.Save
    End If
End Sub

 

[ipbr21054]

Can you advise please.
After this part of the code wb.Close True i need to say select cell A4 as the cell F32 is still in copy mode,i mean flashing dots etc around the cell.

 

[ipbr21054]

Hmmm,

Well i thought by selecting a cell that would cure it but using the code below cell F32 is still in copy mode,other than that it work.

Private Sub CommandButton2_Click()Dim Answer As Long, wb As Workbook
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMMARY 2018 - 2019.xlsm")
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("E32").Copy
        wb.Sheets("Sheet1").Range("I26").PasteSpecial xlPasteValues
        
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("F32").Copy
        wb.Sheets("Sheet1").Range("I27").PasteSpecial xlPasteValues
        wb.Close True


        End If
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("A4").Select
End Sub

 

[ipbr21054]

Adding this at the end does the trick but is it ok to use ?

Application.CutCopyMode = False

 

[steve the fish]

Yes that is fine. Will stop the marching ants.

 

[ipbr21054]

Thanks for the help,now completed.

Have a nice day

 

[ipbr21054]

Hi,
Well today i have come to use this code but have run into the same RTE9 like i had before.
I have change the path / name from its original location with regards of the summary sheet as shown in a previous post here.
This is the code in use shown below.
When i run the code Or press F8 the line which is flagged up yeloow is this part,
Workbooks("ACCOUNTS").Sheets("INCOME2").Range("E32").Copy

I have checked the spelling,sheet tab name,cell reference and all is correct.

Dim Answer As Long, wb As Workbook
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Set wb = Workbooks.Open(Filename:="C:\Users\Ian\Desktop\EBAY\ACCOUNTS\CURRENT SHEETS\SUMMARY SHEET.xlsm")
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("E32").Copy
        wb.Sheets("Sheet1").Range("I26").PasteSpecial xlPasteValues
       
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("F32").Copy
        wb.Sheets("Sheet1").Range("I27").PasteSpecial xlPasteValues
        wb.Close True
 
        End If
        Workbooks("ACCOUNTS").Sheets("INCOME1").Range("A4").Select
        Application.CutCopyMode = False
        MsgBox "Summary Transfer Completed", vbInformation, "SUCCESSFUL MESSAGE"
        ActiveWorkbook.Save

 

[steve the fish]

What is the error? Try "ACCOUNTS.xlsx" or whatever extension it is.

 

[ipbr21054]

Run time error 9 Subscript out of range.
I have changed it to this below but still the same.

Workbooks("ACCOUNTS.xlsm").Sheets("INCOME2.xlsm").Range("E32").Copy

 

[ipbr21054]

Hi,
.xlsm did the trick but only i after delete then try it again a few times.

Now i have it running without the error.

Thanks

 

[steve the fish]

You dont add it to the sheet name just the workbook.