• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 114
  • Last Modified:

Tidying up code

Hi,

I have some code below, i must repeat the action for 4 other spreadsheets

Europe.xlms
LATAM.xlms etc etc

Instead of having the code repeated an additional 4 times, can i create some form of loop that is tidier and more efficient?

Many thanks

Sub ClearSheets()

Windows("Asia.xlsm").Activate

With ActiveWorkbook

Sheets("PB Review").Select
ActiveSheet.UsedRange.Clear

Sheets("PM Review").Select
ActiveSheet.UsedRange.Clear

End With

End Sub

Open in new window

0
Seamus2626
Asked:
Seamus2626
  • 2
2 Solutions
 
SteveCommented:
A couple of suggestions here...

Cycle through sheets or call each individually.
Up to you which works...

Sub MainSheets()

Windows("Asia.xlsm").Activate

Call ClearSheets(Sheets("PB Review"))
'Call ClearSheets(Sheets("PB Review"))  'change this line for each seet

End Sub


Sub AllSheets()

Dim ws As Worksheet

Windows("Asia.xlsm").Activate

For Each ws In ThisWorkbook.Sheets

    Call ClearSheets(ws)

Next ws

End Sub


Sub ClearSheets(ws As Worksheet)

With ws

    .UsedRange.Clear

End With

End Sub

Open in new window

U--Example.xlsm
0
 
Rgonzo1971Commented:
Hi,

pls try

Sub ClearSheets()
aWbkList = Array("Asia.xlsm", "Europe.xlsm", "LATAM.xlsm")
For Each Item In aWbkList
    Windows(Item).Activate
    With ActiveWorkbook
        Sheets("PB Review").UsedRange.Clear
        Sheets("PM Review").UsedRange.Clear
    End With
Next
End Sub

Regards
0
 
SteveCommented:
Oops.. I read worksheet, not workbook...

Workbook example below:

Sub MainSheets()

Call ClearSheets(Workbooks("Example.xlsm"))
Call ClearSheets(Workbooks("Europe.xlsm"))
Call ClearSheets(Workbooks("LATAM.xlsm"))

End Sub



Sub ClearSheets(wb As Workbook)

With wb

    .Sheets("Sheet1").UsedRange.Clear
    .Sheets("Sheet2").UsedRange.Clear

End With

End Sub

Open in new window

U--Example.xlsm
0
 
Seamus2626Author Commented:
Thanks guys!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now