Solved: move records

[Shane]

Hi,

I have made a macro which moves the records from sheet1 (weeks) to sheet2(YTD_QC) basis the column value weeks!E. If the column value is "Done" or "Delayed" then only the entire record copy and pasted in SHeet2(Ytd_qc) as first record.

The below record does the same however the only issue is when it pasted the record in (YTD_QC) is deleted the first record of YTD_QC which i don't want.

[VBA]
Sub Move()
Dim cl As Range
Dim rng As Range
Dim NextCl As Range, rngOutput As Range
Dim ShtName As String
Dim Del As Worksheet, Don As Worksheet
Set Del = ThisWorkbook.Sheets("YTD_QC")


Worksheets("weeks").Activate
Set rng = Worksheets("Weeks").Range(Cells(5, 5), Cells(Rows.Count, 1).End(xlUp))
For Each cl In rng
ShtName = cl.Value
If cl.Value = "done" Or cl.Value = "delayed" Then 'Or cl.Value = "Presentation" Then
' NextCl = Worksheets("YTD_QC").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
cl.EntireRow.Copy
Worksheets("YTD_QC").Activate
Set rngOutput = Worksheets("YTD_QC").Range("A4")
ActiveCell.EntireRow.Insert shift:=xlDown
rngOutput.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End If
Next cl

End Sub
[/VBA]
Please help

[mdmackillop]

Add this line to show the range you are working with. I think that will demonstrate your problem.
[VBA]
Set rng = Worksheets("Weeks").Range(Cells(5, 5), Cells(Rows.Count, 1).End(xlUp))
rng.Interior.ColorIndex = 6
[/VBA]

[Shane]

Hi,

I am facing an issue with second sheet (YTD_DC). If you see that sheet as of now it contains2 records. When I run the above macro what it will do is it will pick all the four records available in SHeet1 (Weeks) and paste it on to sheet2 (YTD_QC). Only issue is in YTD_QC where we have 2 records one of the record will get deleted that i don't want.

Basis the matching criteria if three records (Weeks sheet) contains "Done" or "Delayed" then those records get pasted in Sheet2(YTD_QC). However the records which are already there in Sheet2 will remain as is. So lets say if we have 3 matching records in sheet1 then after executing macro sheet2 contains total 5 records.

Thanks for your help in Advance. The previous attachment contains the macro as well.

Regards,
Shane

[Shane]

Hi mdmackillop,

Please help...


Regards,
Shane

[Bob Phillips]

[vba]

Sub Move()
Dim cl As Range
Dim rng As Range

Set Del = ThisWorkbook.Sheets("YTD_QC")

Worksheets("weeks").Activate
Set rng = Worksheets("Weeks").Range(Cells(5, 5), Cells(Rows.Count, 1).End(xlUp))
For Each cl In rng
ShtName = cl.Value
If cl.Value = "done" Or cl.Value = "delayed" Then 'Or cl.Value = "Presentation" Then
' NextCl = Worksheets("YTD_QC").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
Worksheets("YTD_QC").Rows(4).Insert
cl.EntireRow.Copy Worksheets("YTD_QC").Range("A4")
End If
Next cl

End Sub
[/vba]

[Shane]

Thank you so much XLD..


Regards,

Shane