One Macro multiple sheets

[jjlbmcnews]

Hi,

I have a macro that modifies the properties of a column within a sheet. I've set this up to use Autpen but it only runs on one sheet and not the other three. How can you force the macro to run on all the sheets in the workbook on open?

 

[Bowers74]

dim ws as worksheet
for each ws in thisworkbook.worksheets

' Your Macro

next ws

Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[jjlbmcnews]

Hi,

Thanks for the advice, I've tested this and it doesn't appear to work, code below.

Sub Autpen()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Columns("B:B&quot.Select
Selection.TextToColumns Destination:=Range("B1&quot, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
Columns("B:B&quot.EntireColumn.AutoFit
Selection.NumberFormat = "0"
Columns("B:B&quot.EntireColumn.AutoFit

Next ws

End Sub

Am I missing something?

 

[xlbo]

try this

Sub Autpen()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
with ws
.columns("B:B&quot.TextToColumns Destination:=.Range("B1&quot, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:=Array(1, 1)
.NumberFormat = "0"
.Columns("B:B&quot.EntireColumn.AutoFit
end with
Next ws


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

This should work:

Sub Auto_Open()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws.Columns("B:B")
        .TextToColumns Destination:=Range("B1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
        :=Array(1, 1)
    .NumberFormat = "0"
    .EntireColumn.AutoFit
End With    
Next ws

Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[Bowers74]

Cross-Posted Geoff's





Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884

 

[xlbo]

Looks like we're both on a mission today Mike....
Used the same with construct and both deleted the unnecessary autofit line......

Great minds think alike....
or
Fools rarely differ...... ?????


Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[Bowers74]

Not really on a mission Geoff ;-)

It's just been awhile since I had a chance to post here and I got a little bored. My Project is "on hold" until after the holidays!

Happy Ho Ho!!! [Cheers]



Peace!

Mike

Didn't get the answers that you wanted? Take a look at FAQ219-2884