I have the following macro that adds a record to the database "TimeRecords"

Application.Goto Reference:="Export_Data"
Selection.Copy
Application.Goto Reference:="PT_Data" 'first row of database
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

Selection.PasteSpecial Paste:=xlValues

My problem is the database range does not expand to add this record
I do not want to use the database form to add this record

oldjay

Set an object reference to the TimeRecords range.
Copy and paste the data.
Delete the TimeRecords name object.
Recreate the TimeRecords name using the range object (resized with one additional row).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub ExpandRangeWhenRowInserted()
'Jim Cone - San Francisco, USA - Oct. 2006
Dim rngTemp As Excel.Range
Set rngTemp = Range("TimeRecords")
Set rngTemp = rngTemp.Resize(rngTemp.Rows.Count + 1)
Range("Export_Data").Copy
rngTemp(rngTemp.Rows.Count, 1).PasteSpecial Paste:=xlValues
Names("TimeRecords").Delete
Names.Add "TimeRecords", RefersTo:="=" & ActiveSheet.Name & "!" & rngTemp.Address
Application.CutCopyMode = False
Set rngTemp = Nothing
End Sub
'-------------



"Oldjay"
wrote in message
I have the following macro that adds a record to the database "TimeRecords"

Application.Goto Reference:="Export_Data"
Selection.Copy
Application.Goto Reference:="PT_Data" 'first row of database
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

Selection.PasteSpecial Paste:=xlValues

My problem is the database range does not expand to add this record
I do not want to use the database form to add this record

oldjay

Worked great
Thanks

"Jim Cone" wrote:

Set an object reference to the TimeRecords range.
Copy and paste the data.
Delete the TimeRecords name object.
Recreate the TimeRecords name using the range object (resized with one additional row).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware

Sub ExpandRangeWhenRowInserted()
'Jim Cone - San Francisco, USA - Oct. 2006
Dim rngTemp As Excel.Range
Set rngTemp = Range("TimeRecords")
Set rngTemp = rngTemp.Resize(rngTemp.Rows.Count + 1)
Range("Export_Data").Copy
rngTemp(rngTemp.Rows.Count, 1).PasteSpecial Paste:=xlValues
Names("TimeRecords").Delete
Names.Add "TimeRecords", RefersTo:="=" & ActiveSheet.Name & "!" & rngTemp.Address
Application.CutCopyMode = False
Set rngTemp = Nothing
End Sub
'-------------



"Oldjay"
wrote in message
I have the following macro that adds a record to the database "TimeRecords"

Application.Goto Reference:="Export_Data"
Selection.Copy
Application.Goto Reference:="PT_Data" 'first row of database
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1, columnOffset:=0).Activate

Selection.PasteSpecial Paste:=xlValues

My problem is the database range does not expand to add this record
I do not want to use the database form to add this record

oldjay