moving data to different sheets

**shreya** · 10-27-2010, 03:22 AM

Hi there,
there is a question, hope i can get help. Pleaes find attached a document.

This is a sample of shipping invoice.
you can find invoice number. abc is the number

SHR is the name of place similarly DOHA, JBR

now my question is, is it possible to get all the data which belongs to
SHR JBR in one sheet
DOHA SHR in another sheet.

but please note that i would require the invoice number sepeartly.
I know it is confusing. but i have a very huge data. doing manually is very hectic.

i hope i could get a help.it is something like in the attachmnet.

**shreya** · 10-27-2010, 06:09 AM

Is there a chance or should i do it manually??? can you please suggest. This is bit urgent.

**JBeaucaire** · 10-27-2010, 12:04 PM

Assuming there are only two sheets to filter to as you demonstrated, run this macro:

Option Explicit

Sub ParseSpecial()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      10/27/2010
Dim RNG As Range, SubRNG As Range
Dim a As Long, sh As Worksheet
Application.ScreenUpdating = False

'Option to clear destination sheets so all data is fresh
    If MsgBox("Clear destination sheets?" & vbLf & vbLf & _
        "YES - all data cleared and new data added from this sheet" & vbLf & _
        "NO  - data from this sheet added to the bottom of existing data", _
        vbYesNo, "Clear Data?") = vbYes Then
        
        For Each sh In Sheets(Array("SHR - JBR", "DOHA - SHR"))
            sh.Cells.Clear
        Next sh
    End If
        
'insert row to separate data from headers
    Rows(4).Insert xlShiftDown
'Make sure autofilter isn't on already
    ActiveSheet.AutoFilterMode = False
'set the copy range
    Set RNG = Range("A4:A" & Rows.Count).SpecialCells(xlConstants)

'Loop through each invoice as a section
    For a = 1 To RNG.Areas.Count
        With Rows(RNG.Areas(a).Cells(2, 1).Row)
          'turn on autofilter in this invoice
            .AutoFilter
          
          'filter for SHR and JBR
            .AutoFilter Field:=3, Criteria1:="SHR"
            .AutoFilter Field:=4, Criteria1:="JBR"
          'Copy to SHR - JBR sheet
            If RNG.Areas(a).Cells(1).End(xlDown).Row > .Row Then _
                .CurrentRegion.Copy Sheets("SHR - JBR").Range("A" & Rows.Count).End(xlUp).Offset(2)
                
          'filter for DOHA and SHR
            ActiveSheet.ShowAllData
            .AutoFilter Field:=3, Criteria1:="DOHA"
            .AutoFilter Field:=4, Criteria1:="SHR"
          'Copy to DOHA - SHR sheet
            If RNG.Areas(a).Cells(1).End(xlDown).Row > .Row Then _
                .CurrentRegion.Copy Sheets("DOHA - SHR").Range("A" & Rows.Count).End(xlUp).Offset(2)
                
          'turn off autfilter in this invoice
            .AutoFilter
        End With
    Next a
    
Rows(4).Delete xlShiftUp
Application.ScreenUpdating = True
Beep
End Sub

I corrected the spelling of your DOHA - SHR sheet so that it is consistent.
I've added the macro to your sample workbook and attached it to the button seen.

**shreya** · 10-28-2010, 04:05 AM

Hi, Thanks for this. but not sure why it is not working in the file that i sent.

I have a huge data and it was only the sample that i created.
Original file contains with different locations aswell.

is it possible to do some changes please.
.

**JBeaucaire** · 10-28-2010, 09:47 AM

Yes, it is possible.

The macro works on the sheet I uploaded which is a corrected version of your sheet. Remember I said your original file had a typo in the third sheet name.

The method shown in that macro can easily be expanded to add more sections once you understand the code provided.

Try the code on my sheet and ask questions about that which you don't understand, we should be able to get you to the point you can add more "sections" to the code to handle the other sheets you didn't represent.