VBA: Certain commands are not looping through all worksheets

[gelll]

Hi all, I am new to VBA so sorry if this is obvious.

I have a macro which copies sheets from a Masterfile into a Template output file containing formatting.

Because the output can have varying column numbers I have written a command which clears formatting from whenever the data finishes.

The second demand then clears contents of column A (which contains lookup references, hence isn't wanted for the output).



These commands work as when I run the macro the first sheet is formatted correctly.

However, it doesn't loop the formatting commands through to the other worksheets.

My code reads as below.


' Clear formatting for all columns after last column
' Clear contents of Column A

Dim ws As Worksheet
For Each ws In Worksheets

Set LastCell = Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
LastColumn = LastCell.Column

Range(Columns(LastColumn + 1), Columns(Columns.Count)).ClearFormats
Columns(1).ClearContents

Next

Any help at all would be appreciated!

[mjr veverka]

Welcome to the forum,

You operate with certain commands in a loop, that is, you want to apply them to subsequent sheets in your workbook, but you refer the commands to the current-active sheet, because the commands lack reference to an object, i.e. "ws."

Please Login or Register  to view this content.