How split column into multiple columns for multiples sheets
Solved
Hasson_6770
Hasson_6770
- Posts
- 19
- Registration date
- Monday August 2, 2021
- Status
- Member
- Last seen
- May 23, 2022
Hasson_6770
- Posts
- 19
- Registration date
- Monday August 2, 2021
- Status
- Member
- Last seen
- May 23, 2022
Related:
- Split excel sheet into multiple sheets based on column value
- Split excel into multiple sheets based on column ✓ - Forum - Excel
- Split data into multiple workbooks based on column with vba code - Guide
- Copy same column from multiple sheets - Guide
- Copy multiple columns from one sheet to another vba ✓ - Forum - Programming
- How to create a master sheet from multiple sheets in excel ✓ - Forum - Excel
2 replies
TrowaD
Updated on Feb 10, 2022 at 11:31 AM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
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:
PS: stop drugging your formula's, it isn't good for them :)!
Best regards,
Trowa
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
TrowaD
Feb 8, 2022 at 11:46 AM
- Posts
- 2880
- Registration date
- Sunday September 12, 2010
- Status
- Moderator
- Last seen
- May 2, 2022
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
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
Hasson_6770
Feb 8, 2022 at 02:16 PM
- 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
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
Updated on Feb 11, 2022 at 02:54 PM
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
Feb 14, 2022 at 12:00 PM
To fix the headers, add the following code lines between 25 (Next cell) and 26 (End sub):
Best regards,
Trowa
Feb 15, 2022 at 08:20 AM
many thanks for your assistance.