Solved: copy entire row to new sheet along with header

[satish gubbi]

Hi,

Can you please help me in getting a macro that would copy entire row if cells in column A has a value called "GB123" along with header to new sheet called "GB123"

Row 1 will have headers

There will be only one sheet in workbook with the name DATA out of which GB123 to be created and seperate the GB123 out of it.

kindly help me.

[sassora]

Hi

It sounds like the best way to deal with this is to avoid a macro and use a filter.

[Simon Lloyd]

This should do what you needSub Copy_It()
Dim Rng As Range, Mycell As Range
With Sheets("DATA")
Set Rng = .Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row)
If Application.WorksheetFunction.CountIf(Rng, "GB123") > 0 Then
Sheets.Add after:=Sheets(Sheets.Count)
ActiveSheet.Name = "GB123"
.Rows(1).Copy Destination:=ActiveSheet.Range("A1")
For Each Mycell In Rng
If Mycell.Value = "GB123" Then
Mycell.EntireRow.Copy Destination:=ActiveSheet.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Mycell
End If
End With
End Sub

[snb]

Like Sassora said:

Sub M_snb()
if iserror(evaluate("GB123!A1")) then sheets.add.name="GB123"

with sheets("Data").columns(1)
.autofilter 1, "GB123"
.copy sheets("GB123").cells(1)
.autofilter
end with
end sub

[satish gubbi]

Hi Simon,

thank you very much for your help, code is working as intended.

[satish gubbi]

Hi Snb,

Thank your very much for your help, however code is copying particular cell, my requirement was to copy entire row to next sheet.

regards.

[snb]

You should have adapted the code

Sub M_snb()
If iserror(evaluate("GB123!A1")) Then sheets.add.name="GB123"

With sheets("Data").columns(1)
.autofilter 1, "GB123"
.entirerow.copy sheets("GB123").cells(1)
.autofilter
End With
End Sub

[satish gubbi]

Thank you very much Snb, code is working as intended.