Excel Copy Row Using VBA (1 Viewer)

DanG

Registered User.
Local time
Today, 11:19
Joined
Nov 4, 2004
Messages
477
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

Registered User.
Local time
Today, 18:19
Joined
Oct 5, 2004
Messages
749
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

Registered User.
Local time
Today, 11:19
Joined
Nov 4, 2004
Messages
477
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

Registered User.
Local time
Today, 18:19
Joined
Oct 5, 2004
Messages
749
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

Retired
Local time
Today, 18:19
Joined
Jun 2, 2003
Messages
12,701
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

New member
Local time
Today, 19:19
Joined
Dec 21, 2005
Messages
5
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

Retired
Local time
Today, 18:19
Joined
Jun 2, 2003
Messages
12,701
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:D

Brian
 

FAB4X

New member
Local time
Today, 14:19
Joined
Jun 15, 2007
Messages
1
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

Retired
Local time
Today, 18:19
Joined
Jun 2, 2003
Messages
12,701
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

New member
Local time
Today, 18:19
Joined
Jan 28, 2012
Messages
1
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
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

New member
Local time
Today, 11:19
Joined
Jan 31, 2012
Messages
1
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

Banned
Local time
Today, 11:19
Joined
Jan 18, 2012
Messages
2
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
 

tehNellie

Registered User.
Local time
Today, 18:19
Joined
Apr 3, 2007
Messages
751
Because he's a spammer trying to build up a valid post count by quoting legitimate posts having removed the quote tags
 

slim0705

New member
Local time
Today, 14:19
Joined
Apr 16, 2014
Messages
3
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
 
Last edited:

Brianwarnock

Retired
Local time
Today, 18:19
Joined
Jun 2, 2003
Messages
12,701
No attachments , you have only 1 post so the attachment will need to be a zip file.

Brian
 

slim0705

New member
Local time
Today, 14:19
Joined
Apr 16, 2014
Messages
3
Thanks Brian,

I have attached the file.
 

Attachments

  • 10 April Alphabetized.zip
    15.6 KB · Views: 168

Brianwarnock

Retired
Local time
Today, 18:19
Joined
Jun 2, 2003
Messages
12,701
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

New member
Local time
Today, 14:19
Joined
Apr 16, 2014
Messages
3
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
 

Attachments

  • 10 April Alphabetized.zip
    18.9 KB · Views: 156

Brianwarnock

Retired
Local time
Today, 18:19
Joined
Jun 2, 2003
Messages
12,701
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
 

Users who are viewing this thread

Top Bottom