Copy an entire row by selecting one cell 2

[henkus]

I would like to be able to copy an entire row and paste it to another location by just selecting the cell in row A. I'm pretty new to VB and can't figure out the syntax. please help!!

 

[bkpchs237]

henkus,

I don't quite know what you're after, but you can't "copy" the items based on a particular cell, but you can have Excel look for items for you and return the desired values.

You could try vlookup formulas that would return your desired values: i.e. =vlookup(a1,range,1,false) then in the adjacent cell =vlookup(a1,range,2,false), =vlookup(a1,range,3,false) and so on...

Or: You could check out this thread:
thread68-220970 If you want to do this manually you could right-click on the row header and choose Copy, then paste wherever you want.

The first two methods have the advantage of "copying" cells with data only and not the entire 256 columns at a time.

Hope this helps.

 

[henkus]

This is it,
Range"A*".Select (where * is a variable row)
then Range "A*:R*".Copy
Where I'm having the problem is setting the variable
does this explain better?
henkus

 

[acron]

Try one of the following approahes :

Range("A1&quot.Offset(4, 0).EntireRow = _
Range("A1&quot.Offset(0, 0).EntireRow.Value

which copies th econtents of row 1 to row 5

or

Range("A1&quot.Offset(0, 0).EntireRow.Copy
Range("A1&quot.Offset(4, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False

will do the same thing

AC

 

[DaleWatson123321]

henkus,

I’ve developed a working model which I believe "zeros in" specifically on the task you described - i.e. where you want to "selectively" choose a row to copy by placing your cursor on that row.

Because you also say you are new to VB, I would recommend that you email me, and I'll attach this example model via return email.

With the model, you only need to place your cursor on the row you wish to copy, and then activate the routine. I have attached the routine to the letter &quot;Q&quot;, so you would simply hold down the <Control> key, and hit Q.

The routine automatically copies the contents of the current row to your &quot;Destination&quot; sheet, and &quot;tacks in on&quot; to the next available (blank) row.

Rather than set it to copy the &quot;entire&quot; row, I set the routine to copy from A to Z, and of course you can EASILY modify the &quot;Z&quot; to match with the last column of the data in your &quot;Origin&quot; sheet.

The routine returns you to your &quot;Origin&quot; sheet, and moves down to the next row, and terminates. This is of course intentional, so you can have &quot;control&quot; over which row you choose to copy next - i.e. just move your cursor to the next row you wish to copy and use <Control> Q.

Because the routine automatically moves down to the next row, this permits you to just keep hitting <Control> Q in a situation where you want to copy a succession of records that are TOGETHER one-after-the-other.

A final note: The routine does not require that you activate this from Column A – i.e. you can be in any column – but the data copied with still be from A to Z.

I expect this is what you would prefer, so as mentioned, email me and I'll attach the file via return email.

If anyone else would also like a copy, please feel free to ask.

Regards, …Dale Watson dwatson@bsi.gov.mb.ca

 

[SkipVought]

Hi,
This is comparatively easy.

1. In a new module enter this code...

Sub CopyRow()
    With ActiveCell
        iFirstCol = .CurrentRegion.Column
        iColCount = .CurrentRegion.Columns.Count
        Range(Cells(.Row, iFirstCol), Cells(.Row, iColCount + iFirstCol - 1)).Copy
    End With
End Sub

Running CopyRow, loads the clipboard. Then past wherever you like Skip,
metzgsk@voughtaircraft.com