Hello,
I've built a macro to extract a txt file and create a separe worksheet for each scenario (only blue, only red & both colours - based on columns "A" & "B"). I also wanted to keep empty rows between different records in each worksheet. When I run the macro, it gets stack and no details are created under each worksheet. My macro and the input file are attached.
Any help would be appreciated.
Cheers,
Goropd
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.
Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source
If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE
Walking the tightrope between genius and eejit...
Hello goropd,
I have added this macro to the attached workbook. Let me know if this needs any modification.
Please Login or Register to view this content.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Hi ! Another way …
As I'm not sure to well copy your need without any expected result workbook attachment
but as a starter just with Excel inner features like text convert, filter, formula, sort, …
PHP Code:Sub Demo1()
Dim V, F&, Ws As Worksheet, C&, R&
V = Application.GetOpenFilename("Text Files (*.txt),*.txt")
If V = False Then Exit Sub
F = Application.SheetsInNewWorkbook
Application.ScreenUpdating = False
Application.SheetsInNewWorkbook = 3
Set Ws = Workbooks.Add.Worksheets(1)
Application.SheetsInNewWorkbook = F
With Workbooks.Open(V).Worksheets(1)
Ws.[A1].Value = LTrim(.[A1].Text)
.UsedRange.TextToColumns , xlFixedWidth, xlTextQualifierNone, DecimalSeparator:=".", _
FieldInfo:=Array([{0,2}], [{9,2}], [{22,2}], [{29,2}], [{43,2}], [{51,4}], [{65,1}], [{78,1}], [{97,2}], [{105,2}])
F = .[A1].End(xlDown).Row
C = .UsedRange.Columns.Count + 1
R = .UsedRange.Rows.Count
V = .Cells(F, 1).Text
With Range(.Cells(2, C), .Cells(R, C))
.Formula = Replace("=OR(AND(A2="""",OR(A1="""",A1=""#"")),A2=""#"")", "#", V)
.Formula = .Value
End With
Range(.Cells(F, 1), .Cells(R, C)).Sort .Cells(F, C), xlAscending, Header:=xlYes
R = .UsedRange.Columns(C).Find(False, SearchDirection:=xlPrevious).Row
Range(.Cells(F, 1), .Cells(R, 2)).Copy Ws.[B1]
.Parent.Close False
End With
For Each V In [{"BLUE","RED"}]
With Ws.Parent.Worksheets(2 - (V = "RED"))
Ws.UsedRange.Columns(2).AutoFilter 1, V, xlOr, ""
Ws.UsedRange.Copy .[A1]
Range(.Cells(2, 4), .Cells(.UsedRange.Rows.Count, 4)).Formula = "=AND(B2="""",B1="""")"
.UsedRange.Sort .Cells(4), xlAscending, Header:=xlYes
.UsedRange.Columns(4).Clear
.Columns(1).AutoFit
.Name = V
End With
Next
Ws.[A1].AutoFilter
Ws.Columns(1).AutoFit
Ws.Name = "BOTH"
Set Ws = Nothing
Application.ScreenUpdating = True
End Sub
Do you like it ? So thanks to click on bottom left star icon « ★ Add Reputation » !
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks