Button and Macro

[dergrimmer]

Hello at all I'm new in this Forum because I can not find a solution for my Excel problem in the Internet. I have a sheet with some Buttons assignt with Macros. The Button has the following function: copy a Special part of the Row, in which the Button is, to the next sheet. The problem is that I have some Buttons with the same function, but for different Rows (for example one for row 4 and one for row 5), they all copy always the same part (A(row) to G(row)) but in different rows. I now want to make only one Macro, assigned to all Buttons, which can find out in which row the Button is, you press. SO if you press the Button in row 4 he has to copy the text from Range A4 to G4 and if you press the Button in row 5 he has to copy the text from Range A5 to G5. But everything with the same Macro because if I Need to copy the Content from a new line I don't want to create a new Macro with the row. I only want to Create a Button an assign the same Macro. I don't know if this is possible or how it works. Thanks for your help dergrimmer

 

[kevin lazell]

hello dergrimmer
welcome to the board
try the following code, it does not use buttons. it is set to work on row selection
change to suit your needs

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Variant
If Target.Column = 1 Then x = Target.Row
Range("a" & x & ":g" & x).Copy
On Error Resume Next
With Sheets("sheet2").Range("a" & x & ":g" & x).PasteSpecial.xlPasteValues
Application.CutCopyMode = False
End With
End Sub

good luck

kevin

 

[Smitty]

Welcome to the Board!

It's always a good idea to post your code when you have a code question.

In your case I'd look at using something like ActiveCell.Row to identify the row number. That will cut you down to just one button right away, and trying to tie buttons to rows is generally an exercise in futility.

You'd have something like this:

Dim lng_CurRow as Long

Range("A" & lng_CurRow & ":G" & lng_CurRow).Copy ' etc

HTH,