Cdoe Excluding specific Tabs

[Peggy2015]

Hello,

I have written code to copy down a formula in one cell into all the other cells in a column. I need to repeat this for several tabs in my workbook, however I want to exclude certain tabs from this task as they do not have the same information in their cells. How do I write this to exclude tabs called 'Analysis', 'Data' and 'Input Data'?

My current code is:

Sub Summary_JobCode_Formulas_CopiedDown2()
'
' Summary_JobCode_Formulas_CopiedDown Macro
' This is to copy the foluma at the top of the column and pull down to the remaining cells which will overwrite any hard keyed numbers that have been typed in.
'
ActiveSheet.Range("$A$6:$Y$288").AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Range("P8:P278").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("P7").Select
ActiveSheet.Range("$A$6:$Y$281").AutoFilter Field:=1
Range("P7").Select
End Sub

Thanks for your help.

Best wishes,
Peggy

 

[Peter_SSs]

I hope that makes more sense.

It just changes my questions a little, it didn't answer them.

That is not specific enough. Remember that I have never seen your sheet, nor am I familiar with just what you are trying to do.

1. Where is E1:Q1?
- Is it on one particular sheet & a single decision has to be made whether to process the whole code or not at all? If so, what sheet?
- Is it on each of the sheets being processed and a decision is made sheet-by-sheet whether to process that sheet?
- Somewhere else?

2. What does if E1:Q1 ="Actual" mean? E1:Q1 is 13 cells.
- Do they all need to be "Actual"?
- Does just one of them need to be "Actual"?
- What should happen if one of those cells is "Actual" and another one is "Forecast"?
- Are they merged cells?
- Something else?

Also, in my previous example, I copied a formula from cell P1 on each processed sheet. I don't what cell(s) of your need to be copied.

 

[Peggy2015]

Hi Peter,

1. E1:Q1 are the ones in blue:

[TABLE="class: cms_table, width: 1231"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Actual[/TD]
[TD]Forecast[/TD]
[TD]Forecast[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[TD]Period[/TD]
[/TR]
[TR]
[TD]Filter[/TD]
[TD][/TD]
[TD]Acc[/TD]
[TD]Account Name[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]


- Is it on one particular sheet & a single decision has to be made whether to process the whole code or not at all? If so, what sheet? - No they are on several sheets in a workbook, except for some which don't have these headings at all. the ones that are not included are they ones from the original formula you sent me excluding: Case "Analysis", "Data", "Input Data"

- Is it on each of the sheets being processed and a decision is made sheet-by-sheet whether to process that sheet? All the sheets are set up the same where they change to Actual once we have gone through that month. Until then, they remain a 'Forecast'. For example, August is our Period 1, once we go into September I will be running a report on Period 1 actual spending. Forecast Period 1 now becomes Actual Period 1.

- What should happen if one of those cells is "Actual" and another one is "Forecast"? My colleagues type over formulas in these spread sheets so they can forecast their spending. It will remain as forecast until we have closed an 'Actual' spending month then I run the lovely macro you have helped me with to copy down the formula in the month we have just changed from Forecast to Actual because we no longer need their information and we need to see what spending has actually happened and if what they predicted actually happened.

- Are they merged cells? No merged cells.

thanks for your help.


Best wishes,
Peggy

 

[Peter_SSs]

So what exactly should happen on that sheet you showed a sample of?

... and that would include answering the other question that I asked in my last post too (though I did miss out a word):

Also, in my previous example, I copied a formula from cell P1 on each processed sheet. I don't (know) what cell(s) of your need to be copied.

 

[Peggy2015]

At the beginning of the financial year we start with all 13 Periods at 'Forecast'. When we go through one month and close off the financials each column will change to 'Actual'. When each column is at 'Forecast' staff will type over existing formulas so we can predict what will be spent for that month. Then my task to close the month end and report the actuals is to change the relevant column to Actual and copy down the formulas which point to another spreadsheet that holds the actual information.

Not sure what you mean by the other question you asked. Hopefully this answers it?

Best wishes,
Peggy