Cdoe Excluding specific Tabs

P

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
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
Hi,

I would wrap your code with a loop which would go through all sheets and check their names before executing your code:

Dim i as integer

For i = 1 to Sheets.count
if Sheet(i).Name <> "Analysis" and Sheet(i).Name <> "Data" and Sheet(i).Name = "Input Data" Then
'your code here
End if
Next i

Hope this helps.
 
Upvote 0
Here is another structure that you can investigate.
Rich (BB code): 
Sub Test()

Dim ws As Worksheet

For Each ws In Worksheets
  Select Case ws.Name
    Case "Analysis", "Data", "Input Data"
      'Do nothing
    Case Else
      With ws
        .Range("$A$6:$Y$288").AutoFilter Field:=1, Criteria1:="<>"
        
        ' Whatever else you want to do
        
  End Select
Next ws


End Sub
 
Upvote 0
Hi Peter,

I tried this code and a compile error comes up 'End Select without Select Case' I can't work out why?

Sub Test()
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.Name
Case "Analysis", "Data", "Input Data"
'Do nothing
Case Else
With ws
.Range("$A$6:$Y$288").AutoFilter Field:=1, Criteria1:="<>"
Selection.Copy
Range("P8:P281").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("P7").Select
.Range("$A$6:$Y$281").AutoFilter Field:=1
Range("P7").Select
' Whatever else you want to do
End Select
 
Upvote 0
Sorry, my mistake. I omitted the red line
Rich (BB code): 
Dim ws As Worksheet

For Each ws In Worksheets
  Select Case ws.Name
    Case "Analysis", "Data", "Input Data"
      'Do nothing
    Case Else
      With ws
        .Range("$A$6:$Y$288").AutoFilter Field:=1, Criteria1:="<>"
        
        ' Whatever else you want to do
      End With
  End Select
Next ws

However, the rest of your code will need modification but I can't do it for you (yet at least) as I don't know what you are trying to do.
You are copying the 'Selection' but when you run the macro, only one sheet (the ActiveSheet) can have something selected on it. So the code makes no sense to work through a number of sheets copying the selection.
Also, any range on the various worksheets that the code is being run on will need to be prefixed with a "." as I did with .Range("$A$6:$Y$288")

If you can't make it do what you want, please explain carefully exactly what you want to happen as the code loops through each sheet.

Also, when posting code, please indent your code and post it using Code Tags. My signature block below explains how to do that.
 
Last edited:
Upvote 0
Hi Peter,

Code still didn't work. I've probably deleted key instructions and just left basic things like .paste and the code doesn't know what to do. Here's what I am trying to achieve:

Goal: clean spreadsheets where each cell that has a formula in it is in place. throughout the month my colleagues will update their workbooks and type over a formula to put a number in so they can update their forecasts. However, I all these spreadsheets in a workbook are dependant on formulas in order to pull in the actual numbers from downloaded information. Ultimately I am trying to write code that will update all these spreadsheets that look like this in each workbook where the period has changed to 'Actual' and just leave the 'Forecast' columns alone until we move into that month where it changes to Actual. Here's an example of what the spreadsheet looks like:

Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Actual
Forecast
Forecast
Period
Period
Period
Period
Period
Period
Period
Period
Period
Period
Period
Period
Period
Filter
Acc
Account Name
1
2
3
4
5
6
7
8
9
10
11
12
13
'
1100
Recurrent Grant - Teaching
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
!
1103
Joint Info System Grant (JISC)
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
3.00
0.00
'
1151
Grant Transfers
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
11
FUNDING COUNCIL GRANTS
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
'
1260
Non-CR Bearing & CPD Fees
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
!
1262
SITS PG ODL Fees
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
5.00
0.00
!
1290
Research Overheads
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
10.00
0.00
'
1292
Res Ind. HSS Cost - Sustain
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
'
1296
Res Ind. SCE Cost - Sustain
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
'
1298
Res PI & CO-PI Dir Cost
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
14
RESEARCH GRANTS & CONTRACTS
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
'
1401
Software Royalty Income
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00
0.00

<tbody>
</tbody>


The rows labelled '11Funding council Grants' & '14Research Grants & Contracts' have subtotal formulas in them so I've recorded the macro to put the filter on in column A to hide these so I can copy all the information in column Period 12, for example. I got the basics to work for just one spreadsheet. However, when I use the code for multiple spreadsheets it doesn't work. Does this explain things a bit better?

