Need help copy data from shee1 to sheet2

[muzzy]

I am looking for a Formulas or VBA to copy this to sheet 2. If I have this.

Assigned Role	Assigned
1-2-8-26-1-1-11	ARTHUR
1-2-8-26-1-1-11	CHARLOTTE
	Subtotal
1-2-8-26-1-2-13	MARSHALL
1-2-8-26-1-2-13	MATTHEW
1-2-8-26-1-2-13	PASCAL
	Subtotal
1-2-8-26-1-1-12	ABRIL
1-2-8-26-1-1-12	BRIA
1-2-8-26-1-1-12	DIANNA
	Subtotal
1-2-8-26-2-2-12	JERMELL
	Subtotal

<colgroup><col><col></colgroup><tbody>
</tbody>

But this is what I need I need the Subtotal to be removed so it will look like this.

Assigned Role	Assigned
1-2-8-26-1-1-11	ARTHUR
1-2-8-26-1-1-11	CHARLOTTE
1-2-8-26-1-2-13	MARSHALL
1-2-8-26-1-2-13	MATTHEW
1-2-8-26-1-2-13	PASCAL
1-2-8-26-1-1-12	ABRIL
1-2-8-26-1-1-12	BRIA
1-2-8-26-1-1-12	DIANNA
1-2-8-26-2-2-12	JERMELL

<colgroup><col><col></colgroup><tbody>
</tbody>

Please can someone help thanks

 

[syedalisajjad]

ws_src is the sheet from which you want to copy data i.e. source sheet
ws_dst is the sheet to which you want to copy data to i.e. destination sheet

so please replace the names and it should work.

 

[muzzy]

I did what you said and it works on the destination sheet good but it also does the same thing on the source sheet.

 

[mole999]

if you step through with F8, I think the work is being done on the main page and then being copied over

where as I think you know want to keep the first page with the erroneous formatting

in which case the copy to the other sheet has to be done first and then you do the work there

 

[syedalisajjad]

What do you actually want?

Do you want to keep the source sheet as it is and apply formatting (i.e. deleting rows which contain Subtotal) to the destination sheet?

 

[muzzy]

Yes that is what I need Keep the Source sheet as it is. Thanks

 

[syedalisajjad]

Ok, i will post the code tomorrow

 

[syedalisajjad]

Hello Muzzy,

here is the code:

Private Sub btnExport_Click()
Dim ws_src As Worksheet, ws_dst As Worksheet
Dim wb_src As Workbook


Set wb_src = ThisWorkbook
Set ws_src = wb_src.Sheets(1)
Set ws_dst = wb_src.Sheets(2)


Dim subTotal As String


subTotal = "Subtotal"


'Get the last filled row number
LastDataRow = ws_src.Range("A" & ws_src.Rows.Count).End(xlUp).Row
rngToCheck = "B2:B" & LastDataRow


rangeToCopy = "A1:B" & LastDataRow
ws_src.Range(rangeToCopy).Copy
ws_dst.Range(rangeToCopy).PasteSpecial

For counter = 2 To LastDataRow
deleteRowRng = "B" & counter

If ws_dst.Range(deleteRowRng).Value = "Subtotal" Then
ws_dst.Rows(counter).Delete
End If
Next

End Sub

 

[muzzy]

Thanks it work great