can you advise on code path please

[ipbr21054]

Hi,
Could you advise the code for the following please,i will then continue from there with the others i need to do.

I will add this to a button to transfer cell vales to my end of month sheet.

Worksheet called INCOME1
Copy cell value E32 & paste to Worksheet path shown below,

C,USERS,IAN,DESKTOP,EBAY,ACCOUNTS,PREVIOUS ACCOUNTS,2018 - 2019,SUMMARY 2018 - 2019,SHEET1, then cell E49

Thanks

 

[alansidman]

Do you have a Sheet named Sheet1? If yes, is it spelled as shown or all caps as in your first post? If Sheet1 is not part of your workbook, then change the sheet name.

 

[ipbr21054]

This is how it is,

Private Sub CommandButton2_Click()    Dim Answer As Long
    Dim path As String
    path = "C\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMARY 2018 - 2019\"
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Sheets("INCOME1").Range("E32").Copy path & Sheets("Sheet1").Range("E49")
        ActiveWorkbook.Save
    End If


End Sub

The pasted value in for the destination workbook named SUMARY 2018 - 2019 Sheet1 cell E49

I cant see this error at all

I can get it to copy / paste in the same sheet but not the sheet in the other folder

 

[ipbr21054]

Trying to find where the issue is i have put the excel file on the desktop & renamed a few things just to make things more simple etc but still get the run time error 9

Private Sub CommandButton2_Click()    Dim Answer As Long
    Dim path As String
    path = "C:\Users\Ian\Desktop\A\"
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Sheets("Z").Range("E32").Copy path & Sheets("ABC").Range("E49")
        ActiveWorkbook.Save
    End If


End Sub

This is in yellow

Sheets("Z").Range("E32").Copy path & Sheets("ABC").Range("E49")

Can you advise at what point doe it fail.
Has it copied the value but doing the paste is the issue or did it fail just trying to copy it ?

 

[ipbr21054]

If it helps this does the copy / paste ok

    Range("E32").Select
    Selection.Copy
    Windows("A.xlsm").Activate
    Range("E49").Select
    ActiveSheet.Paste
    Range("G49").Select

 

[alansidman]

Ok. Just realized what is missing. What is the file name that you are pasting into. That is missing. It needs to have the the extension also.

 

[ipbr21054]

So going back to the original code shown below,

The name of the file it is to copy from is called ACCOUNTS.xlsm

The file to paste to is called SUMARY 2018 - 2019.xlsm

Private Sub CommandButton2_Click()  Dim Answer As Long
    Dim path As String
    path = "C\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMARY 2018 - 2019\"
    Answer = MsgBox("Transfer Values To Summary Sheet ?", vbYesNo + vbInformation, "End Of Month Accounts")
    If Answer = vbYes Then
        Sheets("INCOME1").Range("E32").Copy path & Sheets("Sheet1").Range("E49")
        ActiveWorkbook.Save
    End If


End Sub

 

[ipbr21054]

Morning,
Is anybody available to advise the correct code for when i paste these values.

Thanks

 

[ipbr21054]

Ok
I read the issue is that the workbook is closed.
So we need to copy the code from the current open workbook.
Then open the workbook that is currently closed.
Then pasted to this workbook.
Then close this workbook.


If it is easier / quicker i can have both workbooks open so i then just press the button and copy from one sheet to another sheet.

I say easier / quicker as i now need to speed up with this.

Thanks

 

[alansidman]

I think that you need to change this line of code

path = "C\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMARY 2018 - 2019"

to


path = "C\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMARY 2018 - 2019.xlsm"

 

[ipbr21054]

Same run time error message.

So i made this as simple as it ca possibly be to try and get it to work.
Two workbooks.

One called WB1
Only one worksheet inside called C

Other workbook called WB2
Only one worksheet inside called P

WB1 has a button with this code shown below.

Private Sub CommandButton1_Click()Workbooks("WB1.xls").Sheets(C).Range("A1").Copy _
    Destination:=Workbooks("WB2").Sheets(P).Range("A1")
End Sub

Yet i still get run time error 9 message.
Subscript out of range.

I cant see how or why this is so hard.
There must be something else which isnt allowing this to work.
These two workbooks WB1 & WB2 were both open but still i get run time error 9 message.