I have this scenario: On cell A1 of workbook A, when the text "Click" is
selected, certain cell from this workbook will be transferred to workbook B.
Is there any possible program that allows this scenario to function?

Ive been trying to troubleshoot the above but cant seem to get it started.
Can anybody help please?

Hi

Inset the code below in the codesheet for sheet1 of workbook A. (Rightclick
on the sheet tab and select View code, and paste the code in the sheet which
appears)

The range to copy and the destination can be changed as desired.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim wbA As Workbook
Dim wbB As Workbook

Set wbA = ThisWorkbook
Set wbB = Workbooks("Book2.xls") 'change to desired name

If Target.Address = "$A$1" Then
If Target.Value = "Click" Then
Range("B2:H4").Copy wbB.Sheets("Sheet1").Range("A1")
End If
End If
End Sub

Hopes it helps.

Regars,
Per

"amelia" skrev i meddelelsen
...
I have this scenario: On cell A1 of workbook A, when the text "Click" is
selected, certain cell from this workbook will be transferred to workbook
B.
Is there any possible program that allows this scenario to function?

Ive been trying to troubleshoot the above but cant seem to get it started.
Can anybody help please?

Gee, I cant seem to get the result, I do not know where I go wrong in the
code that I've inserted. Condidering that I am not proficient in Excel, I
would just like to check a few things.

1) if i name wbA as section properties. It should then be:
Dim wbsection properties As Workbook

2)Set wbA=ThisWorkbook
ThisWorkbook is the default name or must i change it to a desired name

3)What's the difference between A1 and $A$1?

Thank you once again.

Hi

1) wbA is just a variable used to hold a workbook object. It can be named as
desired (without spaces), but then it shall be changed throughout the code.

2) ThisWorkbook is used to make a reference to the workbook holding the
code. You can change it to something like "Set wbA =Workbooks("Book1.xls")".
Assuming the code is in workbook A, I would use my code.

3) The first is a relative referece the other is a absolute reference. In
VBA all addreses are returned as absolute addresses.

As you don't say which cells to copy, and where to paste, I've just used
some cells as example. If ýou need help copying and pasting desired cells,
state the cells.

Hopes it helps
Per

"amelia" skrev i meddelelsen
...
Gee, I cant seem to get the result, I do not know where I go wrong in the
code that I've inserted. Condidering that I am not proficient in Excel, I
would just like to check a few things.

1) if i name wbA as section properties. It should then be:
Dim wbsection properties As Workbook

2)Set wbA=ThisWorkbook
ThisWorkbook is the default name or must i change it to a desired name

3)What's the difference between A1 and $A$1?

Thank you once again.

Ohh ok..i understand better now.

Mayb ill specifically re-state my scenario again:
In wbA, when a user click a value(thickness) from range B12 to B59, the
other values(Depth,Area) where Depth, Area is in column A,D respectively from
that particular row where (thickness) has been selected, will be transferred
to wbB where Depth will be inserted in cell C16, Area in N16.

I am not sure if this is possible to be programmed but Thanks for the help
again.

Cheers.


"Per Jessen" wrote:

Hi

1) wbA is just a variable used to hold a workbook object. It can be named as
desired (without spaces), but then it shall be changed throughout the code.

2) ThisWorkbook is used to make a reference to the workbook holding the
code. You can change it to something like "Set wbA =Workbooks("Book1.xls")".
Assuming the code is in workbook A, I would use my code.

3) The first is a relative referece the other is a absolute reference. In
VBA all addreses are returned as absolute addresses.

As you don't say which cells to copy, and where to paste, I've just used
some cells as example. If ýou need help copying and pasting desired cells,
state the cells.

Hopes it helps
Per

"amelia" skrev i meddelelsen
...
Gee, I cant seem to get the result, I do not know where I go wrong in the
code that I've inserted. Condidering that I am not proficient in Excel, I
would just like to check a few things.

1) if i name wbA as section properties. It should then be:
Dim wbsection properties As Workbook

2)Set wbA=ThisWorkbook
ThisWorkbook is the default name or must i change it to a desired name

3)What's the difference between A1 and $A$1?

Thank you once again.

Ok there is no click event which can be used, but we can use a
Double_Click or Right_Click event.
Only one of the two first lines shall be used, the other is to be
deleted. Inset the code as described in previous post.

I still assume that values shall be pasted into sheet1 of wbB.


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel
As Boolean)

Dim wbA As Workbook
Dim wbB As Workbook

Set wbA = ThisWorkbook
Set wbB = Workbooks("Book2.xls") 'change to desired name
Set isect = Intersect(Target, Range("B12:B59"))

If Not isect Is Nothing Then
TargetRow = Target.Row
Cells(TargetRow, "A").Copy _
Destination:=wbB.Sheets("Sheet1").Range("C16")
Cells(TargetRow, "D").Copy _
Destination:=wbB.Sheets("Sheet1").Range("N16")
End If
End Sub

Hopes it helps

Regards,
Per

On 28 Dec., 01:28, amelia wrote:
Ohh ok..i understand better now.

Mayb ill specifically re-state my scenario again:
In wbA, when a user click a value(thickness) from range B12 to B59, the
other values(Depth,Area) where Depth, Area is in column A,D respectively from
that particular row where (thickness) has been selected, will be transferred
to wbB where Depth will be inserted in cell C16, Area in N16.

I am not sure if this is possible to be programmed but Thanks for the help
again.

Cheers.



"Per Jessen" wrote:
Hi

1) wbA is just a variable used to hold a workbook object. It can be named as
desired (without spaces), but then it shall be changed throughout the code.

2) ThisWorkbook is used to make a reference to the workbook holding the
code. You can change it to something like "Set wbA =Workbooks("Book1.xls")".
Assuming the code is in workbook A, I would use my code.

3) The first is a relative referece the other is a absolute reference. In
VBA all addreses are returned as absolute addresses.

As you don't say which cells to copy, and where to paste, I've just used
some cells as example. If ýou need help copying and pasting desired cells,
state the cells.

Hopes it helps
Per

"amelia" skrev i meddelelsen
...
Gee, I cant seem to get the result, I do not know where I go wrong in the
code that I've inserted. Condidering that I am not proficient in Excel, I
would just like to check a few things.

1) if i name wbA as section properties. It should then be:
Dim wbsection properties As Workbook

2)Set wbA=ThisWorkbook
ThisWorkbook is the default name or must i change it to a desired name

3)What's the difference between A1 and $A$1?

Thank you once again.- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -