Selecting Cell based on a criteria and copy selected row

Closed
Debs269 Posts 23 Registration date Monday October 15, 2012 Status Member Last seen August 18, 2016 - Feb 18, 2014 at 04:37 AM
Debs269 Posts 23 Registration date Monday October 15, 2012 Status Member Last seen August 18, 2016 - Apr 1, 2014 at 04:21 AM
Hello,


I am working on VBA for the following

Selecting cell based on criteria and the copying the selected row to another sheet. Then deleting the orginal row from master sheet.


The criteria is "Completed on System" and information in column B (starting at row12).
The Row is A:AC

There are three seperate sheets I need to identify the data on
"WhiteHillMaster" "GloryParkMaster" and WentworthMaster"

need to identify all the "Completed on System" on each sheet


Rows being copied to "DataMaster"

The script needs to find the next avaialble row in the "DataMaster" sheet

Copy the values and formats (not the Forumlas)

Then delete the row from the original sheet


Can anyone advise...please

Many Thanks in advance









3 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Feb 18, 2014 at 11:07 AM
Here you go Debs:
Sub RunMe()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "DataMaster" Then
Sheets(ws.Name).Select
For Each cell In Range("B12:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If cell.Value = "Completed on System" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "AC")).Copy
Sheets("DataMaster").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Sheets("DataMaster").Range("A" & Rows.Count).End(xlUp).PasteSpecial Paste:=xlPasteFormats
End If
Next cell
End If
Next ws

For Each ws In Worksheets
If ws.Name <> "DataMaster" Then
Sheets(ws.Name).Select
For Each cell In Range("B12:B" & Cells(Rows.Count, "B").End(xlUp).Row)
If cell.Value = "Completed on System" Then
Range(Cells(cell.Row, "A"), Cells(cell.Row, "AC")).Delete
End If
Next cell
End If
Next ws

End Sub

It's not the shortest structure but it will do the job.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0