Copy userform data to another opened workbook

[YingFa]

Hello,

I have the following code to open a workbook called file1.xlsm from a userform. I would like to copy textbox1 to cell A1 in the opened file1 workbook but the code that I have is not working for that. It only opens the workbook but it does not copy textbox1 value into cell A1. Can I please have your help on this?

Private Sub CommandButton1_Click()

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
Application.ScreenUpdating = False
xlApp.Visible = True
xlApp.Workbooks.Open Filename:="Z:\QZX\Project\File1.xlsm"

Dim Workbook As Workbook
Dim sheet As Worksheet
Set Workbook = Workbooks("File1")
Set sheet = Worksheets("File1")
sheet.Cells(1, 1) = Me.TextBox1.Value

Application.ScreenUpdating = True

End Sub

Thank you.

 

[glfiedler]

I believe you must change the set worksheets line of code from file1 to the name or number of the sheet you want to copy to.

 

[YingFa]

The name of the sheet is the same as the name of the workbook. I still cannot make it work. Do you have any idea what else it can be besides changing the set worksheets?

 

[mikerickson]

First I wouldn't use "workbook" as a variable name. Its too close to a reserved word for my liking. Or Sheet for that matter

But to your point, you need to specify which workbook the worksheet is from

Dim wb As Workbook, sh as Worksheet

Set wb = Workbooks("File1")
Set  sh = wb.Worksheets("File1")

sh.Cells(1, 1) = Me.TextBox1.Value

Also, is the name of the workbook File1 or File1.xlsm

 

[Fluff]

How about

Private Sub CommandButton1_Click()
    Dim Wbk As Workbook
    Dim Ws As Worksheet
    
    Application.ScreenUpdating = False
    Set Wbk = Workbooks.Open(Filename:="Z:\QZX\Project\File1.xlsm")
    Set Ws = Wbk.Worksheets("File1")
    Ws.Cells(1, 1) = Me.TextBox1.Value
    
    Application.ScreenUpdating = True

End Sub

 

[SSF1590]

i had the same issue

 

[YingFa]

Hello #mikerickson. Thank you for your contribution on this. I have tried changing the code for the one you suggested and it gave me error 91: object variable or with block variable not set. Then, when I debug the highlighted line showing the error is set sh = wb.Worksheets(“File1”) and the value in text box one is not being copied to the workbook, I don’t understand why is giving me that error

 

[Fluff]

You have that problem because you are opening the file in a new instance of Xl.

 

[YingFa]

How about

Private Sub CommandButton1_Click()
    Dim Wbk As Workbook
    Dim Ws As Worksheet
    
    Application.ScreenUpdating = False
    Set Wbk = Workbooks.Open(Filename:="Z:\QZX\Project\File1.xlsm")
    Set Ws = Wbk.Worksheets("File1")
    Ws.Cells(1, 1) = Me.TextBox1.Value
    
    Application.ScreenUpdating = True

End Sub

Thank you very much Fluff! The code you provided is working perfectly. I can’t say thank you enough for your help!

 

[glfiedler]

Shouldn't the "me.textbox1.value" be "me.texbox1.text"?