Log In
Register
Home
New Member
November 21, 2020
Offline
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!
Trusted Members
December 20, 2019
Offline
Please add worksheet examples, it means we don’t have to waste time creating data that you already have and it ensures it is in the correct format.
the issue you have is that you are not telling the code to move to the next worksheet
The for each ws in worksheets is looping through the sheets, but in excel you are still looking at Sheet1 and all the code after this is acting on the active worksheet.
So you need to either 'fully qualify' where you want the action to occur at each action (this is normally considered best practice as you know exactly where something is happening)
Sub Moth()
' Clear formatting for all columns after last column
' Clear contents of Column A
Dim ws As Worksheet
For Each ws In Worksheets
Set LastCell = ws.Cells.Find(What:="*", After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False)
LastColumn = LastCell.Column
ws.Range(ws.Columns(LastColumn + 1), ws.Columns(ws.Columns.Count)).ClearFormats
ws.Columns(1).ClearContents
Next
End Sub
Or you tell Excel to move sheets, so the action is happening on the active sheet - this can slow down the process but its unlikely to have any impact on small datasets. Just add in the ws.activate line below. (this is a bit easier bit if you select a different sheet you can sometimes get things happening in the wrong place)
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
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
End Sub
1 Guest(s)
