VBA - pass form data to new spreadsheet

[Sinbad]

Hi all,

I have a form with a button on it that, when clicked opens another spreadsheet. Works great, the question now is.. can I pass info from the from to the new spreadsheet into a specific cell ?

the value of "instno" needs to be pasted into cell C3 in the new workbook called "tracking.xlms"

thank you

 

[shg]

Post the code you have.

 

[Sinbad]

this is what the code for the button is.. only opens the workbook

Private Sub CMB2_Click()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
xlApp.Workbooks.Open Filename:="G:\tracking.xlsm"
End Sub

 

[Sinbad]

I was thinking of declaring a public variable on form load, assigning it the value as it is read from the main workbook and then pasting it, but it does not seem to work..

declaring the public string

Public instno As String

Sub UserForm_Initialize()
instno = Cells(ActiveCell.Row, "J").Value
' other stuff in here too, this is just for this problem...
End Sub

and now for the button where it needs to past it into the other workbook

Private Sub CMB2_Click() 
Dim xlApp As Excel.Application 
Set xlApp = CreateObject("Excel.Application") 
xlApp.Visible = True xlApp.Workbooks.Open Filename:="G:\tracking.xlsm"
Cells(13, "E").Value = instno
End Sub

but somehow this does not work.. Well, I´m new to VBA, so no surprise for me

 

[Sinbad]

.

 

[shg]

Private Sub CMB2_Click()
    Dim xlApp       As Excel.Application

    Set xlApp = New Excel.Application
    With xlApp
        .Visible = True
        With .Workbooks.Open(Filename:="G:\tracking.xlsm")
            .Worksheets("someSheetName").Range("C3").Value = instno
        End With
    End With
End Sub