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!

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Forms Control Button Relative position for cut and paste - OzGrid Free Excel/VBA Help Forum
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Skyybot]

@Hombre , CommandButtons do not, and cannot, have Range Addresses. You can use an ActiveCell reference at the beginning of your code to "Choose" which Column to Paste to.

 

[Hombre]

@Hombre , CommandButtons do not, and cannot, have Range Addresses. You can use an ActiveCell reference at the beginning of your code to "Choose" which Column to Paste to.

confused: The following returns the button's cell address (in its own sub, but errors as the start of my code).

Dim BtRng As Range
Set Btnrng = ActiveSheet.Buttons(Application.Caller).TopLeftCell

Do I do:?

ActiveCell = Btnrng.address

then offset to where I want the paste and paste?

Sorry I am not better at this--

 

[dmt32]

@Hombre , CommandButtons do not, and cannot, have Range Addresses. You can use an ActiveCell reference at the beginning of your code to "Choose" which Column to Paste to.

OP did state

Trying to use one macro for multiple Forms Buttons

which should, subject to more detail, using common code solution requested be doable

Helpful to forum if you could post an example of your worksheet with dummy data using MRExcel Addin XL2BB - Excel Range to BBCode

Dave

 

[Hombre]

OP did state


which should, subject to more detail, using common code solution requested be doable

Helpful to forum if you could post an example of your worksheet with dummy data using MRExcel Addin XL2BB - Excel Range to BBCode

Dave

Here is my code for Button 1--I have 10 buttons at different columns and all draw from the same "copy" columns, then paste to the button's column, in the following code--The below example is for a button in H24, with the selection choice returning "1" "2" or "3" (I have 70 options). It copies from a different column for each of 1-70 options, then pastes to H25- for all 70 options.

Macro 2 is the same code, but pastes to : "K25" with the button in K24, (not inH)

Macro 3, same but pastes to N25, etc etc with 70 x 10 if statements it gets hard for maintenance or adding more etc. I am hoping for the button to use the same code for all 10 buttons, and say "I am in column K, so paste to K25 etc. But how I KNOW I am in column K? I thought getting the address of the button in the macro first, then using it as a variable to paste to "the macro cell address offset 1" kind of deal.

Thank you for trying to help all--the workbook is huge and does a lot for my industry and I am trying to make it more foolproof to have others use it.....

The current code:

Sub ALEQ1()
comments
Application.ScreenUpdating = False
If Range("H24") = "1" Then ' Button is over H24 and returns the relative table # 1-70)
Range("AE25:AF81").Select ' there are 70 columns to copy from and then to paste to the button's column at "X25)
Selection.Copy
Range("H25").Select
ActiveSheet.Paste

End If

If Range("H24") = "2" Then
Range("AG25:AH81").Select
Selection.Copy
Range("H25").Select
ActiveSheet.Paste
Range("H23").Select
End If

If Range("H24") = "3" Then
Range("AI25:AJ81").Select
Selection.Copy
Range("H25").Select
ActiveSheet.Paste
Range("H23").Select
End If


'etc etc to 70 options/columns to copy from. I want just 70 if statements, not 700

 

[Skyybot]

Option Explicit
Sub copyPaste()
Dim wb As Workbook, sht As Worksheet, cRng As Range, col As String
Set wb = ThisWorkbook: Set sht = ActiveSheet
col = Split(Selection.Address, "$")(1)
Select Case col
    Case Is = "H"
        'Do code if column H is selected
    Case Is = "K"
        'Do code if column K is selected
    Case Is = "N"
        'Do code if column N is selected
End Select
End Sub

 

[NoSparks]

@Hombre
was my suggestion at Oz of any use?

 

[Hombre]

Option Explicit
Sub copyPaste()
Dim wb As Workbook, sht As Worksheet, cRng As Range, col As String
Set wb = ThisWorkbook: Set sht = ActiveSheet
col = Split(Selection.Address, "$")(1)
Select Case col
    Case Is = "H"
        'Do code if column H is selected
    Case Is = "K"
        'Do code if column K is selected
    Case Is = "N"
        'Do code if column N is selected
End Select
End Sub

This will work for the "Paste" portion

Case Is = "H"
Paste to col H
Case Is = "K"
Paste to col K

But

If Range("H24") = "2" Then
Range("AG25:AH81").Select
Selection.Copy

Case Is = "H"
Paste to H25
Case Is = "K"
Paste to K25
etc

The BOLD if Range ("H24") needs to be the column the button is in (variable)

......unless I am missing something.

 

[Hombre]

@Hombre
was my suggestion at Oz of any use?

I did not see it--sorry--will look now