Clearing multiple ranges from multiple worksheets efficiently

[Jon EvanCook]

I have a large worksheet used for creating daily reports on a monthly basis. I am trying to create a macro that will clear the data entries in order to create a blank worksheet that can be used as a template.

The workbook contains a lot of pages but the ones that are currently giving me a problem are the 31 daily entry sheets(one for each day of the month). The sheet codes are Sheet1 through 31, and the sheet names are "1" through "31"

I want to select each sheet, clear the contents of multiple ranges and then in 3 cells I want to insert some text. The code I am currently using is below this clears the multiple ranges but the following 3 lines which add text to 3 cells only work on the first page selected leaving these fields blank on the following 30 pages.

I have tried using a For/Next loop to cycle through the 31 pages but this requires the sheet names to be created as a variable of the loop index, when it selects Sheet1 it actually selects the first sheet in the workbook and not the sheet with the sheetname id = Sheet1 (the tab Name is "1") so this method does not act on the correct sheets.

Is there a more efficient way to clear these ranges and add the three text cells and then park the cursor in cell B8?

'   Clean out all entries in DRS to create a Virgin copy - Screen updating disabled
    Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", _
        "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31")).Select
    Range("$B$8:$C$48,$F$8:$O$48,$E$66:$E$72,$C$75:$C$77,$H$66:$H$74,$H$76:$H$77,$C$101:$M$113,$C$115:$M$117,$C$119:$M121,$C$123:$M$127,$U$8:$AM$48").Select
    Selection.ClearContents
    Range("$C$119").Value = "Today:"
    Range("$C$120").Value = "Forecast:"
    Range("$C$121").Value = "Outlook:"

    Range("$B$8").Select

Many thanks
Jon

 

[Peter_SSs]

Welcome to the MrExcel board!

Try this

<del>Range("$C$119").Value = "Today:"
Range("$C$120").Value = "Forecast:"
Range("$C$121").Value = "Outlook:"</del>

Range("C119:C121").Select
Selection.Value = Application.Transpose(Array("Today:", "Forecast:", "Outlook:"))

 

[jolivanes]

Sub Maybe()
Dim i As Long
For i = 1 To 31
With Sheets("" & i & "")
    .Range("$B$8:$C$48,$F$8:$O$48,$E$66:$E$72,$C$75:$C$77,$H$66:$H$74,$H$76:$H$77,$C$101:$M$113,$C$115:$M$117,$C$119:$M121,$C$123:$M$127,$U$8:$AM$48").ClearContents
    .Range("C119:C121").Value = Application.Transpose(Array("Today:", "Forecast:", "Outlook:"))    '<---- Thanks to Peter SSs
End With
Next i
End Sub

 

[Peter_SSs]

Sub Maybe()
Dim i As Long
For i = 1 To 31
With Sheets("" & i & "")
    .Range("$B$8:$C$48,$F$8:$O$48,$E$66:$E$72,$C$75:$C$77,$H$66:$H$74,$H$76:$H$77,$C$101:$M$113,$C$115:$M$117,$C$119:$M121,$C$123:$M$127,$U$8:$AM$48").ClearContents
    .Range("C119:C121").Value = Application.Transpose(Array("Today:", "Forecast:", "Outlook:"))    '<---- Thanks to Peter SSs
End With
Next i
End Sub

That won't 'park the cursor in cell B8' on each sheet as the OP had wanted to do.

 

[Jon EvanCook]

Welcome to the MrExcel board!

Try this

<del>Range("$C$119").Value = "Today:"
Range("$C$120").Value = "Forecast:"
Range("$C$121").Value = "Outlook:"</del>

Range("C119:C121").Select
Selection.Value = Application.Transpose(Array("Today:", "Forecast:", "Outlook:"))

Thanks Peter,
That worked a treat, I was pretty sure that where there was a will there must be a way.

Cheers
Jon

 

[Peter_SSs]

Thanks Peter,
That worked a treat, I was pretty sure that where there was a will there must be a way.

Cheers
Jon

You're welcome.

 

[Jon EvanCook]

Sub Maybe()
Dim i As Long
For i = 1 To 31
With Sheets("" & i & "")
    .Range("$B$8:$C$48,$F$8:$O$48,$E$66:$E$72,$C$75:$C$77,$H$66:$H$74,$H$76:$H$77,$C$101:$M$113,$C$115:$M$117,$C$119:$M121,$C$123:$M$127,$U$8:$AM$48").ClearContents
    .Range("C119:C121").Value = Application.Transpose(Array("Today:", "Forecast:", "Outlook:"))    '<---- Thanks to Peter SSs
End With
Next i
End Sub

Thanks for your suggestion as I mentioned I had tried a For/Next Loop which was operating on the wrong sheets, this solution taught me how to solve that problem thanks. However when I used this loop it added 7 seconds to the run time so I went back to looking at Peter SS's solution and combined his soluion with my original code to get the fastest solution so far.

    Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", _
        "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31")).Select
    Range("$B$8:$C$48,$F$8:$O$48,$E$66:$E$72,$C$75:$C$77,$H$66:$H$74,$H$76:$H$77,$C$101:$M$113,$C$115:$M$117,$C$119:$M121,$C$123:$M$127,$U$8:$AM$48").Select
    Selection.ClearContents
    Range("C119:C121").Select
    Selection.Value = Application.Transpose(Array("Today:", "Forecast:", "Outlook:"))
    Range("$B$8").Select

I have been trying to avoid using 'Select' in my macros, as advised by so many, but using the Array option to select multiple sheets seems to be faster than cycling through sheet by sheet. Unless of course someone else knows an even faster solution.

Thanks for your assistance.
Jon

 

[Peter_SSs]

.. I went back to looking at Peter SS's solution and combined his soluion with my original code to get the fastest solution so far.

About how long is that 'fastest so far' code taking?

 

[Jon EvanCook]

About how long is that 'fastest so far' code taking?

This snippet is only part of a larger macro which clears selected sheets from a 52 page workbook. Using the Sheets(Array("1",...,"31") code the whole macro was taking about 9.5 seconds, using the For/Next loop the macro took about 16.5 seconds. I didn't run a time in the macro but I could see that the For/Next loop was slower so I just timed the two versions with a stop watch.

I'm still working on optimising the code.
Cheers

 

[Peter_SSs]

Using the Sheets(Array("1",...,"31") code the whole macro was taking about 9.5 seconds, ..

That seems like quite while still. Lots of re-calculations when you clear the ranges? Are you setting calculation mode to manual while the code does its stuff and then turning it back on to automatic at the end?

Do you have any 'Event' codes that could be triggered by the deletions? If so set Application.EnableEvents to false at the start of the code and back to True at the end? (That is unless that would cause errors in your workbook)