PDA

View Full Version : Solved: Copy and Paste row to another sheet



Aussiebear
02-22-2006, 05:12 AM
I need assistance in creating some code to complete the follow if possible.

The workbook contains two sheets, and when you open it you will notice that in row 2 of the Whiteboard sheet the value in U2 is "Cleared". I would like to use that as the trigger to call the macro, rather than having to manually activate the macro, as the below code makes me do.

Sub Cleared()


' Copy and Paste a "Cleared " row to the last row in sheet two
Range("A2:U2").Select

Selection.Copy
Sheets("Bucket History").Select
Range("A2").Select
ActiveSheet.Paste
Sheets("Whiteboard").Select
Application.CutCopyMode = False

' Reset the default values in the row just copied


Range('B2:G2").Select


Selection.ClearContents
Range("G2").Select
ActiveCell.FormulaR1C1 = "0"
Range("I2").Select
Selection.ClearContents
Range("K2").Select
Selection.ClearContents
Range("M2").Select
Selection.ClearContents
Range("O2").Select
Selection.ClearContents
Range("Q2").Select
Selection.ClearContents
Range("S2:U2").Select
Selection.ClearContents



End Sub This may be a long way around carrying out this proceedure but I'm using baby steps to learn. In summing up, what ever row I'm in at the time, if I type the value "Cleared" in the U column of that row I'd like the above macro to operate. So that it ends up looking like row 10, with the formulas still intact

XLGibbs
02-22-2006, 07:03 AM
Hi aussiebear,

You don't need to individual Select ...Selection all those areas..






' Copy and Paste a "Cleared " row to the last row in sheet two
Range("A2:U2").Copy


Sheets("Bucket History").Range("A2").PasteSpecial xlPasteValues

Application.CutCopyMode = False


' Reset the default values in the row just copied
ActiveSheet.Range('B2:G2","I2""K2","M2","O2","Q2","S2:U2").ClearContents


Range("G2") = "0"


End Sub




To make this more dynamic as to the next blank row:


Dim lRow as Long

With Sheets("Bucket History")
lrow = .Cells(.rows.count,1).End(xlup).Offset(1).Row
End With




' Copy and Paste a "Cleared " row to the last row in sheet two
ActiveSheet.Range("A2:U2").Copy


Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False


' Reset the default values in the row just copied






Range("B2:G2","I2""K2","M2","O2","Q2","S2:U2").ClearContents


Range("G2") = "0"


End Sub




to make this happen when you type cleared in cell U2 of activesheet

Private Sub Worksheet_Change(byVal Target as Range)
If Target.Count > 1 then exit sub
If Target.Address = "$U$2" and Target = "Cleared" then

ActiveSheet.Range("A2:U2").Copy
Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

ActiveSheet.Range("A2:U2").Copy
Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False

' Reset the default values in the row just copied
Range('B2:G2","I2""K2","M2","O2","Q2","S2:U2").ClearContents
Range("G2") = "0"

End If

End Sub

Hope that helps you out!

Pete

Note: Edited a couple typos...

Aussiebear
02-23-2006, 01:03 AM
Hi aussiebear,

You don't need to individual Select ...Selection all those areas..

Okay, got this bit....




To make this more dynamic as to the next blank row:


Dim lRow as Long

With Sheets("Bucket History")
lrow = .Cells(.rows.count,1).End(xlup).Offset(1).Row
End With
' Copy and Paste a "Cleared " row to the last row in sheet two
ActiveSheet.Range("A2:U2").Copy
Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False
' Reset the default values in the row just copied
Range("B2:G2","I2""K2","M2","O2","Q2","S2:U2").ClearContents
Range("G2") = "0"
End Sub


Error at the Range("B2:G2" line telling me too many arguments... then this one.... There appears to be too many ActiveSheet.Range.... Am I correct here?


to make this happen when you type cleared in cell U2 of activesheet

Private Sub Worksheet_Change(byVal Target as Range)
If Target.Count > 1 then exit sub
If Target.Address = "$U$2" and Target = "Cleared" then

ActiveSheet.Range("A2:U2").Copy
Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

ActiveSheet.Range("A2:U2").Copy
Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False

' Reset the default values in the row just copied Range('B2:G2","I2""K2","M2","O2","Q2","S2:U2").ClearContents
Range("G2") = "0"

End If

End Sub


Why is it that in the original post by Pete that the ' Reset the default values line is continued to include the Range("B2:G2...." line but when you requote it it shows up as in the correct order?

So in baby steps once again please. Remember the aim here is to allow the user to clear the active row once the value "Cleared" is entered in the cell of the Column U. http://vbaexpress.com/forum/images/smilies/banghead.gif

Ted

Aussiebear
02-23-2006, 06:33 AM
to make this happen when you type cleared in cell U2 of activesheet


Private Sub Worksheet_Change(byVal Target as Range)
If Target.Count > 1 then exit sub
If Target.Address = "$U$2" and Target = "Cleared" then

ActiveSheet.Range("A2:U2").Copy
Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

ActiveSheet.Range("A2:U2").Copy
Sheets("Bucket History").Range("A" & lRow).PasteSpecial xlPasteValues

Application.CutCopyMode = False

' Reset the default values in the row just copied Range('B2:G2","I2""K2","M2","O2","Q2","S2:U2").ClearContents
Range("G2") = "0"

End If

End Sub



I have deleted the repeated lines and changed the Range ("B2:G2......") statement to ActiveSheet.Range("B2:G2...") and the error message "invalid number of arguments"... disappeared.

Saved and ran the proceedure and its looking good, no error messages, no yellow highlights etc like before. Raced out and grabbed another beer, plonked down in front of the spreadsheet and.... nothing happening.

Righto I said, another look at the code. Back to spreadsheet, yep no other values in the Column U on the spreadsheet, so it can't be the line If Target.Count > 1 Then exit Sub. Have another go..... still nothing. Grab another beer. ( and one for ron, just in case I get excited and try to do the whole spreadsheet)

Back to the Code, hmmmm, are we locking in the Target.Address As ("$U$2")..... when we want any active row with the value of "Cleared"? Maybe, no don't change that bit, as Pete knows what he's doing, which is a damn side more than I do.

Only one thing to do, get another beer and wait.http://vbaexpress.com/forum/images/smilies/beerchug.gif

XLGibbs
02-23-2006, 07:17 AM
Hey Aussiebear...my post go way ugly there, seems some stuff got out of whack..

Where is Cleared getting typed in column U row 2? or any row in Column U?

This will handle cleared typed in any row column U, below row 1 and copy Ax:Ux to the Bucket History.

Please note the cells you indicated for clearing are not contiquous...

tested and works. Sorry about the plethora of bad syntax provided before. Maybe i should have some of that beer!


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False

If Target.Column = 21 And Target.Row > 1 And Target = "Cleared" Then

lRow = Sheets("Bucket History").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With ActiveSheet

.Range(.Cells(Target.Row, 1), .Cells(Target.Row, 21)).Copy
With Sheets("Bucket History")
.Range(.Cells(lRow, 1), .Cells(lRow, 21)).PasteSpecial xlValues
' Reset the default values in the row just copied
End With


.Range("B2:G2, H2, K2, M2, O2, Q2, S2:U2").ClearContents

.Range("G2") = "0"
Application.EnableEvents = True

End With
End If

End Sub

Aussiebear
02-23-2006, 12:11 PM
As I raise my eyes to the monitor I find my keyboard and I are surrounded by an army of bottles... I'm about to yell Holler for the Marshall when the mist begins to clear....

Its XLGibbs leading the charge. I'm saved.

Thankyou for you assistance.
Ted

XLGibbs
02-23-2006, 04:20 PM
LOL, you might want a breath mint to avoid giving him any additional reasons....

Based on your PM that said all was working well, marking this solved for ya.

Pete

Aussiebear
02-24-2006, 02:16 AM
Just a few hours too early it seems... took the code to work and... have come home with my tail between my legs. Didn't want to work one iota.

Holler for the Marshall!!!!!!

XLGibbs
02-24-2006, 07:04 AM
Make sure you pasted it in the Worksheet module for the worksheet you want it to to work for...specifically the worksheet where you want to type "cleared" in column U.

Make sure the sheet names are correct for the workbook/worksheets you are using at work.

Aussiebear
02-24-2006, 02:49 PM
Ok. Sheet names are correct, the format and data type are correct, its just the actual data that's different. Will have another go on Monday.

Alt F11, and right click the worksheet in which you want the code to work with, click view code and paste into, save, and then exit.... right?

Thanks once again

XLGibbs
02-24-2006, 06:37 PM
For the benefit of those who may be searching...some problems with the original solution posted..


Private Sub Worksheet_Change(ByVal Target As Range)
' Copy and paste "Cleared" row to Bucket History sheet
If Target.Cells.Count > 1 Then Exit Sub
Application.ScreenUpdating = False
Application.EnableEvents = False
If Target.Column = 21 And Target.Row > 1 And Target = "Cleared" Then
lRow = Sheets("Bucket History").Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
With ActiveSheet
.Range(.Cells(Target.Row, 1), .Cells(Target.Row, 21)).Copy
With Sheets("Bucket History")
.Range(.Cells(lRow, 1), .Cells(lRow, 21)).PasteSpecial xlValues
.Range(.Cells(lRow - 1, 1), .Cells(lRow - 1, 21)).Copy
.Range(.Cells(lRow, 1), .Cells(lRow, 21)).PasteSpecial xlFormats
.Cells(lRow, 20) = Format(Now(), "dd/mm/yy")

End With
' Reset the default values for the row just copied
cRow = Target.Row
.Range("B" & cRow & ":G" & cRow & ",I" & cRow & ",K" & cRow & _
",M" & cRow & ",O" & cRow & ",Q" & cRow & ",S" & cRow & ":U" & cRow).ClearContents
.Range("G" & cRow) = "0"
Application.EnableEvents = True

End With
End If
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub



This will copy the target row where cleared is typed into the other sheet...copy the formatting down the destination sheet to the new row... and tag the appropriate column with the date cleared, then clear the row from sheet1 where it was copied from

Aussiebear
02-25-2006, 04:31 AM
This latest version works perfectly. I would like to thank Pete for his patience with my request and for working on this issue.

Ted

Aussiebear
02-28-2006, 02:43 AM
Just a queiry here, On the first page I normally place a date in column T to indicate the date, the data was considered to be cleared. With the new code it automaticially creates a code when pasting the copied row into the second sheet. There must be some sort of conflict as it tries to display a date for the year 1967. If I don't manually indicate the date on the first page, no issues occur.

So I ask if I don't need to manually indicate a date myself, can I afford to delete Column T and if I were to do so how do I redefine the code so that I have a column T in sheet two. The very simple solution is to put up with a blank column T in sheet 1 but it would look odd.

Ted

XLGibbs
02-28-2006, 10:19 AM
Take out this line to remove the "automatic" data in column T..

.cells(lRow,20) = Format(now(),"dd/mm/yy")

if you delete that line, it will take the date that you enter in column T.

Pete