Excluding worksheets from a Macro

M

MancmMonkee

New Member
Joined
Nov 21, 2018
Messages
4
Hi there - bit of an Excel novice, but trying to get a Macro I have on a document updated so it ignores a few of the worksheets. The sheets to be ignored are just data, and the macro in question is a simple copy & paste job for some monthly tabs we have further in the document. Have tried to set the code so it ignored Sheets 1, 2 & 3 - but does all others - however it's just not working, so sure I've missed something!

Any help would be appreciated!

Code: 
Sub CopyYest()
Dim xsheet As Worksheet
   
     For Each ws In Worksheets
    If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" And ws.Name <> "Sheet3" Then
       
    Range("C4:C34").Select
    Selection.Copy
    Range("D4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F4:F34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I4:I34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L4:L34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("M4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O4:O34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("R4:R34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("S4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("U4:U34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("V4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("X4:X34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Y4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AA4:AA34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AB4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AD4:AD34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AE4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AG4:AG34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AH4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AJ4:AJ34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AK4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=29
    Range("AM4:AM34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AN4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AP4:AP34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AQ4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AS4:AS34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AT4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AV4:AV34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AW4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AY4:AY34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AZ4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BB4:BB34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BC4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BE4:BE34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BF4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll Down:=72
    ActiveWindow.ScrollColumn = 30
    ActiveWindow.ScrollColumn = 27
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 3
    Range("C76:C106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("D76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("F76:F106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("G76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("I76:I106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("J76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("L76:L106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("M76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("O76:O106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("P76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("R76:R106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("S76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("U76:U106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("V76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("X76:X106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("Y76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AA76:AA106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AB76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AD76:AD106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AE76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AG76:AG106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AH76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AJ76:AJ106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AK76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.SmallScroll ToRight:=24
    Range("AM76:AM106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AN76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AP76:AP106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AQ76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AS76:AS106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AT76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AV76:AV106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AW76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AY76:AY106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AZ76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BB76:BB106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BC76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("BE76:BE106").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("BF76").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


    Next ws
    
End Sub
 
Last edited by a moderator:

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Re: Need help excluding worksheets from a Macro

Welcome to the board.

You need an

Code: 
End If

line right before the "Next ws" line to terminate your If statement.
 
Upvote 0
Re: Need help excluding worksheets from a Macro

Thanks Eric - it's still not working though.

I have a sheet with a Macro button on it - if I push that, it just runs the Macro on that sheet (which should be sheet 1 and not included in the macro now?). I've tried starting the Macro on one of the monthly tabs too, but all it does is update that one tab, not the others.
 
Upvote 0
Re: Need help excluding worksheets from a Macro

Well, first of all, it's apparent that you (or someone) created that macro from the macro editor. While that's a great way to start, there's a lot you can do to clean it up. For example, there's usually no reason to include the scrolling commands, you can just delete those lines. Next, you can do almost anything in VBA without using .Select or .Activate. In fact, those to methods are pretty slow. Here's a link to some tips about how to clean up recorded macros (written by one of our very own moderators here):

http://www.informit.com/articles/article.aspx?p=2461303


It appears that the main reason your macro doesn't work on all your sheets is that you don't include the sheet on any of the Selects, Copys, or Pastes, so the macro defaults to the active sheet. A quick and dirty way to fix that (which violates what I just said! :rolleyes:) is to activate the sheet you want to modify. Add a

Code: 
ws.Activate

right after your If. When you have some time, you can clean up the rest of the macro. For example:

Code: 
    Range("C4:C34").Select
    Selection.Copy
    Range("D4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
can be replaced with:

Code: 
ws.Range("D4:D34").Value = ws.Range("C4:C34").Value
and the sheet does not need to be active for it to work. I'd bet that your whole macro could be about 1/4 of its current size. Good luck!
 
Upvote 0
Re: Need help excluding worksheets from a Macro

That's worked - thanks a lot Eric, really appreciate the help & advice!
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Insert formula in last column macro
2
Replies
13
Views
215
MARK858
MARK858
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
230
kbk
K
willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
400
willow1985
willow1985
R
  • Question Question
I keep getting the error 91 but i cant figure out what is wrong here.
Replies
2
Views
60
mumps
mumps
S
  • Question Question
Dynamic Address by VBA
Replies
8
Views
212
MikeVol
MikeVol

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

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