Previous Topic Next Topic

How split column into multiple columns for multiples sheets

Solved/Closed
Hasson_6770 Posts 19 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 - Updated on Feb 7, 2022 at 04:57 AM
Hasson_6770 Posts 19 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 - Feb 15, 2022 at 08:20 AM
Hello,


I need macro to split column C for sheets REP1,PROC1 into columns C,D,E based on matching column B with column B into sheet RP . it should split data for sheets REP1,PROC1 based on divided data into sheet RP . with considering when split data into sheets REP1,PROC1 should be the same arranging based on sheet RP .



sheet RP


original data
sheet REP1

sheet PROC1



result should be

sheet REP1



sheet
PROC1

note:my real data about 1000 rows across multiple sheets and every time I will add new sheet , so this is not just two sheet to divide data based on sheet RP . just ignore error duplicate ITEM twice for two columns I will change the second in column B to CODE.
I hope my question is clear , please inform me if you need some details if this is not clear .
thanks in advance
Related:

2 replies

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Updated on Feb 10, 2022 at 11:31 AM
Hi Hasson,

Ok, it's just that some people don't realise what formula's can do.

Here is the requested code: 
Sub RunMe()
Dim lRow As Long
Dim mFind As Range

Sheets("RP").Select
lRow = Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Range("B2:B" & lRow)
    With Sheets("REP1")
        Set mFind = .Columns("B").Find(cell.Value)
        If Not mFind Is Nothing Then
            .Range("C" & mFind.Row).Value = cell.Offset(0, 1).Value
            .Range("D" & mFind.Row).Value = cell.Offset(0, 2).Value
            .Range("E" & mFind.Row).Value = cell.Offset(0, 3).Value
        End If
    End With
    With Sheets("PROC1")
        Set mFind = .Columns("B").Find(cell.Value)
        If Not mFind Is Nothing Then
            .Range("C" & mFind.Row).Value = cell.Offset(0, 1).Value
            .Range("D" & mFind.Row).Value = cell.Offset(0, 2).Value
            .Range("E" & mFind.Row).Value = cell.Offset(0, 3).Value
        End If
    End With
Next cell
End Sub


PS: stop drugging your formula's, it isn't good for them :)!

Best regards,
Trowa


1
Hasson_6770 Posts 19 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Updated on Feb 11, 2022 at 02:54 PM
Hi Trowa,
You're legend! this is exactly what I need it.
Just I want fixing the headers into two sheets when split should replace the headers as the same thing in sheet RP . I'm taking about columns D,E into sheets Rep1,Proc1.
best regards,
Hasson
0
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545 > Hasson_6770 Posts 19 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Feb 14, 2022 at 12:00 PM
Thanks Hasson!

To fix the headers, add the following code lines between 25 (Next cell) and 26 (End sub): 
Range("D1:E1").Copy
Sheets("REP1").Range("D1:E1").PasteSpecial
Sheets("PROC1").Range("D1:E1").PasteSpecial
Application.CutCopyMode = False


Best regards,
Trowa
1
Hasson_6770 Posts 19 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023
Feb 15, 2022 at 08:20 AM
awesome!
many thanks for your assistance.
0
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 545
Feb 8, 2022 at 11:46 AM
Hi Hasson,

It seems to me like you could just use VLOOKUP functions for that.

Sheet REP1, cell C2: =VLOOKUP($B2,RP!$B$2:$E$1000,2)
Sheet REP1, cell D2: =VLOOKUP($B2,RP!$B$2:$E$1000,3)
Sheet REP1, cell E2: =VLOOKUP($B2,RP!$B$2:$E$1000,4)

Then drag those formula's down.

Do the same for Sheet PROC1, or select both sheets while holding CTRL to only have to do it once.

Best regards,
Trowa
0
Hasson_6770 Posts 19 Registration date Monday August 2, 2021 Status Member Last seen May 23, 2022
Feb 8, 2022 at 02:16 PM
Hi Trowa,
I appreciate to try helping me by formula
as I said I have about 1000 rows . So I search for macro to big data instead of every time drug the formula when increase data. Actually I search too much in Google but I don't find like my request.
Thanks
0