making my VBA shorter

[andrewb90]

Hi all,

I have a macro that I use to clear out selected pieces of information from the current sheet. (about 70 lines) Now, I have several other sheets that need the same cells cleared out. My dilemma is that I don't want to just put in

Sheets("Page2").Select

then put in my 70 or so lines of deleting code over and over again. Is there a way to have the code go through the different pages that I specify and run the code?


Thanks,

Andrew

 

[Jonmo1]

You can use something like

Sub Test()
Dim MySheets As Variant, MySheet As String, ws As Worksheet

MySheets = Array("Sheet1", "Sheet2", "Sheet3") '<-list of sheets to run code on.
For Each MySheet In MySheets
    Set ws = Sheets(MySheet)
    
    'Your 70 lines of Various code here using ws to reference ranges and objects

Next MySheet
End Sub

Now the key is to go through your 70 lines of code, look for references to ranges and cells and add the ws. reference to them..
i.e.
Range("A1:A100") would be changed to ws.Range("A1:A100")


You can post that code for help adjusting it accordingly.

 

[Rick Rothstein]

I have a macro that I use to clear out selected pieces of information from the current sheet. (about 70 lines)

I would be interested in seeing your 70-line macro as I would think for just clearing things out, something less that 70 lines of code should be possible.

 

[Jonmo1]

And I had a pretty big mistake in the code I posted.
It's what happens when you don't test code before posting it..


The MySheet Variable used in the For Each loop should not be dimmed as a String.

So it should look like

Sub Test()
Dim MySheets As Variant, ws As Worksheet, MySheet
MySheets = Array("Sheet1", "Sheet2", "Sheet3") '<-list of sheets to run code on.
For Each MySheet In MySheets
    Set ws = Sheets(MySheet)
    ws.Range("A1").Value = ws.Name
    'Your 70 lines of Various code here using ws to reference ranges and objects
Next MySheet
End Sub