Off-Topic - Excel VBA to Copy Entire Row to New Tab based on Cel

Yahoo Archive
1
Hi Chris,



Right click on sheet1, select View Code and paste the following. It
will copy the row from sheet1 to sheet2 whenever you put the word order
in column g.



Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 7 Then

For Each cell In Target

If UCase(cell.Value) = "ORDER" Then

Dim ws As Worksheet

Dim NextRow As Long

Set ws = Sheets("sheet2")

With ws

NextRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1

End With

cell.EntireRow.Copy Destination:=ws.Cells(NextRow, 1)

Set ws = Nothing

End If

Next

End If

End Sub



Regards,

Linda



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Chris Thompson
Sent: Thursday, September 09, 2010 11:06 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Off-Topic - Excel VBA to Copy Entire Row to New Tab
based on Cell Value





Hi All

Does anyone know any VBAScript which will copy an entire row in Sheet 1
to the
next available row in Sheet 2 based on a cell value in column G.

So if G6 = Order then copy the entire row and paste in the next avilable
row on
Sheet 2.

I have tried a few I have found on Google, but none of them seem to work
when I
try them.

It is as if the macros are disabled - but I can't find how to enable
them in
Excel.

Thanks.

[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
2
Hi All

Does anyone know any VBAScript which will copy an entire row in Sheet 1 to the
next available row in Sheet 2 based on a cell value in column G.

So if G6 = Order then copy the entire row and paste in the next avilable row on
Sheet 2.

I have tried a few I have found on Google, but none of them seem to work when I
try them.

It is as if the macros are disabled - but I can't find how to enable them in
Excel.

Thanks.




[Non-text portions of this message have been removed]
3
This should do it, but I don't know how you plan to fire the event. Do you
want to cycle through all the rows in Sheet1 and if column G is order, copy
to sheet2?


Sub copyOrders()
'
' copyOrders Macro
'

' this assumes that the row in sheet1 is selected
Dim lastEmptyRow As Long
Sheets("Sheet1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
lastEmptyRow = Range("A1").End(xlDown).Row + 1
Range("A" & lastEmptyRow).Select
ActiveSheet.Paste
End Sub


--
Randy Weber
weber.randy@...
(651) 263-1811

http://randallweber.com/


[Non-text portions of this message have been removed]