Hi

I have never used VBA in Excel (but have in MS Access). What I'd like to do is when a user puts the curser in a cell and clicks a button I want to copy the row in which the cursure is places as well as copy the 1st four rows at the top of the sheet (always the 1st 4 rows) and paste these 5 rows into a sheet called "merge sheet".
The idea is that they can select a client by placing the curser on the above referenced cell and then merge the pasted info from the "merge sheet" into a word letter/label.

Any pointers in the right direction would be great.
And remember I am new to Excel VBA!
Thanks!

Go to the Macro Menu. Turn on record. Do the steps you have just described. Stop the macro recording. Hey Presto, there's your macro.

Yeah, I thought about that but while the first four rows are always the same, the other row I want copied changes to what ever row has the forcus by the user selecting it.

Yeah, I thought about that but while the first four rows are always the same, the other row I want copied changes to what ever row has the forcus by the user selecting it.

True, but then you have the majority of what you want. You should be able to work out how to get it to work like you want just by changing a couple of lines at the most. If you get stuck, you can post the code, saying where you are stuck. This way people can see you've done most of the work yourself and just need a little help to get you there :-)

Given the last post date I don't know whether Dan gave up or solved his problem, but recording a macro would not help in this case as tha produces

Note I only had 2 hdr rows

Sub copyrowMacro4()
'
' copyrowMacro4 Macro
' Macro recorded 20/12/2005 by brian warnock
'

'
Range("1:2,5:5").Select
Range("A5").Activate
Selection.Copy
Range("A9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Whereas to do what Dan wanted requires,IMHO, the following or similar.again only 2 hdr rows in this example

Sub copyrow()
Dim myrow As Integer
myrow = ActiveWindow.RangeSelection.Row
Rows(myrow).Select 'This copies the selected row
Selection.Copy
ActiveSheet.Paste Destination:=Worksheets("merge").Cells(3, 1)
Application.CutCopyMode = False
Range("1:2").Select
Selection.Copy
ActiveSheet.Paste Destination:=Worksheets("merge").Cells(1, 1)
Application.CutCopyMode = False

End Sub

Feel free to tweak as I'm self taught with no ref book so there may be better ways.

brian

Dim x As Integer
x = ActiveCell.Row
Range("1:4," & x & ":" & x).Copy
Sheets("Merge sheet").Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Pieter

x = ActiveCell.Row
Didn;t know you could do that . it's neat

Range("1:4," & x & ":" & x).Copy

Tried allsorts to get that syntax correct before giving up, its great what you can learn even when trying to help:D

Brian

I have similar request but I need the macro to do this:

I have 3 worksheets. Example

Copy from WKS2 cells A1.
Paste to WKS1 cell C8

Copy from WKS3 cells A1.
Paste to WKS1 cell D8

Copy from WKS2 cells F1.
Paste to WKS1 cell A3

Copy from WKS3 cells F1.
Paste to WKS1 cell A4

now once I run the macro again I need it to move to the next row auto and repeat the whole copy and paste like above.

Hope someone could code this micro

Thanks

My VBA is somewhat rusty now but I do know that what you are requesting is not like the rest of this thread so suggest that you delete post from here after copying it to a new thread.

Brian

Hello everyone, ...:)
(And especially to those who take the trouble to help me in this issue.:cool:
I know that was five years have passed since it was added comments on this topic...
But ... Never too late to adapt old methods to recent problems.)

I'm not knowledgeable in this of VBA code to "Excel MACRO" ...
And i would like if someone could help me in a method to adapt this code added by Brianwarnock
Sub copyrowMacro4()
'
' copyrowMacro4 Macro
' Macro recorded 20/12/2005 by brian warnock
'
'
Range("1:2,5:5").Select
Range("A5").Activate
Selection.Copy
Range("A9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End SubSo that at the middle of the code ...
Range("A9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Subinstead of pasting the copied cells
in to a pre-designated cell ("A9")
to be pasted below any of my currently selected cell.

Here is what a have done so far from this code:
Private Sub CommandButton1_Click()
' CommandButton TEST-1
' Macro recorded 20/12/2005 by brian warnock
'
Range("3:3,5:5").Select
Range("A3:A7").Activate
Selection.Copy
Range(ActiveCell.Offset(Rowoffset:=1).Address).Sel ect
ActiveSheet.Paste
Application.CutCopyMode = False

End SubBut... when i click on CommandButton to execute command,
it gives me ERROr DEBUG at this line;

Range(ActiveCell.Offset(Rowoffset:=1).Address).Sel ect
ActiveSheet.Paste
Application.CutCopyMode = False
Can someone please help me in this little, little, little,..:( Problem...
THANKs...

Here is what a have done so far from this code:
Private Sub CommandButton1_Click()
' CommandButton TEST-1
' Macro recorded 20/12/2005 by brian warnock
'
Range("3:3,5:5").Select
Range("A3:A7").Activate
Selection.Copy
Range(ActiveCell.Offset(Rowoffset:=1).Address).Sel ect
ActiveSheet.Paste
Application.CutCopyMode = False

End SubBut... when i click on CommandButton to execute command,
it gives me ERROr DEBUG at this line;

Range(ActiveCell.Offset(Rowoffset:=1).Address).Sel ect
ActiveSheet.Paste
Application.CutCopyMode = False
Can someone please help me in this little, little, little,..:( Problem...
THANKs...

This is what your code does:

1. Selects rows 3 and 5
2. Activates A3:A7
3. Copies rows 3 and 5
4. Selects the cell one row below the top-leftmost cell on the currently active range. In this case, A4
5. Attempts to paste into the active sheet, defaulting to the currently selected cell. In this case, A4. This results in a paste over rows 4 and 5.

The reason ActiveSheet.Paste fails is because attempting to paste into A4 causes rows 3 and 5 to attempt to paste into rows 4 and 5 <--note that this changes the shape of the paste area to 2 contiguous rows rather than two detached rows. You cannot have a copy range and a paste range overlap unelss they are the same shape. Here, they overlap on row 5.

As a quick fix, if you set rowoffset=3, you will clear the copy range and paste without an issue.

However, the syntax of your code leaves something to be desired : ). If you tell me what you are trying to do exactly, I can do my best to give you a more all-encompassing solution.