Copy a selected range of data

[SeyyaH]

It's been long to write in english for that reason i hope you could understand me. There is an excel file contain 1 sheet which name is "data". in data sheet there are 6 column. One of this column is "district" names column. i want to write a code and built new sheets which names are district names and contain data what each district has.

[stevebriz]

Please email me an I will send the file that does as you requested.
I is too big to attach here.
to make the district sheets go Tools> copy to district sheets.
to delete the district sheets go to Tools> delete district sheets.

[protonLeah]

Try the following code:

Option Explicit 
Sub Distributor() 
          Dim DATA_SHEET As Worksheet 
          Dim sDistrictName As String, sTempName As String, COPY_AFTER_SHEET As String 
          Dim rStartRow As Range, rEndRow As Range 
          Dim iStartRow As Integer, iEndRow As Integer, LCount As Integer, NextDistrict_Pointer As Integer 
          '-----------------------------------------------------------------------------------------' 
          Set DATA_SHEET = Sheets("Data") 
           
          DATA_SHEET.Select 
          DATA_SHEET.Range("B2").Select 
          NextDistrict_Pointer = 2 
          COPY_AFTER_SHEET = "Data" 
           
          While DATA_SHEET.Range("B" & NextDistrict_Pointer).Value > "" 
                    DATA_SHEET.Select 
                    Range("B" & NextDistrict_Pointer).Select 
                    sDistrictName = ActiveCell.Value 
                    sTempName = ActiveCell.Offset(1, 0).Value 
                    LCount = 1 
                    iStartRow = ActiveCell.Row 
           
                    While sTempName = sDistrictName 
                              LCount = LCount + 1 
                              sTempName = ActiveCell.Offset(LCount, 0).Value 
                    Wend 
                    iEndRow = ActiveCell.Offset(LCount - 1, 0).Row 
                     
                    Call CreateDistrictSheet(sDistrictName, COPY_AFTER_SHEET) 
                    Call CopyData2District(sDistrictName, iStartRow, iEndRow) 
                    NextDistrict_Pointer = iEndRow + 1 
                    COPY_AFTER_SHEET = sDistrictName 
          Wend 
End Sub 
Sub CreateDistrictSheet(ByVal TabName As String, sLastSheetName As String) 
          Dim iCount As Integer 
          Dim NewSheet As Worksheet 
          Set NewSheet = Sheets.Add 
          NewSheet.Name = TabName 
          Worksheets(TabName).Range("A1") = Worksheets("Data").Range("A1").Value 
          Worksheets(TabName).Range("B1:E1").Value = Worksheets("Data").Range("C1:F1").Value 
          Worksheets(TabName).Move after:=Worksheets(sLastSheetName) 
End Sub 
Sub CopyData2District(ByVal sTHIS_DISTRICT As String, ByVal iFirstRow As Integer, ByVal iLastRow As Integer) 
          Dim Src_Row_Pointer As Integer, Dst_Col_Pointer As Integer, Dst_Row_Pointer As Integer 
          Dim THIS_SHEET As Worksheet, SRC_DATA_SHEET As Worksheet 
           
          Set SRC_DATA_SHEET = Worksheets("Data") 
          Set THIS_SHEET = Worksheets(sTHIS_DISTRICT) 
          Dst_Row_Pointer = 2 
           
          With THIS_SHEET 
                    For Src_Row_Pointer = iFirstRow To iLastRow 
                              .Range("A" & Dst_Row_Pointer).Value = SRC_DATA_SHEET.Range("A" & Src_Row_Pointer).Value 
                              For Dst_Col_Pointer = 2 To 5 
                                        .Cells(Dst_Row_Pointer, Dst_Col_Pointer).Value = SRC_DATA_SHEET.Cells(Src_Row_Pointer, Dst_Col_Pointer + 1).Value 
                              Next Dst_Col_Pointer 
                              Dst_Row_Pointer = Dst_Row_Pointer + 1 
                    Next Src_Row_Pointer 
          End With 'THIS_SHEET' 
End Sub  

[Aika]

Hi! Maybe you could help me too... I'm really new to all macro-stuff and besides I try to learn it myself.

I want to do a similar thing, but i dont want to make new sheets with data but move, let's say, columns that correspond one of the districts to one another sheet where I choose which one (district) I am interested in.

Is it a bad explanation? (sorry for my English as well)
I can tell more, but don't want to add too much information that maybe isn't useful.

Thanks if someone has time to look at it!

[protonLeah]

I need more specific information. As I understand you so far, you do not want to create any new sheets; but, you want to copy to an existing sheet. How is the destination sheet determined? Is there something about the value of the data that tells where it should go?

[Aika]

Thanks for your interest! I found a temporary solution, but it is not the smartest one, so I am still interested in advise.

To ansver your question, I want to copy to the sheet from which macro was started (it starts by button "go!"). There are two possible sheets, by the way, and they don't differ a lot. Lets say, I want to copy info from column B, C and D to Sheet1 and B, C and E to Sheet2.

Information that I want to copy is located at one another sheet, lets call it a List (in the same file). Column A has about 20 unik values, B and C have only unik values and D and E has some number values like, for example, price or size. There are about 400 rows (not too many )

In (or on?) Sheet1 or 2 I have a choice of this 20 unik vaues in the dropdown list and a button "go!". "Go" should bring me the named columns from the List that correspond to this value.

Hope it is more clear now.
And I'm sorry if the things I ask are pretty simple, I just started to figure it myself and they are not simple for me yet.