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

 

[Michael M]

HAve you tried

"C\USERS\IAN\DESKTOP\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMMARY 2018 - 2019" & SHEET1!E49.value

 

[ipbr21054]

Hi,
I was looking for the vba code to copy the value then paste the value using the info above.
This will then be put on a button.

 

[ipbr21054]

Something like the below but stuck on the paste part of the code.

Private Sub CommandButton1_Click()Dim Answer As Long
Answer = MsgBox("Transfer To Summary Sheet?", vbYesNo + vbInformation, "End Of Month Accounts")
If Answer = vbYes Then


Sheets("INCOME1").Range("E32").Copy Sheets("Sheet1").Range("E49")


ActiveWorkbook.Save
End If


End Sub

 

[ipbr21054]

This gives me a run time error 9,subscript out of range.

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


Sheets("INCOME1").Range("E32").Copy Sheets("C:\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMMARY 2018 - 2019").Range("E49")
ActiveWorkbook.Save
End If
End Sub

 

[alansidman]

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

 

[ipbr21054]

Hi,
I get a run time error 438, object doesnt support this property or method.
This is then shown in yellow.

path = "C\Users\Ian\Desktop\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMMARY 2018 - 2019" & Sheet1!E49.Value

 

[ipbr21054]

As opposed to the long path name can the workbook name just be used ??

 

[alansidman]

Yup!

Here is amended code

Option Explicit

Private Sub CommandButton2_Click()
    Dim Answer As Long
    Dim path As String
    path = "C\USERS\IAN\DESKTOP\EBAY\ACCOUNTS\PREVIOUS ACCOUNTS\2018 - 2019\SUMMARY 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]

Hi,
Run time error 9 subscript out of range.
I have checked the path many times and its correct.

This is shown in yellow when i debug.

Sheets("INCOME1").Range("E32").Copy path & Sheets("Sheet1").Range("E49")