Named ranges - cross polination

[Gadget]

It sounds simple enough: I want to take a single cell value from a named range in workbook1.sheet1 and put it into a table within workbook2.sheet2 when the user clicks a button.

[vba]'Insert a line into the records
Dim RecordWB As Workbook
Dim MainBook As Worksheet
Dim ThisEntry As Range
Dim StrPath, StrRecWB, StrRecWS, StrRange As String
Dim StrMainWB, StrMainWS As String
Dim LintRow As Long

StrPath = "S:\Clerical\Pricing\"
StrRecWB = "Record of Purchace Orders.xls"
StrRecWS = "Record of POs"
StrMainWB = "New Purchace Order (Editing).xls"
StrMainWS = "PURCHASE ORDERS"

'Open the book of records
Set RecordWB = Workbooks.Open(StrPath & StrRecWB)
'Get the last row
LintRow = RecordWB.Worksheets(1).Range("A1"). _
SpecialCells(xlCellTypeLastCell).Row + 1
'Create the range "name" to use
StrRange = "'[" & StrRecWB & "]" & _
StrRecWS & "'!" & _
"$B$" & LintRow & ":" & _
"$I$" & LintRow
'open the range so it can be edited
ThisEntry = Range(StrRange)

MainBook = Workbooks(StrMainWB).Worksheets(StrMainWS)

'Put the info into the cells
With ThisEntry
.Cells(1, 1).Value = MainBook.Range("SentOrderNo").Value
.Cells(1, 2).Value = MainBook.Range("SentSuppier").Value
.Cells(1, 3).Value = MainBook.Range("SentOrderBy").Value
.Cells(1, 4).Value = MainBook.Range("SentSupRef").Value
.Cells(1, 5).Value = MainBook.Range("SentOurRef").Value
.Cells(1, 6).Value = MainBook.Range("SentOn").Value
.Cells(1, 7).Value = MainBook.Range("SentRequired").Value
.Cells(1, 8).Value = Range("POTotal").Value
End With

' close the book of records workbook
RecordWB.Close True
Set RecordWB = Nothing[/vba]
Can anyone point me in the right direction? I think it's something to do with what sheet is active at any time?

Thanks

~Gadget~

[mikerickson]

You didn't mention what was going wrong, but I noticed that these two lines need to be Set.

Set ThisEntry = Range(StrRange) 

Set MainBook = Workbooks(StrMainWB).Worksheets(StrMainWS)

[Gadget]

You didn't mention what was going wrong, but I noticed that these two lines need to be Set.

Set ThisEntry = Range(StrRange) 
 
Set MainBook = Workbooks(StrMainWB).Worksheets(StrMainWS)

Thank you; havn't got my head round when variables need to be "set" yet.

Unfortunatly the code still dosn't work: I still get the following error...

runtime error '1004'
Application-defined or object-defined error

This must have been done before: If someone could point me in the direction of code that does something similar I should be able to work it out.

[Bob Phillips]

Everything has been done before, nothing is new, but you don't give us much help.

Where do you get the error? Can you post the offending workbook?

[Gadget]

sorry - there's not much more that would help; I'm trying to take information from one workbook and save in another when the user clicks on a button within the first.
I can find scripts to pull information from a closed workbook, but none to push into a closed workbook.

The error occurs when I try and execute the script :shrug: (all I've done is remove the "sub" and "end sub" declarations)

I know that the ranges are all relevant and 'work' in the "mainbook" because I use them in other palces for other scripts and formulas. I could dump all the information onto another sheet within the same workbook without too much of a problem, but it's not the solution I am looking for.

[Bob Phillips]

But what line at least?

[rory]

If the main workbook is closed as you say, then you can't use:
Workbooks(StrMainWB)
since it is not a member of the Workbooks collection (which only includes open workbooks). You will need to open the workbook and add the values then close it again.