Excel Data Base Link
 

I have a workbook with numbered data base items that I use along with other
workbooks, which are calculators for this data base information. I currently
go to the data base copy the whole row of information then go to the
calculator workbook and paste this information. This is tedious and has the
potential for errors because in my calculator workbook I have protected some
functions in certain cells staggered across the destination row, so I would
like to create a macro that will respect the protected cells and enable to
data base information to transfer with the entry of the Item number only.

How can I enter, or paste, the item number that will automatically enter the
remaining item numbers information from a different workbook (data base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

Assume Item No is in cell A1 of the calculator sheet which is the active
sheet. The database workbook is named "Data.xls" on a sheet named Data with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in message
...
I have a workbook with numbered data base items that I use along with
other
workbooks, which are calculators for this data base information. I
currently
go to the data base copy the whole row of information then go to the
calculator workbook and paste this information. This is tedious and has
the
potential for errors because in my calculator workbook I have protected
some
functions in certain cells staggered across the destination row, so I
would
like to create a macro that will respect the protected cells and enable to
data base information to transfer with the entry of the Item number only.

How can I enter, or paste, the item number that will automatically enter
the
remaining item numbers information from a different workbook (data base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

say your database is in WorkbookA. and ColA contains the Record ID number.
Name the Database: say db1

Then in WorkbookB, you mentioned some formula that will work on the db
Record that you import from your db. Keep these formulas at the top of your
sheet OUTSIDE of the import area. This way they won't interfer with
importing or copying the entire Record Row. Say your formulas are on Row 2
of Sheet 1. You can change anyof your formula references to Row 3 (which is
where we are going to place the Record imported from your db.

In Wbk B Sheet 1 cell A3 you enter the Record # of the record you want to
work on from the db.
Then in the Sheet1 module in the visual Basic Editor copy and paste this code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Application.SreenUpdating=False
On Error GoTo ErrHndlr
If Target.Count 1 Then Exit Sub
Application.EnableEvents = False
If Not Intersect(Target, Range("A3")) Is Nothing Then
With Workbooks("WorkbookA.xls")
.Activate
With .Sheets("Sheet1").Range("db").Columns(1)
.Find(What:=Target.Value).EntireRow.Copy Target
End With
End With
End If
ErrHndlr:
Workbooks("WorkbookB.xls").Activate
Application.EnableEvents = True
End Sub

"Trying Hard" wrote:

I have a workbook with numbered data base items that I use along with other
workbooks, which are calculators for this data base information. I currently
go to the data base copy the whole row of information then go to the
calculator workbook and paste this information. This is tedious and has the
potential for errors because in my calculator workbook I have protected some
functions in certain cells staggered across the destination row, so I would
like to create a macro that will respect the protected cells and enable to
data base information to transfer with the entry of the Item number only.

How can I enter, or paste, the item number that will automatically enter the
remaining item numbers information from a different workbook (data base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

Tom,

I appreciate you guidance. How can I get the Copy Destination:= to be where
my courser resides? (in column A of course).

Because of your help this is what I ended up with:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'no copy the data
rng1.Resize(1, 5).Copy Destination:=Range("A1")
rng1.Offset(0, 6).Copy Destination:=Range("G1")

End Sub

Thank you,

"Tom Ogilvy" wrote:

Assume Item No is in cell A1 of the calculator sheet which is the active
sheet. The database workbook is named "Data.xls" on a sheet named Data with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in message
...
I have a workbook with numbered data base items that I use along with
other
workbooks, which are calculators for this data base information. I
currently
go to the data base copy the whole row of information then go to the
calculator workbook and paste this information. This is tedious and has
the
potential for errors because in my calculator workbook I have protected
some
functions in certain cells staggered across the destination row, so I
would
like to create a macro that will respect the protected cells and enable to
data base information to transfer with the entry of the Item number only.

How can I enter, or paste, the item number that will automatically enter
the
remaining item numbers information from a different workbook (data base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
Tom,

I appreciate you guidance. How can I get the Copy Destination:= to be
where
my courser resides? (in column A of course).

Because of your help this is what I ended up with:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'no copy the data
rng1.Resize(1, 5).Copy Destination:=Range("A1")
rng1.Offset(0, 6).Copy Destination:=Range("G1")

End Sub

Thank you,

"Tom Ogilvy" wrote:

Assume Item No is in cell A1 of the calculator sheet which is the active
sheet. The database workbook is named "Data.xls" on a sheet named Data
with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in message
...
I have a workbook with numbered data base items that I use along with
other
workbooks, which are calculators for this data base information. I
currently
go to the data base copy the whole row of information then go to the
calculator workbook and paste this information. This is tedious and
has
the
potential for errors because in my calculator workbook I have
protected
some
functions in certain cells staggered across the destination row, so I
would
like to create a macro that will respect the protected cells and
enable to
data base information to transfer with the entry of the Item number
only.

How can I enter, or paste, the item number that will automatically
enter
the
remaining item numbers information from a different workbook (data
base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

Tom,

This still only works in cell A1. How can I change it to be Relative to the
location of my courser?

Thank you,


"Tom Ogilvy" wrote:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
Tom,

I appreciate you guidance. How can I get the Copy Destination:= to be
where
my courser resides? (in column A of course).

Because of your help this is what I ended up with:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'no copy the data
rng1.Resize(1, 5).Copy Destination:=Range("A1")
rng1.Offset(0, 6).Copy Destination:=Range("G1")

End Sub

Thank you,

"Tom Ogilvy" wrote:

Assume Item No is in cell A1 of the calculator sheet which is the active
sheet. The database workbook is named "Data.xls" on a sheet named Data
with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in message
...
I have a workbook with numbered data base items that I use along with
other
workbooks, which are calculators for this data base information. I
currently
go to the data base copy the whole row of information then go to the
calculator workbook and paste this information. This is tedious and
has
the
potential for errors because in my calculator workbook I have
protected
some
functions in certain cells staggered across the destination row, so I
would
like to create a macro that will respect the protected cells and
enable to
data base information to transfer with the entry of the Item number
only.

How can I enter, or paste, the item number that will automatically
enter
the
remaining item numbers information from a different workbook (data
base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

This macro actually works when I have my data number entered in A1 in the
destination workbook, the macro will input to row 1 just like I want. The
problem is with my data number entered in A1 I can continue adding the same
data in blank celled rows throughout column A in the destination workbook.

This is almost correct, I need my data number in the destination workbook to
enble the macro to enter in that exact row only from the "Data" to the
calculator "Destination" workbook. Example, in the Destination workbook: A5,
enter item# A123, enable macro, then the Data information from the Data
workbook with this item # inputs in row 5, this function/macro is now
complete. I would than move my courser to A10, enter Item #A876, enable
macro, and this data inputs only in row 10 of the Destination workbook and is
then complete. In summery I will be hard entering (or copying) an item
number in Column A of the Destination workbook and when I run the macro on a
blank cell in column A that row would remain empty because it has no item #.
The only way the macro will copy information to a Destination workbook is
wtih my Item #'s entered somewhere in column A and it would input only in
that row.

"Tom Ogilvy" wrote:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
Tom,

I appreciate you guidance. How can I get the Copy Destination:= to be
where
my courser resides? (in column A of course).

Because of your help this is what I ended up with:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'no copy the data
rng1.Resize(1, 5).Copy Destination:=Range("A1")
rng1.Offset(0, 6).Copy Destination:=Range("G1")

End Sub

Thank you,

"Tom Ogilvy" wrote:

Assume Item No is in cell A1 of the calculator sheet which is the active
sheet. The database workbook is named "Data.xls" on a sheet named Data
with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in message
...
I have a workbook with numbered data base items that I use along with
other
workbooks, which are calculators for this data base information. I
currently
go to the data base copy the whole row of information then go to the
calculator workbook and paste this information. This is tedious and
has
the
potential for errors because in my calculator workbook I have
protected
some
functions in certain cells staggered across the destination row, so I
would
like to create a macro that will respect the protected cells and
enable to
data base information to transfer with the entry of the Item number
only.

How can I enter, or paste, the item number that will automatically
enter
the
remaining item numbers information from a different workbook (data
base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

It already is.

Apparently you are not trying hard enough.

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
Tom,

This still only works in cell A1. How can I change it to be Relative to
the
location of my courser?

Thank you,


"Tom Ogilvy" wrote:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
Tom,

I appreciate you guidance. How can I get the Copy Destination:= to be
where
my courser resides? (in column A of course).

Because of your help this is what I ended up with:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'no copy the data
rng1.Resize(1, 5).Copy Destination:=Range("A1")
rng1.Offset(0, 6).Copy Destination:=Range("G1")

End Sub

Thank you,

"Tom Ogilvy" wrote:

Assume Item No is in cell A1 of the calculator sheet which is the
active
sheet. The database workbook is named "Data.xls" on a sheet named
Data
with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in
message
...
I have a workbook with numbered data base items that I use along
with
other
workbooks, which are calculators for this data base information.
I
currently
go to the data base copy the whole row of information then go to
the
calculator workbook and paste this information. This is tedious
and
has
the
potential for errors because in my calculator workbook I have
protected
some
functions in certain cells staggered across the destination row,
so I
would
like to create a macro that will respect the protected cells and
enable to
data base information to transfer with the entry of the Item
number
only.

How can I enter, or paste, the item number that will automatically
enter
the
remaining item numbers information from a different workbook (data
base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Cells(ActiveCell.Row,1).Value = "" Then Exit Sub
Set rng1 = rng.Find(Cells(ActiveCell.Row,1).Value)
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
This macro actually works when I have my data number entered in A1 in the
destination workbook, the macro will input to row 1 just like I want. The
problem is with my data number entered in A1 I can continue adding the
same
data in blank celled rows throughout column A in the destination workbook.

This is almost correct, I need my data number in the destination workbook
to
enble the macro to enter in that exact row only from the "Data" to the
calculator "Destination" workbook. Example, in the Destination workbook:
A5,
enter item# A123, enable macro, then the Data information from the Data
workbook with this item # inputs in row 5, this function/macro is now
complete. I would than move my courser to A10, enter Item #A876, enable
macro, and this data inputs only in row 10 of the Destination workbook and
is
then complete. In summery I will be hard entering (or copying) an item
number in Column A of the Destination workbook and when I run the macro on
a
blank cell in column A that row would remain empty because it has no item
#.
The only way the macro will copy information to a Destination workbook is
wtih my Item #'s entered somewhere in column A and it would input only in
that row.

"Tom Ogilvy" wrote:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
Tom,

I appreciate you guidance. How can I get the Copy Destination:= to be
where
my courser resides? (in column A of course).

Because of your help this is what I ended up with:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'no copy the data
rng1.Resize(1, 5).Copy Destination:=Range("A1")
rng1.Offset(0, 6).Copy Destination:=Range("G1")

End Sub

Thank you,

"Tom Ogilvy" wrote:

Assume Item No is in cell A1 of the calculator sheet which is the
active
sheet. The database workbook is named "Data.xls" on a sheet named
Data
with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in
message
...
I have a workbook with numbered data base items that I use along
with
other
workbooks, which are calculators for this data base information.
I
currently
go to the data base copy the whole row of information then go to
the
calculator workbook and paste this information. This is tedious
and
has
the
potential for errors because in my calculator workbook I have
protected
some
functions in certain cells staggered across the destination row,
so I
would
like to create a macro that will respect the protected cells and
enable to
data base information to transfer with the entry of the Item
number
only.

How can I enter, or paste, the item number that will automatically
enter
the
remaining item numbers information from a different workbook (data
base)?

--
Thank you,

Trying Hard

Excel Data Base Link
 

Tom,

I need to learn much more about Visual Basic. I apreciate your time, this
works great!

Thank you,

Trying Hard/Harder...

"Tom Ogilvy" wrote:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Cells(ActiveCell.Row,1).Value = "" Then Exit Sub
Set rng1 = rng.Find(Cells(ActiveCell.Row,1).Value)
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
This macro actually works when I have my data number entered in A1 in the
destination workbook, the macro will input to row 1 just like I want. The
problem is with my data number entered in A1 I can continue adding the
same
data in blank celled rows throughout column A in the destination workbook.

This is almost correct, I need my data number in the destination workbook
to
enble the macro to enter in that exact row only from the "Data" to the
calculator "Destination" workbook. Example, in the Destination workbook:
A5,
enter item# A123, enable macro, then the Data information from the Data
workbook with this item # inputs in row 5, this function/macro is now
complete. I would than move my courser to A10, enter Item #A876, enable
macro, and this data inputs only in row 10 of the Destination workbook and
is
then complete. In summery I will be hard entering (or copying) an item
number in Column A of the Destination workbook and when I run the macro on
a
blank cell in column A that row would remain empty because it has no item
#.
The only way the macro will copy information to a Destination workbook is
wtih my Item #'s entered somewhere in column A and it would input only in
that row.

"Tom Ogilvy" wrote:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'now copy the data
rng1.Resize(1, 5).Copy Destination:=Cells(ActiveCell.Row,1)
rng1.Offset(0, 6).Copy Destination:=Cells(ActiveCell.Row,7)

End Sub

--
Regards,
Tom Ogilvy

"Trying Hard" wrote in message
...
Tom,

I appreciate you guidance. How can I get the Copy Destination:= to be
where
my courser resides? (in column A of course).

Because of your help this is what I ended up with:

Sub Button1_Click()
Dim rng As Range, rng1 As Range
Set rng = Workbooks("Data").Worksheets("Data").Range("A1:A20 00")
If Range("A1").Value = "" Then Exit Sub
Set rng1 = rng.Find(Range("A1"))
If rng1 Is Nothing Then
MsgBox "Not Found"
Exit Sub
End If
'no copy the data
rng1.Resize(1, 5).Copy Destination:=Range("A1")
rng1.Offset(0, 6).Copy Destination:=Range("G1")

End Sub

Thank you,

"Tom Ogilvy" wrote:

Assume Item No is in cell A1 of the calculator sheet which is the
active
sheet. The database workbook is named "Data.xls" on a sheet named
Data
with
Item numbers listed in columns A

Sub Button_click()
Dim rng as Range, rng1 as Range
set rng =Workbooks("Data").Worksheets("Data").Range("A1:A2 000")
if Range("A1").Value = "" then exit sub
set rng1 = rng.find(Range("A1")
if rng1 is nothing then
msgbox "Not Found"
exit sub
End if
' no copy the data
rng1.Resize(1,4).Copy Destination:=Range("A9")
rng1.offset(10,0).Resize(1,2).Copy Destination:=Range("J9")

' and so forth

End Sub

--
Regards,
Tom Ogilvy




"Trying Hard" wrote in
message
...
I have a workbook with numbered data base items that I use along
with
other
workbooks, which are calculators for this data base information.
I
currently
go to the data base copy the whole row of information then go to
the
calculator workbook and paste this information. This is tedious
and
has
the
potential for errors because in my calculator workbook I have
protected
some
functions in certain cells staggered across the destination row,
so I
would
like to create a macro that will respect the protected cells and
enable to
data base information to transfer with the entry of the Item
number
only.

How can I enter, or paste, the item number that will automatically
enter
the
remaining item numbers information from a different workbook (data
base)?

--
Thank you,

Trying Hard