Copy the ActiveCell row into another worksheet

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
I need the VBA code to copy the ActiveCell Row into the Worksheet called Cancellations. I need to copy the row onto a new line everytime in Cancellations.


I tried ActiveCell.copy (Cancellations!), but this would not work, and it would only copy the ActiveCell, but I want the whole row that the active cell is on to be copied. It also has to copy onto a new row in the other worksheet every time.

Thanks, Dan
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Sub CpyAct()
ActiveCell.EntireRow.Copy Destination:=Sheets("Cancellations").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub
 

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
Great, that worked fine. It takes the row into the sheet cancelled and works well. The other thing I would like is that, when it takes the data into the sheet cancelled, for it to add todays date to column P to whatever row was just added. So then It would basically say the people that have cancelled and the date they canceled at the end.

This might be abit of extra work but you seem like you can do it. So ive got it to add the active row data into the other sheet, all i'd like now is for it to also add todays date onto that row at the end aswell.

Thanks, Dan.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Try

Code:
Sub CpyAct()
ActiveCell.EntireRow.Copy Destination:=Sheets("Cancellations").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Cancellations").Range("P" & Rows.Count).End(xlUp).Value = Date
End Sub
 

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
It added the date to the sheet cancelled, and also added it to column P, but didn't add it to the correct Row Number. The date went to P1, wheras the Row of data went to Row 3. Any Ideas?

Thanks, Dan
 

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
Oh, Good News, I figured out why it wasn't working and now it works fine, I added the offset to the date aswell so that it would work. EG:


Sheets("Cancellations").Range("P" & Rows.Count).End(xlUp).Offset(1).Value = Date
</pre>
So thanks alot for helping me with this. If I need anything else (which I probably will soon), I'll ask you.

Thanks Alot, Dan
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Sorry about the (lack of) Offset - I was posting in a hurry as about to go out.

Thinking about this more, it would be possible for this to be done automatically if you double-clicked a cell (for example). Post back if you're interested in that.
 

danjames11

New Member
Joined
Apr 22, 2009
Messages
14
The way I've done it now is you click a button saying "client cancelled" and then it adds a "(Cancelled)" onto the end of there name, and then with the help of you, adds them into the cancelled clients sheet. So no, I won't need that but I wouldn't be as far as this if it wasn't for you so, thanks. I'll get in touch if I need anything else.

Dan
 

Forum statistics

Threads
1,081,726
Messages
5,360,903
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top