Using Button location as variable?

[Hombre]

Trying to use one macro for multiple Forms Buttons in multiple columns to copy and then past from the same columns in the spreadsheet..

If I cannot do above, then I need separate buttons and separate Macros for each column I am in, even though they copy from the same place but paste to the column the button is in.

I used:
dim btnrng = Range
Set btnrng = ActiveSheet.Buttons(Application.Caller).TopLeftCell

to get the button's cell address, but it returns a buttom area not defined error, Assuming this is solved (it is not), how do I use the address as a variable, perhaps with offsets?

My original code is:
If Range("H24") = "1" Then
Range("AE25:AF81").Select
Selection.Copy
Range btnrng.Address.Select
ActiveSheet.Paste
Range("H23").Select
End If
If Range("H24") = "2" Then
Range("AG25:AH81").Select
Selection.Copy
Range btnrng.Address.Select
ActiveSheet.Paste
Range("H23").Select
End If

etc to 70 results of the button, and then 70 columns to copy from. Then I paste to the same column as the button.

I would like variables to paste so I don't have 10 (eg) buttons times 70 copies for 700 if statements in 10 macros.

Halp!

 

[Hombre]

Keeping in mind I am a novice:

I think the following is where I put my range to copy from in?
.OnAction = "'CopyCells """ & addr & """'"

I just do not see how it knows which column to copy from and where/when to paste it.........is it still using the Macro of ifs....?

H

 

[dmt32]

Keeping in mind I am a novice:

I think the following is where I put my range to copy from in?

You don't need to do anything, the code get the TopLeftCell.Address places this in the variable addr which is used as an argument to pass to the common code

.OnAction = "'CopyCells """ & addr & """'"

I just do not see how it knows which column to copy from and where/when to paste it.........is it still using the Macro of ifs....?

It knows because variable addr contains the cell address - no If statements are needed.

If you want to see this when press one of the buttons, place a msgbox where shown below

Sub CopyCells(ByVal ButtonAddress As String)
    Dim BtnRng      As Range, CopyRng As Range
    Dim BtnValue    As Long
    '---------------------------------------------------------------------
    '@dmt32 MRExcel - Oct 2023
    '---------------------------------------------------------------------
    
    MsgBox ButtonAddress

' rest of code

End Sub

Note of caution, do not attempt to make any changes to OnAction property line, the quote mark structure is critical.

As mentioned by @RoryA , Form controls are simpler as you can, as we have done, assign the same macro to all of them. But more than this, you can also pass arguments which makes coding a project requirement like yours much simpler.

Hope Helpful

Dave

 

[Hombre]

The "if" statements are used for the selection of the column to copy (one of 70), and the paste address (under the button). I see how you get he button address, but not how you get the copy column.

Consider a spreadsheet: You have a list of boxes (in a table), and a list of 10,000 items of all sizes ('database'). You click on a button, and the box specs (Length, width, height, weight allowed) are inserted under the button. The system then shows (above) how many of your items fit into the particular box you selected in that column the button is located. THEN, you go to the second button in a new column, and selected a second box dimension, and the items that fit in that box are counted (1st selected items are excluded because they were already in the first box).

See picture of the sheet extract attached. I just want to make sure I explain theings correctly. You are VERY kind to put up with me!

 

[dmt32]

The "if" statements are used for the selection of the column to copy (one of 70), and the paste address (under the button). I see how you get he button address, but not how you get the copy column.

This line in the common code gets the copy column which, assuming you followed my instructions in full, is provided with cell address passed from the button you press + value in the cell where button is located

Set CopyRng = Cells(25, 31 + BtnValue).Resize(81, 2)

Solution is based on your requirements in the original post & does not require multiple If statements - what you have not said, is the code doing what you want?
If not, then understand your issue more clearly, post copy of your actual worksheet (with dummy data if needed) using MrExcel Addin XL2BB - Excel Range to BBCode
or, better still, place copy of workbook in a file sharing site like Dropbox & provide a link to it here.

Dave