Getting number of columns of a particular sheet while looping through sheets

[athielem]

Hi all!

I want to automatically check a number of files whether their column numbers corresponds to the number of columns of another sheet.

I have tried to solve that like this:

Workbooks.Open Filename:=Path2 & NewList(o), ReadOnly:=True, UpdateLinks:=False

For Each Sheet In ActiveWorkbook.Sheets

If Sheet.Name = "korr" Then
Lastcol = ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

Else        
End If         

Next Sheet

Workbooks(Filename).Close savechanges:=False
Filename = Dir()

The main problem is that it only identifies the correct number of columns when the sheet of interest (which is called "korr") was the last sheet to be activated; if it is another one, it simply counts the columns of the first sheet. Therefore it seems that I am not properly formulating the Lastcol variable because activesheet is not referring to the sheet name that I am looking for.

I would be very grateful if someone could give me a hint of what I'm doing wrong! Couldn't find an alternative way of establishing column numbers so far...

Best regards,
Andreas

 

[mikerickson]

Try this, it will open the workbook and set LastCol according to that books sheet korr, if there is no korr sheet LastCol will be 0

dim wb As Workbook
Set wb = Workbooks.Open(Filename:=Path2 & NewList(o), ReadOnly:=True, UpdateLinks:=False)

Lastcol = 0
On Error Resume Next
Lastcol = wb.Worksheets("korr").Cells(1, Columns.Count).End(xlToLeft).Column
On Error Goto 0

 

[athielem]

Thank you very much! Works perfectly and it even helped me to adjust another part of the code that was causing an issue