Excel Copy Row Using VBA

DanG · Dec 9, 2005

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!

reclusivemonkey · Dec 9, 2005

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.

DanG · Dec 9, 2005

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.

reclusivemonkey · Dec 9, 2005

DanG said:
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

Brianwarnock · Dec 20, 2005

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

Code:

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

Code:

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

Pieter · Dec 21, 2005

copy multiple rows

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

Brianwarnock · Dec 23, 2005

Code:

x = ActiveCell.Row

Didn;t know you could do that . it's neat

Code:

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

Brian

FAB4X · Jun 15, 2007

Macro to copy row after row

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

Brianwarnock · Jun 15, 2007

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

CyberPath · Jan 28, 2012

Hello everyone, ...

(And especially to those who take the trouble to help me in this issue.

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

Code:

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

So that at the middle of the code ...

Code:

Range("A9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

instead 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:

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).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
End Sub

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

Code:

Range(ActiveCell.Offset(Rowoffset:=1).Address).Select
[COLOR=Red][U]ActiveSheet.Paste[/U][/COLOR]
Application.CutCopyMode = False

Can someone please help me in this little, little, little,..

Problem...
THANKs...

QuickReplier · Jan 31, 2012

CyberPath said:
Here is what a have done so far from this code:

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).Select ActiveSheet.Paste Application.CutCopyMode = False End Sub

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

Code:

Range(ActiveCell.Offset(Rowoffset:=1).Address).Select [COLOR=Red][U]ActiveSheet.Paste[/U][/COLOR] 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.

cashare1234 · Feb 14, 2012

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

Code:
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

Code:
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

Brianwarnock · Feb 14, 2012

Why have you copied a 6 year old post without comment?

Brian

tehNellie · Feb 14, 2012

Because he's a spammer trying to build up a valid post count by quoting legitimate posts having removed the quote tags

slim0705 · Apr 16, 2014

I have a question i need help with. I have two different spreadsheets that have information in them. One sheet has a master list of names and commitments (job, time,location). The other a list of certain people that may or may not be on that master list. I want to make a daily fallout list for everybody on the list. If the name listed on the second row has commitments on the first sheet copy all corresponding rows underneath that name on the second sheet. If the name is not on the master list simply input the text "NOT COMMITTED"! Any help or a starting point will be greatly appreciated. Thanks

Please see attached MS Excel sheets

Brianwarnock · Apr 16, 2014

No attachments , you have only 1 post so the attachment will need to be a zip file.

Brian

slim0705 · Apr 16, 2014

Thanks Brian,

I have attached the file.

Brianwarnock · Apr 17, 2014

Ok I think that we need to start again.

Use your Sheet names in your explanation and show a third sheet of what you expect, this should be simple to do manually for a small sample. include all situations.

Brian

slim0705 · Apr 17, 2014

Here is what I have.

Every afternoon, we get an email with the "commitments board" sheet attached. On this sheet if any members in our unit are committed it will show the job they have, times, role, etc. I want to be able to take the Master list ("Roll Call") of all the members in our unit and have a quick look to see which members are where and if I have any members to use elsewhere. In the sample I have 6 different people, 4 of which are committed. I would like to be able to get the rows (if applicable) from the "Commitments Board" copied underneath the names on the "roll call" sheet. If name does not exist on "Commitments Board" the member is not committed for that day and underneath that name it should read "NOT COMMITTED". Hopefully this all makes sense.

-Mark

Brianwarnock · Apr 18, 2014

Before I retired I would have enjoyed the challenge of writing that code but then it would have been easier for me, however I suspect that I would have still queried the wisdom of it.

The Roll Call is the fixed data and yet you are going to completely mess up the layout each day, how do you get it back?

I think that you are using a sledge hammer to crack a nut here, you have the details of the committed and all you want is a quick visual of the not committed. In column B of the Roll call I would code the following

=IF(ISNA(VLOOKUP(A2,'Commitments Board'!A:A,1,FALSE)),"not committed",A2)
Copy down
Then each time the other sheet is changed you can quickly see who is not committed.

Brian

Excel Copy Row Using VBA

Registered User.

Registered User.

Registered User.

Registered User.

Retired

New member

Retired

New member

Retired

New member

New member

Banned

Retired

Registered User.

New member

Retired

New member

Attachments

Retired

New member

Attachments

Retired

Similar threads

Users who are viewing this thread