Cdoe Excluding specific Tabs

Peggy2015

Board Regular
Joined
Oct 19, 2015
Messages
109
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
 
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.
 
Upvote 0

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,828
Messages
6,181,204
Members
453,022
Latest member
RobertV1609

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top