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!

 

[dmt32]

hi,

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.

Your thinking is largely correct as all you need to do is to pass the cell address from each button to common code (I can write a bit of code to do this for you) which should, if I have understood your explanation correctly, negate need for multiple If or case statements in fact, should not need any at all.

However, & before I think that I can add to the list of assistance you are getting, I need to confirm my understanding that all your FORM BUTTONS copy from the same ranges you have specified which are selected based on the value in the cell value where button is located and paste one row below said button in the same column?

Dave

 

[Skyybot]

I have included a picture to illustrate my concept. Simple data in Columns A, B, and C. A CommandButton that calls the attached Subroutine. Test this and see if it works for you. If so, we can adjust to suit your Range requirements. Select a cell in Column H and click CommandButton1. Do the same with Column K. Change H24 Value and do the same.

Option Explicit
Sub copyPaste()
Dim wb As Workbook, sht As Worksheet, cRng As Range, col As String, hRng As Range
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
Set hRng = sht.Range("H24")
col = Split(Selection.Address, "$")(1)
Select Case col
    Case Is = "H"
        Call selectRange(hRng)
        sht.Range("H1").PasteSpecial (xlPasteAll)
    Case Is = "K"
        Call selectRange(hRng)
        sht.Range("K1").PasteSpecial (xlPasteAll)
    Case Is = "N"
        Call selectRange(hRng)
        sht.Range("N1").PasteSpecial (xlPasteAll)
End Select
Application.CutCopyMode = False
End Sub
Sub selectRange(ByVal hRng As Range)
Dim wb As Workbook, sht As Worksheet
Set wb = ThisWorkbook: Set sht = wb.ActiveSheet
Select Case hRng.Value
    Case 1
        sht.Range("A1:A20").Copy
    Case 2
        sht.Range("B1:B20").Copy
    Case 3
        sht.Range("C1:C20").Copy
End Select
End Sub

 

[Hombre]

hi,


Your thinking is largely correct as all you need to do is to pass the cell address from each button to common code (I can write a bit of code to do this for you) which should, if I have understood your explanation correctly, negate need for multiple If or case statements in fact, should not need any at all.

However, & before I think that I can add to the list of assistance you are getting, I need to confirm my understanding that all your FORM BUTTONS copy from the same ranges you have specified which are selected based on the value in the cell value where button is located and paste one row below said button in the same column?

Dave

Dave, thank you for your assistance and comments!!
Yes, that is true.

 

[dmt32]

Dave, thank you for your assistance and comments!!
Yes, that is true.

did solution provided by @NoSparks on the other site resolve your issue?

Dave

 

[Hombre]

did solution provided by @NoSparks on the other site resolve your issue?

Dave

no. You are the only one I think expressed exactly what the issue was in the last question. Being quite the layman, although my ws looks like I am an expert in Excel, I am not a programmer and macros are a challenge to get efficient.

 

[NoSparks]

@Hombre
have you look within the last 13 hours?

 

[dmt32]

no. You are the only one I think expressed exactly what the issue was in the last question.

I generally do not respond where cross posts are involved as it can create this this kind of issue.
I have figured something out that I think does what you want & negates need for multiple If statements - but before I post I would suggest as a courtesy you let @NoSparks know what the issues are with suggested solution & see if they can resolve. If no joy, I will post my thoughts.

Dave

 

[Hombre]

I generally do not respond where cross posts are involved as it can create this this kind of issue.
I have figured something out that I think does what you want & negates need for multiple If statements - but before I post I would suggest as a courtesy you let @NoSparks know what the issues are with suggested solution & see if they can resolve. If no joy, I will post my thoughts.

Dave

I am confused. What issues are there? At any rate, I just see a few lines of pseudo code from NoSparks that do not help--nothing useable for me in my current abilities. Nothing for 1-70, variables, etc.

Thanks for trying anyway--I may be doomed to 700 if statements which work ok--just the problem supporting changes and knowing it is a poor man's coding!

Best, and best to all that tried to help.
Hombre

 

[dmt32]

I am confused. What issues are there?

forgive me, in my advancing years I do not always articulate my point well sometimes - have a read here: A message to forum cross posters
which better explains issues surrounding cross-posting which my help understand why some like myself, normally avoid such posts.

At any rate, I just see a few lines of pseudo code from NoSparks that do not help--nothing useable for me in my current abilities. Nothing for 1-70, variables, etc.

If solution offered is not doing what is required then out of courtesy to author (and others who tried to help), should let them know who may be able to update their suggestion.

Thanks for trying anyway--I may be doomed to 700 if statements which work ok--just the problem supporting changes and knowing it is a poor man's coding!

I had not given up on you, I was just waiting to see if the solution offered on the other site resolved your issue?
Also. I neglected to ask if ALL the forms buttons on the worksheet will use the common code? or do you have forms buttons doing other things in your project?

Dave

 

[Hombre]

forgive me, in my advancing years I do not always articulate my point well sometimes - have a read here: A message to forum cross posters
which better explains issues surrounding cross-posting which my help understand why some like myself, normally avoid such posts.


If solution offered is not doing what is required then out of courtesy to author (and others who tried to help), should let them know who may be able to update their suggestion.



I had not given up on you, I was just waiting to see if the solution offered on the other site resolved your issue?
Also. I neglected to ask if ALL the forms buttons on the worksheet will use the common code? or do you have forms buttons doing other things in your project?

Dave

OK, thanks!
There are other buttons, for hiding/unhiding on the same worksheet, and a couple other copy paste to store the answers as a template.

I think I can get to the copy, but then I need a way to paste to a relative address in the column. I got the address of the button, got the 1-70 to copy from the right column, but then can't seem to get the paste back to my address (button variable offset down 1). If I make the button address a variable "r", it returns the 1-70, and with r.address it errors.
H