Selecting Cell based on a criteria and copy selected rowClosed

Question

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









Configuration: Windows 7 / Internet Explorer 9.0

TrowaD · Answer

Here you go Debs: Sub RunMe()Dim ws As WorksheetFor 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 IfNext wsFor 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 IfNext wsEnd 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.

Debs269 · Answer

Thanks TrowD for your response..

All seem fine until I run 

Comes up with "subscript out of range"

All looks ok, so not sure what I have done..

BTW thank you so much for looking at this today, really appreciate the help.

Many Thanks
Debs

Debs269 · Answer

On Line 

    If ws.Name <> "DataMaster" Then
        
        Sheets(ws.name).Select

        For Each Cell In Range("C12:C" & Cells(Rows.Count, "C").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

I changed ws.name to WhiteHIllMaster .. assumed that was correct

Selecting Cell based on a criteria and copy selected row

3 replies

Similar discussions

Subscribe To Our Newsletter!