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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
"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
 

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,307
Office Version
  1. 2010
Platform
  1. Windows
"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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
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.
 

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322

ADVERTISEMENT

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
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
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:

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322

ADVERTISEMENT

Neither.
No protected sheets or hidded.
I added a msg box and it is stuck on the last worksheet in the book.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
54,445
Office Version
  1. 365
Platform
  1. Windows
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:

wmtsub

Active Member
Joined
Jun 20, 2018
Messages
322
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,210
Office Version
  1. 365
Platform
  1. Windows
Maybe a stupid question, but how many sheets do you have in the Activeworkbook?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,842
Messages
5,525,171
Members
409,629
Latest member
McGuilliam

This Week's Hot Topics

Top