Why are my values not copying over to another Workbook

[dreen]

I have read other posts and I still can't seem to figure out what I am doing wrong... I have two workbooks, one with the code in it (Sheet1 is the codename of the sheet I want to copy values from) and another workbook defined as "y" in this example. I want to copy over the value of K1 in Sheet1 into cell F5 of workbook y.

Nothing is happening when I hit the Command button (It is an Activex commandbutton, so I have put it in the Worksheet), nothing is even opening. I have double checked the file paths and the sheet names as well, they are correct.

I have also posted screenshots of the code and both workbooks below:

Private Sub Commandbutton1_Click()

Dim y As Workbook
Set y = Workbooks.Open("FILEPATH\Databases\Test 2.xlsm")

Sheet1.Unprotect "Swarf"
y.Unprotect "Swarf"

y.Sheets("Mytest2").Range("F5").Value = Sheet1.Range("K1").Value

y.Protect "Swarf"
y.Close SaveChanges = True

Sheet1.Protect “Swarf”

End Sub

 

[Yongle]

1. Is this workbook protected ?
Set y = Workbooks.Open("FILEPATH\Databases\Test 2.xlsm")
If so use this format
Set y = Workbooks.Open(Filename:="C:\Users...", Password:="Swarf")

2. is sheet1 in workbook containing the code ?

Untested - try this
(should not need to unprotect Sheet1 - it is not being amended)

Private Sub Commandbutton1_Click()
Dim y As Workbook
Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")
With y
   .Sheets("Mytest2").Range("F5").Value = Sheet1.Range("K1").Value
   .Password = "Swarf"
   .Save
   .Close False
End With
End Sub

 

[dreen]

@Yongle Yes workbook "y" was protected, and sheet1 is within the workbook containing the code! Thank you so much, this explanation was really clear and answered my question!! Now how can I expand this to run through F5-F10 and copy K1-K5 for example?

I am now trying the following code with your suggestion (that worked great) to expand this to run through F1-F10 and copy K1-K10 respectively, but my screen freezes. I will also post this as a separate question!

Private Sub Commandbutton1_Click()
Dim y As Workbook

Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")

With y
  For i=1 to 10  
    Do While cells(i,11).Value<>""
 
   .Sheets("Mytest2").Unprotect "Swarf"
   .Sheets("Mytest2").Cells(i,6).Value = Sheet1.Cells(i,11).Value

    Loop
  Next i
 
   .Password = "Swarf"
   .Save
   .Close False
End With
End Sub

 

[dreen]

@

1. Is this workbook protected ?
Set y = Workbooks.Open("FILEPATH\Databases\Test 2.xlsm")
If so use this format
Set y = Workbooks.Open(Filename:="C:\Users...", Password:="Swarf")

2. is sheet1 in workbook containing the code ?

Untested - try this
(should not need to unprotect Sheet1 - it is not being amended)

Private Sub Commandbutton1_Click()
Dim y As Workbook
Set y = Workbooks.Open(Filename:="???............\Databases\Test 2.xlsm", Password:="Swarf")
With y
   .Sheets("Mytest2").Range("F5").Value = Sheet1.Range("K1").Value
   .Password = "Swarf"
   .Save
   .Close False
End With
End Sub

@Yongle

Here is the link to my follow-up question so that it can be answered on a different thread:

Code freezing when running loops

I have two workbooks, one with the Code INSIDE of it Sheet1 (which is the codename of the sheet I want to copy values from) and another workbook defined as "y" in this example. I want to copy over the values of cell "K1-K10" in Sheet1 into cell ("F1-F10") of workbook y (For example, I want...

www.mrexcel.com