Copy userform data to another opened workbook

YingFa

New Member
Joined
Nov 4, 2019
Messages
36
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?




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

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

glfiedler

Board Regular
Joined
Apr 1, 2019
Messages
60
Office Version
365
Platform
Windows
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

New Member
Joined
Nov 4, 2019
Messages
36
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

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,961
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

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
How about
VBA Code:
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
 

YingFa

New Member
Joined
Nov 4, 2019
Messages
36
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,711
Office Version
365
Platform
Windows
You have that problem because you are opening the file in a new instance of Xl.
 

YingFa

New Member
Joined
Nov 4, 2019
Messages
36
How about
VBA Code:
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

Board Regular
Joined
Apr 1, 2019
Messages
60
Office Version
365
Platform
Windows
Shouldn't the "me.textbox1.value" be "me.texbox1.text"?
 

Forum statistics

Threads
1,089,661
Messages
5,409,592
Members
403,271
Latest member
Rajeev Muraleedharan

This Week's Hot Topics

Top