I hope you can help.

Peggy
 
Last edited:
Upvote 0
Peggy2015 said:
Does this explain things a bit better?
Click to expand...
It is not entirely clear, but see if this points you in the right direction.
I don't know exactly where the formula that you want to re-apply down a column is stored, or whether it is the same formula for each sheet.

Anyway, the code below will work through all the sheets except "Analysis", "Data" & "Input Data" & on each sheet ..
- Filter the range A6:Y288 (assumed headings in row 6) for non-blanks in column A
- Copy whatever is in cell P1 (where I put my formula that I wanted re-populated in the table, but it could be anywhere) on that sheet and paste it in the range P7:P288 (it will miss the rows hidden by the filter, as you know)
- Unfilter column A

Code: 
Sub Fix_Formulas()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    Select Case ws.Name
      Case "Analysis", "Data", "Input Data"
        'Do nothing
      Case Else
        With ws
          .Range("A6:Y288").AutoFilter Field:=1, Criteria1:="<>"
          .Range("P1").Copy Destination:=.Range("P7:P288")
          .Range("$A$6:$Y$288").AutoFilter Field:=1
        End With
    End Select
  Next ws
End Sub

Hopefully you can use that as a basis for getting the code to do what you want it to. :)
 
Upvote 0
Hi Peter,

Thank you for your reply and help with this. The code works really well.

How do I write a code to ask if A4:A15="Actual" then carry out this code and if they are labelled "Forecast" don't carry out the code and do nothing?

Best wishes,
Peggy




Peter_SSs said:
It is not entirely clear, but see if this points you in the right direction.
I don't know exactly where the formula that you want to re-apply down a column is stored, or whether it is the same formula for each sheet.

Anyway, the code below will work through all the sheets except "Analysis", "Data" & "Input Data" & on each sheet ..
- Filter the range A6:Y288 (assumed headings in row 6) for non-blanks in column A
- Copy whatever is in cell P1 (where I put my formula that I wanted re-populated in the table, but it could be anywhere) on that sheet and paste it in the range P7:P288 (it will miss the rows hidden by the filter, as you know)
- Unfilter column A

Code: 
Sub Fix_Formulas()
  Dim ws As Worksheet
  
  For Each ws In Worksheets
    Select Case ws.Name
      Case "Analysis", "Data", "Input Data"
        'Do nothing
      Case Else
        With ws
          .Range("A6:Y288").AutoFilter Field:=1, Criteria1:="<>"
          .Range("P1").Copy Destination:=.Range("P7:P288")
          .Range("$A$6:$Y$288").AutoFilter Field:=1
        End With
    End Select
  Next ws
End Sub

Hopefully you can use that as a basis for getting the code to do what you want it to. :)
Click to expand...
 
Upvote 0
Peggy2015 said:
How do I write a code to ask if A4:A15="Actual" then carry out this code and if they are labelled "Forecast" don't carry out the code and do nothing?
Click to expand...
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 A4:A15?
- 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 A4:A15 ="Actual" mean? A4:A15 is 12 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?
 
Last edited:
Upvote 0
Hi Peter,

Sorry that should be E1:Q1.

Actual is when our accounting period moves from 'Forecast' into the 'Actual' period for spending. They are not merged cells with anything, they are stand alone columns with 13 periods for the financial year.

I hope that makes more sense. Sorry for the confusion.

best wishes,
Peggy




Peter_SSs said:
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 A4:A15?
- 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 A4:A15 ="Actual" mean? A4:A15 is 12 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?
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question
VBA Questions - Copying dynamic data from one tab to another and deleting filtered rows
Replies
1
Views
255
DanteAmor
DanteAmor
B
  • Solved
vba code to give next Sunday date for pasted data
Replies
2
Views
260
bh24524
B
O
  • Solved
VBA: Deleting Visible rows using Auto Filter; Excluding Header
Replies
4
Views
496
mumps
mumps
C
  • Question
Adding new date column but excluding weekends
Replies
1
Views
347
Joe4
Joe4
S
  • Solved
Working with multiple Target Ranges in Worksheet Code
Replies
3
Views
435
sspatriots
S

Forum statistics

Threads
1,216,175
Messages
6,129,300
Members
449,499
Latest member
HockeyBoi

Share this page

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