Solved: create the button on file A, but use it for file B

[clarksonneo]

Hi,

There is a file B, and I don't want to create a button/marco in file B.

I want to create the file in file A and then apply it on file B.



For example,

On File B's column B, each cell has a number.

When I select a cell in file B's column B and click the button in file A, then the number in the selected cell will be pasted to the celll which is next to the selected cell in file B.



How can I do that?

[Tinbendr]

That's a little bit of work to recreate a file for testing. Could you u/l a sample file with the expected results?

David

[Aussiebear]

For example,
On File B's column B, each cell has a number.

When I select a cell in file B's column B and click the button in file A, then the number in the selected cell will be pasted to the celll which is next to the selected cell in file B.

Lets just spend some time here to clear up the logic behind the concept you are raising. From what you have posted so far, am I right in assuming you have two files (A & B), of which file A has a macro attached to a button, which copies a number from file A to file B. The destination cell in File B will be in Column C of a row chosen by the user.

What determines the file A number to be copied?
What rules do you apply to chose the column B cell in file B?
Can these be incorporated into a logical rule useful to Excel?

To assist us in understanding your concept, please attach a workbook containing a before and after layout. File A & File B data can be located on the same sheet where possible. On a additional sheet the after results you are requiriing.

When replying, to attach a file, scroll down to Manage Attachments, click this and follow the prompts.

[mikerickson]

I read the situation as both the source and destination cells are in FileB, but the code is in FileA.

Working between workbooks can be done if everything is fully qualified. [VBA]Workbooks("WorkbookA.xls").Sheets("Sheet1").Range("A1") = Workbooks("WorkbookB.xls").Sheets("Sheet2").Range("B2")
[/VBA]
The above code works, even if that code is in a third workbook. (Provided that all workbooks involved are open at the time.)

[Aussiebear]

I read the situation as both the source and destination cells are in FileB, but the code is in FileA.

If this is true, why go to all this trouble just to copy B1 to C1 for example?

[clarksonneo]

hi

please see the picture for sample file with the expected results.

the left file is file a which has a button
the right file is file b which has some number

what I want to do is, for exameple,
when I select cell B2 in file B and click the button in file A, file B's cell C2 will be equal to 2 (ie cells B2's value)

thanks

[mikerickson]

What code do you currently have?

As Aussiebear noted, dragging a cell to the right one cell or even copy pasting is easier than switching workbooks to press a button.

[Cowlers]

How about:

[vba]
Private Sub CommandButton1_Click()
Dim FileA As Workbook
Dim FileB As Worksheet
Set FileA = ActiveWorkbook
Set FileB = Workbooks("FileB.xlsx").Worksheets(1)
FileB.Activate
If ActiveCell.Column = 2 Then
ThisVal = ActiveCell.Value
ReferenceRow = ActiveCell.Row
ActiveSheet.Cells(ReferenceRow, 3).Value = ThisVal
End If
FileA.Activate
End Sub
[/vba]

I tried this on two workbooks and it did the trick.

Best regards,

Dan

[mikerickson]

Rather than activating, one could use the Selection property of the Window.

[mikerickson]

You could put this in a code module in FileA after adjusting for the name of FileB.

[VBA]Sub test()
Dim BookB As Workbook
On Error Goto ErrorHalt
Set BookB = Workbooks("Workbook2.xlsx")

With BookB.Windows(1).Selection
If .Column = 2 Then
With .Columns(1)
.Offset(0, 1).Value = .Value
End With
End If
End With

ErrorHalt:
Err.Clear
On Error Goto 0
End Sub[/VBA]

[Aussiebear]

Should we be testing to ensure that Workbook2 is open?

[mikerickson]

The error checking does that.

Is it nessesary?

Like Shakespear's monkeys, one never knows what control a user will press.

With the button residing in THIS workbook, I think its optimistic to assume that THAT workbook is open.