copy cells from one worksheet and to another

[Cruizer67]

How can I copy certain cells (one row of 5 cells) to another worksheet and add it to the first blank line ? i would like to be able to run this at the click of a button that I have alreay set up... Many sheets will add to one master file

I've been searching other posts before i posted myself so that I might not bother anyone...

any help?? thanks !

 

[Tazguy37]

This will copy cells starting at the active cell, and the next 4 columns over to the first blank row on Sheet2 (referencing Column A to determine the last row). If you have data in other columns that goes down further, use that column.

Cells(ActiveCell.Row, ActiveCell.Column).Resize(, 5).Copy Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)

Hope that helps! If you have questions, post back.

 

[Cruizer67]

I was at work when I got the reply to my post however.. now that I'
m home the post doesn't show the code? Any idea how I can see it?

Thanks

 

[Cruizer67]

So.. I'm trying to work my own macro by recording it.. so far i can get it to copy and paste.. however i would like it to paste to the next avaiable row in sheet 2.. any idea how I can accomplish this

Here's the macro I have so far...

Sub Macro1()
'
'copy and paste
'

Sheet1.Select
Range("A1:E1").Select
Selection.Copy
Sheet2.Select
Range("A1:E1").Select
Sheet2.Paste
Sheet1.Select
Application.CutCopyMode = False
End Sub


any help??

 

[Cruizer67]

TazGuy

I tried your code.. works perfectly. however i wouold not like to have the cell active for it to work... any way aroung that as your code is much smaller.. maybe i can edit and combine the two?

Is it possible?

thanks in advance

 

[Cruizer67]

I got it... wasn't sure of the wording but i did a little trial and error...

thanks for the help guys....



Sub Macro1()
'
'copy and paste
'

Sheet1.Select
Range("A1:E1").Select
Selection.Copy
Sheet2.Select
Cells(ActiveCell.Row, ActiveCell.Column).Resize(, 5).Copy Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)
Sheet1.Select
Application.CutCopyMode = False
End Sub

 

[Cruizer67]

OK.. I guess i still don't know... still trying though.. here's what i have so far.. i can copy and paste from one worksheet to another .. but i can't get it to move to the next row and input there next time it goes to input info.. any help here?

Sub Macro1()
'
'copy and paste
'

Sheet1.Select
Range("A1:F1").Select
Selection.Copy
Sheet2.Select
NextRow = Sheets("Sheet2").Range("A65536").End(xlUp).Offset(1).Row
Selection.PasteSpecial Paste:=xlPasteValues
Sheet1.Select
Application.CutCopyMode = False
End Sub