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