copy cells from one worksheet and to another

Cruizer67

New Member
Joined
Jan 17, 2005
Messages
31
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 !
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Tazguy37

MrExcel MVP
Joined
May 28, 2004
Messages
4,237
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.
Code:
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

New Member
Joined
Jan 17, 2005
Messages
31
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

New Member
Joined
Jan 17, 2005
Messages
31
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

New Member
Joined
Jan 17, 2005
Messages
31

ADVERTISEMENT

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

New Member
Joined
Jan 17, 2005
Messages
31
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

New Member
Joined
Jan 17, 2005
Messages
31
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
 

Forum statistics

Threads
1,147,848
Messages
5,743,525
Members
423,801
Latest member
paulj4177

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top