wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
For Each ws In ActiveWorkbook.Worksheets
ws.Select
entire.Columns.AutoFit
entire.Rows.AutoFit
Columns("G:G").EntireColumn.Delete
Columns("E:E").EntireColumn.Delete
Next ws
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
"entire" is not a valid reference.
Code:
For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Columns("G:G").Delete
    Columns("E:E").Delete
Next ws
 
Upvote 0
"entire" is not a valid reference.
Code:
For Each ws In ActiveWorkbook.Worksheets
    ws.Select
    Cells.EntireColumn.AutoFit
    Cells.EntireRow.AutoFit
    Columns("G:G").Delete
    Columns("E:E").Delete
Next ws
I know you just modified the OP's posted code, but I wanted to point out for the OP that you do not have to select the worksheet in order to do what he wants to it...
Code:
For Each ws In ActiveWorkbook.Worksheets
    ws.Cells.EntireColumn.AutoFit
    ws.Cells.EntireRow.AutoFit
    ws.Columns("G:G").Delete
    ws.Columns("E:E").Delete
Next ws
or, alternately...
Code:
For Each ws In ActiveWorkbook.Worksheets
    With ws
        .Cells.EntireColumn.AutoFit
        .Cells.EntireRow.AutoFit
        .Columns("G:G").Delete
        .Columns("E:E").Delete
    End With
Next ws
 
Upvote 0
I know you just modified the OP's posted code, but I wanted to point out for the OP that you do not have to select the worksheet in order to do what he wants to it...
Good point.
You are right, I was focusing on the points that weren't working.
 
Upvote 0
Thanks guys. I ran the code but the autofit does not seem to work. Which honestly is where I started. Do I kept playing with the code to see it a variation did. And that's how I got brain dead.

So any idea why the auto fit wont work? My guess is it auto fitting all tabs to the last active tab. That was why I tried selecting as we go.



For Each ws In ActiveWorkbook.Worksheets
With ws
.Cells.EntireColumn.AutoFit
.Cells.EntireRow.AutoFit
.Columns("G:G").Delete
.Columns("E:E").Delete
End With
 
Upvote 0
Which one is not working, the rows or columns?
Do you have any merged or protected cells in your workbook?
None of these sheets are hidden, are they?
 
Last edited:
Upvote 0
Neither.
No protected sheets or hidded.
I added a msg box and it is stuck on the last worksheet in the book.
 
Upvote 0
I added a msg box and it is stuck on the last worksheet in the book.
Exactly where did you add it?
It should be here:
Code:
For Each ws In ActiveWorkbook.Worksheets
    [COLOR=#ff0000]MsgBox ws.Name[/COLOR]
    With ws
        .Cells.EntireColumn.AutoFit
        .Cells.EntireRow.AutoFit
        .Columns("G:G").Delete
        .Columns("E:E").Delete
    End With
Next ws
If it is only returning one name, or returns the same name over and over, I suspect that you have placed this Procedure in the wrong module.
It needs to be in a General Module, and NOT in one of the Sheet or ThisWorkbook modules.
Just right-click on the file name in the VB Project Explorer, select Insert, then select Module and move the code to the Module you just created.
 
Last edited:
Upvote 0
It is placed int he correct module. To be safe I placed it twice. both times it returns Only the last sheet name in the workbook.

For Each ws In ActiveWorkbook.Worksheets
MsgBox ws.Name
With ws
MsgBox ws.Name
.Cells.EntireColumn.AutoFit
.Cells.EntireRow.AutoFit
.Columns("G:G").Delete
.Columns("E:E").Delete
End With
Next ws
 
Last edited:
Upvote 0
Maybe a stupid question, but how many sheets do you have in the Activeworkbook?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top