Simple autofit macro crashes.

**peter233** · 10-12-2006, 04:41 AM

I just want to autofit 2 sheets but I get:
Run-time error '1004':
Select method of range class failed

Workbooks(antalwb + 1).Sheets(1).Cells.Select
Workbooks(antalwb + 1).Sheets(1).Cells.EntireColumn.AutoFit
Workbooks(antalwb + 1).Sheets(1).Range("A1").Select

Workbooks(antalwb + 1).Sheets(2).Cells.Select <--- Crashes here
Workbooks(antalwb + 1).Sheets(2).Cells.EntireColumn.AutoFit
Workbooks(antalwb + 1).Sheets(2).Range("A1").Select

First section works but not the second.
help!

**Simon Lloyd** · 10-12-2006, 05:28 AM

Are your worksheets hidden? if they are you first need to make them visible. Try posting your entire code for a more detailed response.

Regards,
Simon

**broro183** · 10-12-2006, 06:42 AM

hi Peter,

Assuming your sheets are visible as suggested by Simon, are you sure that you actually have a "sheets(2)"?
eg the original may have been deleted & now be "sheets(3)" - this can be checked by looking at the sheets under the Project Explorer section of the VB Editor.

btw, I'm not sure if this will help at all but the code could by tidied/shortened to:

with Workbooks(antalwb + 1).Sheets(1)
.Cells.EntireColumn.AutoFit
.Range("A1").Select
end with

I'm not 100% on this but I think that a sheet needs to be active before you try & select the "cells" on it so I've changed your second section of code to:

with Workbooks(antalwb + 1).Sheets(2)
.Select <--- Crashes here
.Cells.EntireColumn.AutoFit
.Range("A1").Select
end with

hth

**peter233** · 10-12-2006, 06:59 AM

I searched and found this piece of code which solved everything

Dim wkSt As String
Dim wkBk As Worksheet
wkSt = ActiveSheet.Name
For Each wkBk In ActiveWorkbook.Worksheets
On Error Resume Next
wkBk.Activate
Cells.EntireColumn.AutoFit
Next wkBk
Sheets(wkSt).Select

**Simon Lloyd** · 10-12-2006, 08:00 AM

Peter, be aware that using "On Error Resume Next" is not the preffered way to handle errors because it ignores all errors so you will be unaware if your code runs or not! try a different method of trapping and handling errors.

Regards,
Simon

Simple autofit macro crashes.

LinkBack

Thread Tools

Rate This Thread

Display

Simple autofit macro crashes.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions