Transferring of certain cells from one workbk to another

[amelia]

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?

 

[Per Jessen]

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]

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.

 

[Per Jessen]

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]

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]

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

 

[amelia]

I've tried programming it but it cant seems to work. I have no idea where I
go wrong:< By the way, I am using excel 2007, so it should be: Set wbB =
Workbooks("Book2.xlsx") right? Does it work for you?

 

[Per Jessen]

It works for me.

When you say, that you can't get the macro to work, does it mean that it
throws an error, or doesn't it get activated at all.

Is the macro placed in the code sheet for the sheet where the user select
the thickness?
You are right about wbB using excel 2007. Is the workbook containing the
macro saved as an macro enabled workbook ?

Regards,
Per

 

[amelia]

Destination:=wbB.Sheets("Sheet1").Range("C16")

A pop-up error box(message shown below) appears when I type the above-
compile error:
Expected: expression

And it doesnt get activated at all.

Yup, Ive placed the code where the user selects the thickness and and save
it as macro enabled workbook.

Thanks.

 

[Per Jessen]

The line mentioned can not stand alone, it's a part of the line:

Cells(TargetRow, "A").Copy _
Destination:=wbB.Sheets("Sheet1").Range("C16")

Notice the "_" sign indication that the two lines should be seen as one
statement and can be entered as such.

If this don't help you can mail me a sample workbook with the code in.

Regards,
Per

 

[amelia]

Hooray!! It works. Thank you for the unconditional help.

 

[Per Jessen]

Thanks for your reply. I'm glad you finally made it work as desired.

Regards,
Per

 

[amelia]

Hi Per Jessen,

I'm now trying to transfer the cells from one sheet to another but..

I came by the error below upon double-clicking the ranged cell.
Compile error:method or data member not found.

Error showed at ....sh1.Sheets...under the line below.
Cells(TargetRow, "A").Copy_Destination = sh1.Sheets("K Joint").Range("C16")

Below is the full code:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)

Dim sh As Worksheet
Dim sh1 As Worksheet

Set sh = Worksheets("Section Properties")
Set sh1 = Worksheets("K Joint")
Set isect = Intersect(Target, Range("C12:C177"))

If Not isect Is Nothing Then
TargetRow = Target.Row
Cells(TargetRow, "A").Copy_Destination = sh1.Sheets("K Joint").Range("C16")
Cells(TargetRow, "C").Copy_Destination = sh1.Sheets("K Joint").Range("E16")
Cells(TargetRow, "G").Copy_Destination = sh1.Sheets("K Joint").Range("G16")
End If


End Sub

Thanks in advance.

 

[Per Jessen]

Hi Amelia

Three problems in that statement:

1) The "_" sign shall only be used when you have one statement split
on two or more lines to indicate that it's one statement.

2) You are missing af colon before the equal sign.

3) You have a double sheet reference in the line mentioned,

use either

Cells(TargetRow, "A").Copy Destination := sh1.Range("C16")

or

Cells(TargetRow, "A").Copy Destination:= Sheets("K Joint").Range
("C16")

Hopes this helps

 

[amelia]

Thank you for your reply. Ive tried as per what you've mentioned but..

I still came by the error below upon double-clicking the ranged cell.
Run-time error '9': Subscript out of range

The line below is highlighted when I want to debug.
Set sh1 = Worksheets("K Joint")

 

[Per Jessen]

That error indicate that the sheet don't exist in the active workbook. Check
for typo's.

Regards,
Per

 

[amelia]

thanks! it can work now but..

I would like to actually add a few other things into the code. My bad, I
should have explained to you intially. Ive scanned through the discussion
groups but I became confused on which one to use.

Are the below possible to be added into the code:
1) Copy only the value from "section" to "k Joint". I want the fomat(thick
box border and blue fill) in "K Joint" to remain upon copying.

2) Both the sheets are protected. Is there anyway that the values can still
be transferred?

3) I have 3 Joints in 3 separate sheets: "K Joint", "T Joint" and "N Joint".
Example in "K Joint"--- cell A16: chord, A17:Brace1, A18:Brace2. If the user
wants to select the section size for chord, he will click the word--chord,
which is hyperlinked to "section" sheet. Upon selecting the section size from
the "section" sheet, (the code that had been written previously), it wil
automatically return to "K Joint". So, if the user now wants to select the
section size for Brace1 and Brace2, the same procedure happens.
The same procedure also happens if the user now wants to select the section
size for "T Joint" and "N Joint".

I am a VB novice and would really appreciate if you are able to aasist me in
this. Thanks again.

 

[amelia]

Hi Per,

I would just like to check if it's possible to write a code for the scenario
I have mentioned previously? Awaiting your reply. Thanks again.

 

[Per Jessen]

Sure it can be done.

This worked for me:

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

Dim sh As Worksheet
Dim sh1 As Worksheet

'Set sh = Worksheets("Section Properties")
Set sh1 = Worksheets("K Joint")
Set isect = Intersect(Target, Range("C12:C177"))

If Not isect Is Nothing Then
TargetRow = Target.Row
Cells(TargetRow, "A").Copy Destination:=sh1.Range("C16")
Cells(TargetRow, "C").Copy Destination:=sh1.Range("E16")
Cells(TargetRow, "G").Copy Destination:=sh1.Range("G16")
End If
End Sub

Regards,
Per

 

[amelia]

That worked for me but.. I would like to actually add a few other things into
the code. My bad, I should have explained to you intially. Ive scanned
through the discussion groups but I became confused on which one to use.

Are the below possible to be added into the code:
1) Copy only the value from "section" to "k Joint". I want the fomat(thick
box border and blue fill) in "K Joint" to remain upon copying.

2) Both the sheets are protected. Is there anyway that the values can still
be transferred?

3) I have 3 Joints in 3 separate sheets: "K Joint", "T Joint" and "N Joint".
Example in "K Joint"--- cell A16: chord, A17:Brace1, A18:Brace2. If the user
wants to select the section size for chord, he will click the word--chord,
which is hyperlinked to "section" sheet. Upon selecting the section size from
the "section" sheet, (the code that had been written previously), it wil
automatically return to "K Joint". So, if the user now wants to select the
section size for Brace1 and Brace2, the same procedure happens.
The same procedure also happens if the user now wants to select the section
size for "T Joint" and "N Joint".

Thanks a million.