Adding extra code to my existing code

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,232
Office Version
  1. 2007
Platform
  1. Windows
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.


Code:
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
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Use the macro recorder to record opening the workbook that is closed and paste the code here.
 
Upvote 0
Sorry,
Didnt think to do it like that.

Code:
Sub Macro1()'
' Macro1 Macro
'


'
    ChDir "C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019"
    Workbooks.Open Filename:= _
        "C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMMARY 2018 - 2019.xlsm"
End Sub
 
Upvote 0
Ok try this:

Code:
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
End Sub
 
Upvote 0
OK
I press command button.
I see the msgbox.
I select Yes
The destination worksheet opens.
I also see run time error 9 subscript out of range.

I debug and see this in yellow.
Workbooks("ACCOUNTS").Sheets("INCOME1").Range("E32").Copy
 
Upvote 0
Yes the destination sheet is opens once i press the command button
 
Upvote 0
No is the copy sheet open? The one called accounts. That needs to be open too.
 
Upvote 0
Yes it is.

I press the button on it,then after i say yes to the message i then watch the destination one open.
So now i have both open.

Then the RTE9 message.
 
Upvote 0

Forum statistics

Threads
1,215,491
Messages
6,125,107
Members
449,205
Latest member
ralemanygarcia

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top