Running the same macro at sheets to the right

[alkarkar]

Sorry , many similar but different questions.
I have a workbook with e.g 40 worksheets from wich the first 10? are for calculations and taking values from the last 30 ( last 30 have names like Sheet11='Team1' , Sheet12='Team2' to last sheet with name 'Team30'.

1. I want to run the macro 'clearcells1' to all the last 30 sheets, or to run the macro 'clearcell1' to all the sheets to the right (from the active sheet- if the active sheet is the tenth , it will run to the next-last 30 sheets-same as previus e.g.)
2. Run the macro 'clearcells1' to cells e.g. 21 to 23 .(Team11!, Team12!, Team13!). The values 21/23 to be changed inside the code manually. Something like range in sheets.

I have a large file and with macro 'clearcells1' i find blank cells, delete emty rows, copy, special paste values etc in sheets e.g. 11 to 40 (reduse size)

3. In fact what i do is copy the 10nth sheet (the master-large sheet-many formulas) paste it to 11nth sheet (Team1!) , then run the macro 'clearcells1' to reduce the file size (avoid memory problems-delays) then copy again the 10nth sheet (the master) and paste it now to 12nth sheet (Team2!) and so on to the last sheet.
The number of sheets are variable but don't change during one session ( i open the file and there are e.g. 40 standar sheets - values inside cells only change). I tried to record a macro but it copies the master sheet always to the 11nth sheet.
Although an answer to the 3rd question would be exactly what i need, i would appreciate answers to the first/second question, i believe that are things i would need-use in the near future.
Thank a lot ( took me half an hour to type it)

 

[just_jon]

If we boil this down, is it that you want to

1] Take a range from the 10th sheet, and ALWAYS this same sheet name, and

2] Copy/Paste it to all sheets - could be a varying number of them - beginning with Team - as values only, or do you want the formulas?

3] And also, on these Team sheets, possible delete some existing information.

4] The range to be copied may be subject to change - how often, if so?

 

[alkarkar]

Jon,
I copy-paste hole 'MASTER SHEET' and i WANT the formulas in it to be copied-pastied to the next sheets (is easier for me to copy the hole sheet although it is not full with formulas).Then i calculate the data in the next sheet and then i 'clearcells' .This copy-paste-calculate-clear is happening at the last e.g. 30 sheets ONE BY ONE (trying to avoid memory problems - huge file - celeron 2000).
In this master sheet i often add new formulas etc and then copy - paste - calculate to check my ideas - changes. I did these checking manually so far but is getting very difficult - time comsuning.

 

[just_jon]

Still unclear as to the requirements, but maybe the below will give you an idea.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet, RangeToCopy <SPAN style="color:#00007F">As</SPAN> Range
<SPAN style="color:#007F00">' Define the range to copy; could make this the current selection, or prompt for range.</SPAN>
<SPAN style="color:#00007F">Set</SPAN> RangeToCopy = Worksheets("Master").UsedRange
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> CurrentSheet <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Left(CurrentSheet.Name, 4)
        <SPAN style="color:#00007F">Case</SPAN> "Team"
            <SPAN style="color:#00007F">With</SPAN> CurrentSheet
                RangeToCopy.Copy .[A1]
                Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
                .Calculate <SPAN style="color:#007F00">' should calculate only the CurrentSheet: do you need also to calc others?</SPAN>
                <SPAN style="color:#007F00">' What are you going to do here to trap your</SPAN>
                <SPAN style="color:#007F00">' results before clearing out the range?</SPAN>
                .Cells.Clear
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
            <SPAN style="color:#007F00">' DO NOTHING</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">Next</SPAN> CurrentSheet
<SPAN style="color:#00007F">Set</SPAN> RangeToCopy = <SPAN style="color:#00007F">Nothing</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[alkarkar]

Sorry for the response. I tried to modified Jon's code with no success. So here is a summary: My master sheet is always the same with name 'Master Team' , I want to copy the entire sheet 'Master Team', the sheets right to 'Master Team' are 'Team1', 'Team2' ...'Team30'. The little macro i'm using to reduse file size is called 'clearcells1', is the same (doesn't change when applied to different sheets-Team1!...) and lets asume for simplicity has the code:
Rows("8:10").Select
Range("AT8").Activate
Selection.Copy
Rows("8:8").Select
Range("AT8").Activate
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
(Special paste only values)

Below i will try to explane with my words what i want to be done.

Sub Macroallinone()
Copy whole sheet 'Master Team'
Paste it to sheet 'Team1'
calculate (at least the current sheet 'Team1' or the whole file ,whatever is eαsier)
now run the macro 'clearcells1' (or the code of 'clearcells1' macro can be inserted here)
Copy whole sheet 'Master Team'
Paste it now to sheet 'Team2'
calculate (at least the current sheet 'Team2' or the whole file ,whatever is eαsier)
now run the macro 'clearcells1' (or the code of 'clearcells1' macro can be inserted here)
Copy whole sheet 'Master Team'
Paste it to sheet 'Team3'.........
...... same until sheet 'Team30'
Hope i was clear this time.

The first request (first post) had this meaning.
I record macro1, in wich i copy 'Master Team', and i request the code to run the above macro (or anyelse macro) to sheets right to a specific (or current) sheet.

 

[just_jon]

Confused about which cells exactly you wish to clear...

 

[alkarkar]

Sorry for asking again, but here it is as simple as i can.
I have 40 sheets and i want to run macro 'XXX' to sheets tenth, eleventh ... .... 40th. What code (new macro) do i need?
The new macro will apply macro 'XXX' to 10nth sheet, then to 11nth sheet ... to the last sheet. Sheets' names are e.g. 10nth=Team1 , 11nth=Team2 ... 40th=Team30.
Thank you for your time .
Alex

 

[just_jon]

Taking another swing with

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> foo()
<SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet, CalcType <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
CalcType = Application.Calculation
Application.Calculation = xlCalculationManual
Application.EnableEvents = <SPAN style="color:#00007F">False</SPAN>
<SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> CurrentSheet <SPAN style="color:#00007F">In</SPAN> ThisWorkbook.Worksheets
    <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Left(CurrentSheet.Name, 4)
        <SPAN style="color:#00007F">Case</SPAN> "Team"
            <SPAN style="color:#00007F">With</SPAN> CurrentSheet
                Sheets("Master Team").Cells.Copy .[A1]
                Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN>
                .Calculate
                <SPAN style="color:#007F00">' Insert the code of clearcells here</SPAN>
                <SPAN style="color:#007F00">' Is it ? --</SPAN>
                .Rows("8:8").Value = Rows("8:8").Value
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">Case</SPAN> <SPAN style="color:#00007F">Else</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN>
<SPAN style="color:#00007F">Next</SPAN> CurrentSheet
<SPAN style="color:#00007F">If</SPAN> CalcType = -4105 <SPAN style="color:#00007F">Then</SPAN>
    Application.Calculation = xlCalculationAutomatic
<SPAN style="color:#00007F">ElseIf</SPAN> CalcType = 2 <SPAN style="color:#00007F">Then</SPAN>
    Application.Calculation = xlCalculationSemiautomatic
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
Application.EnableEvents = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>