Macro based on command button location?

[mattylance]

My macro currently looks like this:
Sub Day1()
'
' Day1 Macro
' Macro recorded 6/20/2006 by Matt Lance
'

'
Range("V7:V28").Select
Selection.Copy
ActiveWindow.SmallScroll ToRight:=-7
Range("D7").Select
ActiveSheet.Paste
End Sub

What I want to do is have it so that instead of selecting D7 to paste
I want it to select the same column that the command button is in an
then the 7th row. This way I can have one macro used across severa
buttons instead of having a macro for every column I use.

Does anybody have any suggestions?

 

[mattylance]

Okay to make it a little more clear here is what I am looking for...

I want 1 macro created which I will have assigned to several button
across several columns. When I push the button I want to select rang
v7:v28, copy the data and then paste it in the 7th row of the sam
column that the button was pushed.

Therefore, if the button was in column B or in column Z, it would stil
select the same data but it would only paste it in the specific colum
the button is in

 

[mattylance]

anybody have any ideas?

 

[Loxley]

you need to use relative references

just to be sure are you saying that the range "V7:V28" is always fixed
?

Mark

 

[Loxley]

create your 3 buttons,

in the example below they are in cells L8,M8,N8 although this is not
relevant
as the code for each button press 'moves' the cursor to the activated
column.

this code below will work, but you could tidy it up a bit by making the
column identifier a variable.


Private Sub CommandButton1_Click()

Range("L1").Select ' column the macro button is in

ActiveCell.Offset(6, 10).Range("A1:A22").Select
Selection.Copy
ActiveCell.Offset(9, -10).Range("A1").Select ' this is cell L16
ActiveSheet.Paste

End Sub
Private Sub CommandButton2_Click()

Range("M1").Select ' column the macro button is in

ActiveCell.Offset(6, 9).Range("A1:A22").Select
Selection.Copy
ActiveCell.Offset(9, -9).Range("A1").Select ' this is cell M16
ActiveSheet.Paste

End Sub
Private Sub CommandButton3_Click()

Range("N1").Select ' column the macro button is in

ActiveCell.Offset(6, 8).Range("A1:A22").Select
Selection.Copy
ActiveCell.Offset(9, -8).Range("A1").Select ' this is cell N16
ActiveSheet.Paste

End Sub

 

[Dave Peterson]

These are all buttons from the Forms toolbar that have the same macro associated
with each button.

If yes, then maybe something like this will help you get started:

Option Explicit
Sub Day1()
Dim RngToCopy As Range
Dim DestCell As Range
Dim DestCol As Long
Dim myBTN As Button

With ActiveSheet
Set myBTN = .Buttons(Application.Caller)
Set RngToCopy = .Range("V7:V28")
DestCol = myBTN.TopLeftCell.Column
Set DestCell = .Cells(7, DestCol)

RngToCopy.Copy _
Destination:=DestCell
End With

End Sub