Copy the whole rows if certain text exit

Fable09

New Member
Joined
Nov 15, 2014
Messages
36
Hi every one,

I have one Excel sheet like this

CustomerLocationSale
Acity X2
Bcity X4
Ccity Y5
Dcity Y7

<tbody>
</tbody>


Now if I want to make two other separate sheet in the same Excel file, instead of use copy paste, is there any VBA code can extracting data base on city from sheet 1 so that

Sheet 2 will be

CustomerLocationSale
Acity X2
Bcity X4

<tbody>
</tbody>

Sheet 3 will be

CustomerCitySale
Ccity Y5
Dcity Y7

<tbody>
</tbody>


Thanks,
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi Fable09,

Are there ever cities with just one sale, and if so do you want to break them out into their own sheet. Additionally, do you want to rename the sheets to the City name or keep them as Sheet2, Sheet3, etc.

igold
 
Upvote 0
Hi Fable09,

Are there ever cities with just one sale, and if so do you want to break them out into their own sheet. Additionally, do you want to rename the sheets to the City name or keep them as Sheet2, Sheet3, etc.

igold


Dear igold, the Location have 0,1,2,... etc any number but Sheet 2 is designated for City X

Sheet 3 is designated for City y. i.e these sheet already created but totally blank and don't need to rename it. It just copy Data related to City X to sheet 2 and City Y to Sheet 3.

Hence if City X have no Sale, then it still blank, if have 1 sale then data need to be transfer to Sheet 2 when the Macro run.

Thanks,
 
Upvote 0
Hi Fable09,

This should do what you want. This code will rewrite the information on Sheets 2 & 3 each time it is run. This is to prevent the same information from being written repetitively every time you run the macro. Therefore any information that is in Columns A,B,C on sheets 2 & 3 will be deleted, if you have any totals calculations, put them in Column D or more. Remember to test this code on a back-up copy of your data. This code will delete data and it is not recoverable!

Code:
Sub CitySalesData()

    Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet
    Dim city As String
    Dim lRow As Long, lXRow As Long, lYRow As Long, i As Long
    
    Application.ScreenUpdating = False
    Set ws1 = Worksheets("Sheet1")
    Set ws2 = Worksheets("Sheet2")
    Set ws3 = Worksheets("Sheet3")
    ws1.Activate
    lRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row
    ws2.Range("A2:C" & Rows.Count).ClearContents
    ws3.Range("A2:C" & Rows.Count).ClearContents
    
    For i = 2 To lRow
        city = ws1.Cells(i, 2).Value
        
        Select Case city
    
            Case Is = "city X"
                lXRow = ws2.Cells(Rows.Count, 1).End(xlUp).Row + 1
                ws1.Range(Cells(i, 1), Cells(i, 4)).Copy ws2.Cells(lXRow, 1)
        
            Case Is = "city Y"
                lYRow = ws3.Cells(Rows.Count, 1).End(xlUp).Row + 1
                ws1.Range(Cells(i, 1), Cells(i, 4)).Copy ws3.Cells(lYRow, 1)
        
        End Select
    Next
    Application.ScreenUpdating = True
End Sub


HTH

igold
 
Upvote 0

Forum statistics

Threads
1,213,514
Messages
6,114,078
Members
448,547
Latest member
arndtea

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top