We need to cleanup sheets, for a load file workbook. The load file will be used many times to load whatever records are needed, and the program hate rows where the data was just cleared. I have created a macro to find the last clear row in the sheet (Col B), ctr + shift + down , then delete selected rows to get rid of any lingering formats in those rows.
I have found a piece of code that allows for your macro to loop through all sheets, but when I step through the code, it stays in the active sheet. Any ideas?
I have found a piece of code that allows for your macro to loop through all sheets, but when I step through the code, it stays in the active sheet. Any ideas?
VBA Code:
Sub RunMacroOnAllSheets()
Dim ws As Worksheet
' Loop through all sheets in the active workbook
For Each ws In ThisWorkbook.Sheets
' Call your macro here
Call CleanUp
Next ws
End Sub
Sub CleanUp()
' Delete all rows after the last used row
Range("B" & Rows.Count).End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.Rows("1:1").EntireRow.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Delete Shift:=xlUp
ActiveWorkbook.Save
Range("B" & Rows.Count).End(xlUp).Select
End Sub
Last edited by a moderator: