Hello,
I have the following file (attached) where there is code that breaks up indentation in one column to multiple columns based on the indentation. It is working great with the Entity Tab. When I copy the code to use for one of the other tabs it no longer works. Can someone help me resolve that? I've tried a number of attempts to fix but am having no luck.
Please Login or Register to view this content.
You probably need to change this lineto refer to the other sheet(s).Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
Re: Split Text To Column based on Indents
Hello, you forgot to indicate where your VBA procedure is located as your attachment is just 'VBA empty' ...
I have two subs as an example where I have with Sheets UD3 and it fails for that sheet.
Re: Split Text To Column based on Indents
As my post #3 still needs an appropriate answer ...
Trying to post again. It is an xlsm file. Not sure why it wouldn't post that way.
It should show up in Module 1.
Re: Split Text To Column based on Indents
So, as yet exposed in post #2, if you launch manually the VBA procedure from the source indent worksheet
then just replace the hardcoded worksheet reference with the statement ActiveSheet obviously ...
The code is running either way without error. The result is not showing as expected. It shows data split to columns for the Entity tab however when I try it on other tabs I just see headers without data beneath. Thanks for the help this far.
Re: Split Text To Column based on Indents
Well in this case the data seem to not be the same structure between Entity and other worksheets
so you need to find what differs in order to revise the logic of 'your' code ...
I never stated it was mine which is why I was asking for help. It just so happened that it worked on the first tab I attempted it on and didn't on the others. Thanks again for your help.
Thanks for the tip. Adjusting the if indent is less than # has it working. Just not as clean as the Entity code.
Here is the adjusted code that has it working thanks to the tip. There were three spots that needed to be updated. Added comments and then added some remove duplicate code on the columns so that I can create dimensions more quickly for an application build.
Please Login or Register to view this content.
Try this ...
Well done !
But duplicate a code tab for other tabs is not necessary
just passing the worksheet(s) name(s) to a procedure like
according to your post #6 attachment these 3 VBA demonstrations just calling the SplitSheet procedure :
PHP Code:Sub SplitSheet(ParamArray S())
Dim W, C%, L%, R&, T$(), N&
Application.ScreenUpdating = False
For Each W In S
C = -1: L = 0: R = 1
With Sheets(W).[A1].CurrentRegion.Rows
ReDim T(1 To .Count, 99)
For N = 2 To .Count
With .Cells(N, 1)
If .IndentLevel > L Then
L = .IndentLevel
Else
R = R + 1
For L = 0 To .IndentLevel - 1: T(R, L) = T(R - 1, L): Next
End If
T(R, L) = .Value
If L > C Then T(1, L) = "Level #" & L: C = L
End With
Next
W = W & "Split"
If Evaluate("ISREF('" & W & "'!A1)") Then With Sheets(W): .UsedRange.Clear: .Activate: End With _
Else Sheets.Add .Parent: ActiveSheet.Name = W
End With
With [A1].Resize(R, C + 1).Columns
.Value = T
N = C + 2
For L = 1 To C
N = N + 2
.Item(L).Resize(, 2).Copy .Item(N)
.Item(N).Resize(, 2).RemoveDuplicates Array(1, 2), 1
Next
End With
ActiveSheet.UsedRange.Columns.AutoFit
With ActiveWindow: .SplitColumn = 0: .SplitRow = 1: .FreezePanes = True: End With
Next
Application.ScreenUpdating = True
End Sub
Sub SplitEntity()
SplitSheet "Entity"
End Sub
Sub SplitScenario()
SplitSheet "Scenario"
End Sub
Sub SplitSheets()
SplitSheet "Entity", "Scenario", "Flow"
End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon ? ★ Add Reputation ? ! ◄ ◄
Last edited by Marc L; 09-22-2022 at 10:20 AM. Reason: optimization ...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks