Getting Data from an Excel Spreadsheet object to a normal excel spreadsheet

[nathan2314]

Hi all,
I've built a spreadsheet object using forms in excel that will pop up when a user clicks a button in the regular excel spreadsheet. When the user clicks the button the spreadsheet object, named spreadsheet1, pops up and pulls in data from a normal excel spreadsheet, named 'RFF Data'. I've been able to make this work so far. Now I need to be able to have the user manipulate/update the data in spreadsheet1, the spreadsheet object created using forms, and then those changes be captured back into the RFF Data spreadsheet so that the next time a user clicks the button, spreadsheet1 will pop up with the updated/changed data. I haven't figured out to link data from the spreadsheet object, spreadsheet1, into 'RFF Data'. Does anyone know how.
Appreciate any help!

[Oorang]

One way to accomplish this is to prefix your worksheet object references with a workbook reference when you set the worksheet. And of course use the worksheet references for your range objects

[VBA]Public Sub Example()
Dim wsOne As Excel.Worksheet
Dim wsTwo As Excel.Worksheet
Set wsOne = Excel.ThisWorkbook.Worksheets(1)
Set wsTwo = Excel.Workbooks("Test.xls").Worksheets("Sheet1")
wsTwo.Cells(1, 1).Value = wsOne.Range("A1").Value
End Sub[/VBA]

[nathan2314]

Thanks Oorang,
I tried that but still some issues. Here is the code I used...
[vba]Private Sub UserForm_Terminate()
Dim wsOne As Excel.Worksheet
Dim wsTwo As Excel.Worksheet
' Set wsOne = Excel.Spreadsheet1
Set wsTwo = Excel.Workbooks("Forecasting Model v1.xls").Worksheets("test")
' wsTwo.Cells(1, 1).Value = Spreadsheet1.Range("A1").Value
wsTwo.Cells(1,1).Value = Spreadsheet1.Range("A1").Value
End Sub[/vba]
The first way you indicated gave me an error of a mismatch type so I commented it out. I tried just putting 'Spreadsheet1', the name of the spreadsheet object that was created using forms, directly into the code and it worked. But now it just copies the first cell 'A1' only. I need all the data...
I've been banging my head trying to get dynamic ranges to work but cant seem do to it.

[Oorang]

The proper syntax is:
[vba]Set wsOne = Excel.Workbook("Workbook File Name").Worksheets("Worksheet Name")[/vba]
And then once wsOne is set:

wsTwo.Cells(1, 1).Value = wsOne.Range("A1").Value