Looking to Expand on Text to Columns Macro

J

Jenny B.

Good Afternoon All,

I'm using a Text to Columns Macro and was just wondering how it can be
reconfigured to accommodate several workbook pages/tabs.

Right now, it can only used when you go to each specific tab with data (have
to do one page at a time). I'm looking to create a front page with a command
button and link it to this macro to work on all pages not just the active
page. All of the additional pages/tabs will have the same column and row
length as example noted in the macro below.

Thanks for your time and thoughts - Jenny B.


Columns("A:A").Select
Application.ScreenUpdating = False
Selection.TexttoColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, comma:=True, Space:=False, Other:=False, FieldInfo _
:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5,
1), Array(6, 1), _
Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1),
Array(12, 1), Array(13, 1 _
), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18,
1), Array(19, 1), Array _
(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1),
Array(25, 1), Array(26, 1), _
Array(27, 1), Array(28, 1), Array(29, 1), Array(30, 1), Array(31,
1), Array(32, 1), Array( _
33, 1), Array(34, 1), Array(35, 1), Array(36, 1), Array(37, 1),
Array(38, 1), Array(39, 1), _
Array(40, 1), Array(41, 1), Array(42, 1), Array(43, 1), Array(44,
1), Array(45, 1), Array( _
46, 1), Array(47, 1), Array(48, 1), Array(49, 1), Array(50, 1),
Array(51, 1), Array(52, 1)) _
, TrailingMinusNumbers:=True
Range("A2").Select
Call Addme
Application.ScreenUpdating = True
End Sub


Sub Addme()
Range("BA2").Formula = "=SUM(C2:AZ2)"
Range("BA2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.FillDown
End Sub
 
G

Gary''s Student

For this example, I assume your code is in a sub called jennyb; I further
assume that Front Page will have the button and will not need the column
fragmented:


Sub columnator()
For Each ws In Worksheets
If ws.Name <> "Front Page" Then
ws.Activate
Call jennyb
End If
Next
End Sub
 
J

Jenny B.

You are exactly correct.

Thank you so much for the great information. Looks great and works just the
way I hoped it would.

Thanks again and have a great holiday weekend - Jenny B.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top