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 to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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

P
  • Solved
Counter in For Each loop
Replies
2
Views
171
Possum17
P
A
  • Question
Macro to update list to last row on another sheet copy source from multiple sheets
Replies
0
Views
296
Azamamir
A
P
  • Solved
Need help with macro Dynamic Range??
Replies
6
Views
155
pennbowl
P
J
  • Question
VBA to select partial filename and go back and forth between two files to copy paste
Replies
1
Views
156
shknbk2
S
S
  • Solved
VBA does not continue to copy after blank cell
Replies
8
Views
508
jolivanes
jolivanes

Forum statistics

Threads
1,214,376
Messages
6,119,179
Members
448,871
Latest member
hengshankouniuniu

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