transfer data in order

[AhmedRady]

Dear supporters,
appreciate your support to help me

i copy data from system to excel sheet (data)
[TABLE="width: 97"]
<colgroup><col></colgroup><tbody>[TR]
[TD]A - Support[/TD]
[/TR]
[TR]
[TD]A - Support[/TD]
[/TR]
[TR]
[TD]A - Support[/TD]
[/TR]
[TR]
[TD]B - Support[/TD]
[/TR]
[TR]
[TD]B - Support[/TD]
[/TR]
[TR]
[TD]B - Support[/TD]
[/TR]
[TR]
[TD]B - Support
B - Support
B - Support[/TD]
[/TR]
[TR]
[TD]C - Support[/TD]
[/TR]
[TR]
[TD]C - Support[/TD]
[/TR]
[TR]
[TD]C - Support[/TD]
[/TR]
[TR]
[TD]C - Support[/TD]
[/TR]
[TR]
[TD]C - Support[/TD]
[/TR]
</tbody>[/TABLE]

and i have 3 Sheets (A Sheet,B sheet and C sheet )

first :
i need Formula to transfer
A - Support to A sheet (in order from cell A2:A4) and so on
B - Support to B sheet (in order from cell A2:A7) and son on
C - Support to C sheet (in order from cell A2:A5) and so on

second :
i need when i delete the data from (data sheet ) it's not effect on data which transfer to the other 3 sheets ( A,B and C)

Notes:
1- i can NOT do it manual with different users and multi department i try to make it easy to the users to copy data in one sheet and the separation will be automatically by Excel

2- i find way to transfer the data to the other sheets but not in order
A - Support to A sheet (in order from cell A2:A4) and so on
B - Support to B sheet (in order from cell A5:A10) and son on
C - Support to C sheet (in order from cell A11:A15) and so on

 

[sijpie]

Hi Ahmed,

your question is not fully clear. Let me try to say it in my words.

1. You receive data from colleagues into one sheet.
2. They all put it inot one table
3. In column A you can see what needs to go to which new sheet:
   1. if it starts with A then the data needs to go to sheet A
   2. if it starts with B then the data needs to go to sheet B
   3. etc.
4. When finished delete the original table

Is it just the data in column A that needs to be transferred, or the complete row?

 

[AhmedRady]

Hi Ahmed,
Hi sijpie,

Q- You receive data from colleagues into one sheet.
A- yes, you can say i received it form colleagues . literally " i copy it form Application "

Q- They all put it inot one table
A- yes , one column "Data sheet" A column

Q- In column A you can see what needs to go to which new sheet
if it starts with A then the data needs to go to sheet A
A- :Yes, it's exactly what i need in sheet A

Q- if it starts with B then the data needs to go to sheet B
A- Yes but not only transfer , i need it to Start from Column A Row 2 " Cell A2 then A3 and so on

Q- When finished delete the original table
A- yes, the important point is when i delete it from original table "data sheet" the data which transfer to the other sheets "A sheet , B sheet and C sheet "are not effected by deleting action .

Q- Is it just the data in column A that needs to be transferred, or the complete row?
A- No , it's complete Row content 5 columns " A,B,C,D,and E

Note :
- i appreciate if you can help my to solve it by Formulas
- i try to upload pictures of my sheet to make it easy to understand my point of view bu i can't

 

[sijpie]

OK, the good part is that this is easy to do. The 'bad' part is that it cannot be done using formulas, but has to be done using a macro.

Very simply said: a formula can never change another cell, so it could never delete the rows from sheet A. Nor can it disable itself, so if it was refering to a certain range, and you delete the values in that range, then the result of the formula would change.

I will write the macro to do the transfer and clear the data sheet

 

[sijpie]

Copy this code into a macro module (in your worksheet press Alt-F11. Then in the top left window of the VBA editor that opens, you see your workbook. Right click on it and select Add/Module.
Then paste the following code into the big right panel that opened. Save the file AS (File Save-as) a macro enabled workbook.)

Option Explicit

Sub TransferData2Sheets()
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\
'\\  Transfer the data in the 'Data Sheet' \\
'\\  to the various other sheets defined   \\
'\\  the first charcter of the data in     \\
'\\  column A                              \\
'\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\

    Dim vInp As Variant, vOutp As Variant
    Dim lR As Long, lDR As Long, lC As Long, lDC As Long, UB1 As Long, UB2 As Long
    Dim rInp As Range
    Dim sID As String
    Dim wsOutp As Worksheet, wsInput As Worksheet
    
    Const iLENGTH As Integer = 1    '<<<<< Set number of characters that define _
                                     which sheet the data goes to. Modify to suit.
    
    '>>>> This macro assumes that the sheets have the same name as the identifier
    
    Set wsInput = Sheets("Data Sheet")    '<<<<< Modify the sheet name if different
    'define the range that has the input
    Set rInp = wsInput.Range("A1").CurrentRegion
    'sort the range on column A
    rInp.Sort key1:=rInp.Cells(1, 1)
    
    'load the data into an array for fast processing
    vInp = rInp.Value
    'get the size of the array
    UB1 = UBound(vInp, 1)
    UB2 = UBound(vInp, 2)
    'Now loop through the array and copy each group of data
    For lR = 1 To UB1
        sID = Left(vInp(lR, 1), iLENGTH)
        For lC = lR + 1 To UB1
            If StrComp(sID, Left(vInp(lC, 1), iLENGTH)) Then
                'different identifier
                Exit For
            End If
        Next lC
        'set the output array size (this also clears the array)
        ReDim vOutp(1 To (lC - lR), 1 To UB2)
        ' and copy the data for this group (sheet) from the input array to the output array
        For lDR = 1 To (lC - lR)
            For lDC = 1 To UB2
                vOutp(lDR, lDC) = vInp(lR + lDR - 1, lDC)
            Next lDC
        Next lDR
        'set up the output sheet
        Set wsOutp = Nothing    'in case the sheet doesn't exist, we want to continue
        On Error Resume Next
        Set wsOutp = Sheets(sID)
        On Error GoTo 0
        ' check if sheets exists
        If wsOutp Is Nothing Then
            'Create new sheet with the identifier name as it doesn't exist yet
            'hide all the activity
            Application.ScreenUpdating = False
            Set wsOutp = Sheets.Add(after:=Sheets(Sheets.Count))
            wsOutp.Name = sID
            wsInput.Activate
            Application.ScreenUpdating = True
        End If
        '' find the 1st empty row on the sheet
        lDR = wsOutp.Cells(Rows.Count, 1).End(xlUp).Row + 1
        ' and copy the output array to the sheet
        wsOutp.Cells(lDR, 1).Resize(lC - lR, UB2).Value = vOutp
        'set lR to the last processed row
        lR = lC - 1
    Next lR
    'Clear the input range
    rInp.Clear
    MsgBox "Done!", Title:="Transfer Data"
End Sub

 

[AhmedRady]

first of all , i must thanks you for your effort. i asked if you can provide my with the sheet you try that code on it , it's high appreciated.
thanks again for your support