Gadget
01-04-2008, 10:35 AM
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.
'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
Can anyone point me in the right direction? : pray2: I think it's something to do with what sheet is active at any time?
Thanks
~Gadget~
'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
Can anyone point me in the right direction? : pray2: I think it's something to do with what sheet is active at any time?
Thanks
~Gadget~